Unanswered

Update items in sp list with id from other sp list (primary key - foreign key relationship)

(0) Share

Report

Posted on by Masakichi123

I need urgent help!

I have two sharepoint lists:
SP List 1: Skills (Relationship: clear data)
ID: Primary Key
Title: Text
Provider: Text
Approval Status: Automatically
Keywords: multiple choice column

SP List 2: Skills Employees (Relationship: many data)
RefID: Foreign Key, Number
Title: Text
Provider: Text
level: text
NameEmployee: Person
email: body
Team: Text

I only transfer title, provider and keywords

I would like that if I change something in the Skills list using Powerapps, that it will also be changed in the Skills Employees list (Relationship 1:n).

I've tried Updateif, Patch, Collections...it just doesn't work...

How do I get one list to notice that the primary key in sp list 1 is the same as in sp list 2???

Thanks for any help

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Ami K 15,679 Super User 2024 Season 1 on at

Like (1)

Report
@Masakichi123 , you can use Patch. For example:

Patch( Skills, LookUp( Skills, ID = Gallery1.Selected.ID ), { Title: "Something", Provider: "Something", Keywords: "Something", 'Approval Status': "Something" } ); Patch( 'Skills Employees', LookUp( 'Skills Employees', RefID = Gallery1.Selected.ID ), { Title: "Something", Provider: "Something", Keywords: "Something" } )

If you're saving data to the Skills list using SubmitForm, you can apply similar logic to the OnSuccess property of the Form to Patch the Skills Employees list. E.g.:

Set( varRecord, Self.LastSubmit ); Patch( 'Skills Employees', LookUp( 'Skills Employees', RefID = varRecord.ID ), { Title: "Something", Provider: "Something", Keywords: "Something" } )

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

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

Was this reply helpful? Yes No
PowerNuggets 360 on at

Like (1)

Report

Hi @Masakichi123

You can use lookup value in the second list to hold the ID of the record from the first list to have 1:N relationship, then use a flow to fetch all records related details from the second list

Below video may help
https://www.youtube.com/watch?v=KYsMNoc7aPY

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.

MD

Was this reply helpful? Yes No
Masakichi123 58 on at

Like (0)

Report

Hi @Amik ,

thanks for your help.
It works partially; now a change is made to the employee skills for a value. If there are multiple values in the Skills Employee List, then only the first one will be adjusted and the others will remain unchanged.
What can I do to change all the values that are found?
Thanks a lot in advance!

Was this reply helpful? Yes No
Masakichi123 58 on at

Like (0)

Report

Does it possibly work with "ForAll" or with "Filter"?

Was this reply helpful? Yes No
Verified answer

Ami K 15,679 Super User 2024 Season 1 on at

Like (0)

Report
@Masakichi123 that's a very different question. If you want to update List 2 based on multiple changes to List 1, I would suggest using Power Automate to achieve this as @PowerNuggets rightly suggested.

Power Automate Solution: https://www.youtube.com/watch?v=looyVm_8OKI

To achieve this in Power Fx, you would need an expression like below. However, I find this unnecessarily complicated given Power Automate can achieve this simply and faster.

With(//produce a filtered table of records in the parent table only where a child record does exist { _parents_with_child: Filter( AddColumns( Skills, "child_ID", LookUp( 'Skills Employees', RelatedID = Skills[@ID], RelatedID ) ), !IsBlank(child_ID) ) }, ForAll( _parents_with_child, UpdateIf( 'Skills Employees', RelatedID = _parents_with_child[@ID], { Title: _parents_with_child[@Title], Title: _parents_with_child[@Provider], Title: _parents_with_child[@Keywords] } ) ) )

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

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

Was this reply helpful? Yes No