Unanswered

Associating multiple rows of column with a single object

(0) Share

Report

Posted on by Sidhant_02

986

Hi everyone,
This is in continuation to one of my post that I had posted earlier in the forum which was about import excel you can have a look at it for better idea: Import from excel .
Previously I was creating different columns for each attribute (like if a user has worked on 2 projects then there will be Project1 Description1, Project 2 Description2), but now instead of creating separate columns for every single attribute I need to maintain just one column and if there are more than 1 value then mention in the next row

For example: In the above pictures a user with employee id : 12383
Has 2 values of Qualification associated which is
(Degree) HSC - (Institute) RR Shinde college - (Grade System) Percentage - (Percentage/CGPA) 69.5
(Degree) BE- (Institute) KSE - (Grade System) CGPA- (Percentage/CGPA) 8.7

Similarly user has more than 1 primary skills and the new skill is mentioned in separate row instead of comma separated like:
PowerApps
JS
Mendix

As you can see in the above picture there are two records and every new item (for a specific column is mentioned in new row like Qualification details (Qualification, InstituteName, Marks; PrimarySkills (every new skill is mentioned on a new line instead of comma separated) ). Now when the save is done like in case of John has 2 qualifications like BE, GED along with their institute name and marks should be associated with him (instead of creating a complete record) and it should only create a new record when it finds out in the new row line the FirstName, LastName are filled in otherwise associate everything with current record

So now if I want to store this data (once user uploads the file) and display it in Power Apps, how to do it is it possible , please let me know about and if there any other approaches do share them as well.

Regards,
Sidhant.

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (0)

narayan225 2,547 Super User 2025 Season 2 on at

Like (0)

Report

@Sidhant_02

You will need to create separate tables for each entity.

For example, you will have a user table where you will store user's details like name, email, address etc. Another table will be project table where you will store project details. And if the user is working on multiple projects, you will create a relation between the user table and project table. If a project has multiple users, you will need to create a separate table that can resolve the many to many relationship.

You can create forms for users to fill up data and views that you can use to filter and summarise your data.

Hope this helps,

Cheers!

Was this reply helpful? Yes No
Sidhant_02 986 on at

Like (0)

Report

Hi @narayan225 ,
I have done this already. Initially for these sections (like Skills, Projects, Qualifications, Certifications) for which I was making use of forms and the data was getting saved for that user (for which I made use of Office365.Users().Id), but now to make things more simpler I have introduced the import from Excel feature that reduces time to fill such long forms.
I have mentioned more details in my another post if possible could you have a look at that, I guess then you can get more idea about it Import from Excel
In the end the goal is same whatever details are present in the excel should be saved in the respective tables

Regards,
Sidhant.

Was this reply helpful? Yes No
Sidhant_02 986 on at

Like (0)

Report

Hi @narayan225 ,
So I have flow that works well when we have records in one row for a single user like
But now in few columns like Education (Qualification, InstituteName, Marks; Project, Description a user can have more than 1 like shown below)
So now when my flow is executed that extracts the data from the uploaded excel file and stores it in a SP list (here name of SharePoint list is 'Details') so what I want is the new values like the new Institute name, Marks , Qualification and Project,Description should be associated with the same user (as we have not added any new value in the Personal Details: FirstName, LastName), but now it is treating the new rows as a new record.

In SharePoint the new qualification and project details are treated as new row
Instead of this I want the new row to be associated with the same individual until we know the record in next row is a new one
Like in this case Pramod has two qualifications MTech on row 1 and HSC on row 2 so the value in row 2 should be associated with Pramod and not create a new record and row-3 there is a new value as FirstName and LastName are filled so create a new record here (and this continues) so how to add a check in the flow to get this.

Regards,
Sidhant.

Was this reply helpful? Yes No
Sidhant_02 986 on at

Like (0)

Report

Hi @narayan225 ,
I was thinking of adding a check which checks the length of the get values like:
For which I had created a new SharePoint List named RegisterationData which looks like this
And the excel:

So I tried running the flow but I am facing an issue which is

(The below issue disappeared when the date format was set to ISO)

And when I checked in Power Apps I was able to see data in Row -1
The next qualification HSC, InstituteName: Modern Jr.College was not populated or saved and same for PrimarySkills, Secondary, Project Details

IN SharePoint:

So the new subsequent action (new row value is failing)

(like the very first row as I mentioned earlier is saved but the next rows for the same record are not)

So if you have any idea do let me know.

Was this reply helpful? Yes No
Sidhant_02 986 on at

Like (0)

Report

Hi @narayan225 ,
Just a small update I was able to resolve the issues (some were related to date and blanks)
So now when I upload the file with some data like this, it is saved in the SharePoint list
Just added a variable with the breakline (here by clicking enter in the value) and then used the following expression in the 'Create Item' : replace(item('Apply_to_each')?['Qualification'],variables('Enter'),'<br>')

Now I want to associate the new line records with the same id (like a check to see if the EmployeeId, Email is not filled in then associate the record with the previous row)

The gallery is working fine (but I want to do this which I have just discussed) as per the data
If you know anything please let me know.

Regards,
Sidhant.

Was this reply helpful? Yes No
narayan225 2,547 Super User 2025 Season 2 on at

Like (1)

Report

@Sidhant_02

You can initialise the employeeid as a variable and when using apply to each, you can check if the row has blank employee id, you can set the variable to the existing value (i.e. previous row's employee id).

Hope this helps.

Cheers!

Was this reply helpful? Yes No
Sidhant_02 986 on at

Like (0)

Report

Hi @narayan225 ,
Thanks for the reply, so as you mentioned that I need to create another variable to check the employeeId column, which I created as follows:
Now in the condition you have mentioned I should check whether the employee id field is blank or not, if it is blank associate with the previous employee id how to that is I am not clear with and if the field (employee id is not blank then it should proceed ahead with the normal flow i.e. here the Yes part)

Regards,
Sidhant

Was this reply helpful? Yes No
Sidhant_02 986 on at

Like (0)

Report
Hi @narayan225 ,
I did try this in the condition I added a check whether the employee id is null and in the EmployeeId value I used the following expression which is:

if(empty(items('Apply_to_each')?['EmployeeId']), last(items('Apply_to_each')?['EmployeeId']),items('Apply_to_each')?['EmployeeId'])

I did try with 'outputs' instead of 'last' but that was not allowed it resulted in the following

And when I ran the flow again got the same result

The next question that I had (let's say once I able to associate new rows with the previous/recent EmployeeId value. The next thing was for every section like Qualification, Certifications, Projects I have created a separate lists already to store the respective data. To give you context initially I was making use of Forms to get the user inputs and save
the data in the respective lists, the lists are:
Qualifications:
Certifications:
Projects:
Skills:
Once the data is in proper format based on the current user I want to patch this data in the respective SharePoint list that I just mentioned. So initially I had done this when the sheet was having only record (no new line) using ForAll and Patch

ForAll(Details, Patch(Employee_Qualifications, Defaults(Employee_Qualifications), { Institutename: ThisRecord.University, Qualificationtitle:ThisRecord.Degree, EmployeeId:Office365Users.MyProfile().Mail } ) );

But now how that is to be done I am not sure of?.

Regards,
Sidhant

Was this reply helpful? Yes No
narayan225 2,547 Super User 2025 Season 2 on at

Like (0)

Report

@Sidhant_02

You simply need to create a create item action, that creates the item of the first row. In that create item action, add an expression with if condition to check if employeeid is empty/blank. If it is, set the employeeid to the variable, else set it to employeeid column that you get from apply to each. After that, you can create a variable and assign it to the employeeid column.

This way, on the first run, because there is already a value for employeeid, it will create a new row without a problem and after creating the row, it will assign the variable to be the current employeeid. When the apply to each runs next time, it will have the employeeid blank and the variable will have the previous employeeid. This way when the action runs, it will store previous employeeid value when creating a row which serves the purpose you are looking after.

Unfortunately, I don't have much time to create a SharePoint list to demo this to you.

Hope this helps.

Cheers!

Was this reply helpful? Yes No
Sidhant_02 986 on at

Like (0)

Report
Hi @narayan225 ,
(The first run where in the employee id is stored in the SP List) and then as you said I'm saving the latest value into the variable

And in the next run got the previous employee id that was saved in the last iteration

The expression that I used were:
In the EmployeeID column (Create Item):
if(empty(items('Apply_to_each')?['EmployeeId']),variables('previousEmployeeId'), items('Apply_to_each')?['EmployeeId'] )

For the variable:
items('Apply_to_each')?['EmployeeId']

Now need some help in the other part that I mentioned in my reply on Monday (which is starts with 'The next question I had')

Regards,
Sidhant.

Was this reply helpful? Yes No