Update Multiple Records With Data From Different Table

(0) Share

Report

Posted on by ar87

353

Hi all,

I have two tables: Resources and Skill Sets. The relation between Resources and Skill Sets is 1:N, meaning that one resource (employee) can have many skill sets. Skill set is defined as a combination of skill level and skill name, e.g. 1-Beginner Azure VMs.

After a user creates a resource inside the app, they have to add at least one skill set to that resource. All that is working perfectly fine and I am able to relate any number of skill sets to the resource using ForAll and Patch function.

As you can see Resource with the ID: 978dff0f-9766-ee11-9ae7-6045bdd62e33 has three different skill sets:
Resource table

Skill Set table

However, I also need to concatenate all those skill sets for each resource and save it inside Resource table as a string in 'Skill Set' column (the empty one). I've tried using the same logic as with relating resources with skill sets (ForAll and Patch functions), but it doesn't work.

Any advice is appreciated.

Thank you.

Best

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Sort by

ANB 7,252 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!
HI @sdedic , Do you want AWS CloudFormation, Amazon CloudWatch, React.js (Class components) to be store in 'Skill Set' column? Below will help you to concate:

Concat(Filter(Skill Set Table, Resource_SkillSet = 978dff0f-9766-ee11-9ae7-6045bdd62e33), SkillSet_SkillName, ", ")

Below will help to patch:

Patch(Resource table, LookUp(Resource = "978dff0f-9766-ee11-9ae7-6045bdd62e33"),{Skill Set: Concat(Filter(Skill Set Table, Resource_SkillSet = 978dff0f-9766-ee11-9ae7-6045bdd62e33), SkillSet_SkillName, ", ")})

The same above code could be used in combination with ForAll to patch multiple record.

-----------------------------------------------------------------------------------------------------------------------------

I hope this helps.

Please click Accept as solution ✅if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs up.👍

Thanks,
ANB

Was this reply helpful? Yes No
CU-18081211-6 9,270 Moderator on at

Like (1)

Report
Copy link

Link copied!

@sdedic ,

After SkillSet table patch you should use a:

Patch(Resource,

Lookup(Resource, ResourcesGUID=currentResource),

{‘Skill Set’:Concat(Filter(SkillSetTable, Resource_SkillSet.Resources=currentResource),Text(Skill_Level&”;”)}
)

The formula is theoretical and need to be updated to specific columns name.

Was this reply helpful? Yes No
ar87 353 on at

Like (0)

Report
Copy link

Link copied!
Hi @ANB,

Thank you for your help. Unfortunately, I'm still getting the same error. The error is related to the 'Skill Set' column that needs to be updated.

Since I need to update 'Skill Set' column for all resources created at that point, I need to combine ForAll and Patch functions. So, I tried integrating your formula to ForAll but the error remains.

Here is complete formula I am using:

ClearCollect(resourcesCollection, galResources.AllItems); ForAll(resourcesCollection, Patch(Resources, LookUp(Resources, Resource = resourcesCollection[@Resource]), { 'Skill Set': Concat( Filter('Resource Skill Set', Resource_SkillSet.Resource = resourcesCollection[@Resource], SkillSet_SkillName.'Skill Name'), ",") } ));

Here is the error I am getting for the above formula:

Though I am not sure if the filter condition is correct, I can see that my datasource 'Resource Skill Set' is not being recognized in this particular formula. Once I figure out how to resolve that issue I can see if matching condition in filter is correct or not.

Do you have any clue why the datasource isn't recognized in the Patch function for this particular column?

EDIT:
When I used collection as a data source for 'Resource Skill Set', the above formula worked. The only thing now is that I am getting some commas (", ,") stored under 'Skill Set' column, and not full skill name and skill level.

Best

Was this reply helpful? Yes No
ANB 7,252 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hi @sdedic , Can you try this:
ClearCollect(resourcesCollection, galResources.AllItems); ForAll(resourcesCollection As var, Patch(Resources, LookUp(Resources, Resource = resourcesCollection[@Resource]), { 'Skill Set': Concat( Filter('Resource Skill Sets',Resource_SkillSet = var.Resource), SkillSet_SkillName, ", ") } ) );
-----------------------------------------------------------------------------------------------------------------------------

I hope this helps.

Please click Accept as solution ✅if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs up.👍

Thanks,
ANB

Was this reply helpful? Yes No
ar87 353 on at

Like (0)

Report
Copy link

Link copied!

Thank you both. You helped me build the working formula and now I have a string of skill sets appended to the resource.

The only change I had to make to your solution was to create a collection locally for skill sets and then use them in Patch function. Otherwise I was getting that error that "the specified column is not accessible in this context."

Once again thank you!

Was this reply helpful? Yes No