cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

When field in list A changes and is a lookup field in List B, need to update all List B items

List A is a custom list with a user entered text column.  List B ihas that column as a lookup from List B.

 

PowerApps has two galleries, List A on left, List B on right.  Clicking on a item in List A shows all items in List B that match that name field from List A.  That works great.

 

The user needs to be able to update columns in List A, and if the text field used as a lookup changtes, then all of the items in List B with that value in the lookup column need to change.  Logically, for each itemin list b where lookup.value -= listA.text, update item in List b so that lookup.value = lista.text.newvalue.

 

I am not having success trying to cobble together PATCH, FORALL, COLLECT, UPDATEIF, etc. to make this happen when the save icon is pressed.  Please show me how.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Andy_Tuke
Level 8

Re: When field in list A changes and is a lookup field in List B, need to update all List B items

I once had to deal with something similar.  With my project, the question was whether the SharePoint list being used as a datasource needed to be used from anywhere apart from the PowerApps.  In our case it didnt so we used PowerApps to control the logic and not the SharePoint lookup field.

Essentially, have a column in List B of type integer, when the app writes a record it can write the ID of the relevant row in List A into this column in List B.  You can then filter List B using a simple check on the ID column matching the ID of List A.  The advantages of this approach is that lookup columns generally fail delgation so you might have scaling issues, and more importantly, the link between the lists is made on the row ID not on the Title field.  The row ID will never change so you can change the Title to your hearts content and not have to worry about it.

If you check out my blog, linked below I have written a post this week about lookup columns, which I will be continuing in the next couple of days.  This type of solution is probably a good thing for me to add at some point, hopefully early next week.

 

regards

Andy
Read my new blog at -
www.powerappssolution.com 

Re: When field in list A changes and is a lookup field in List B, need to update all List B items

I simply added REFRESH() and CLEARCONNECT() functions to the app and it works perfectly.  If the title chnges in an item higher up the chain, it changes on all the records automatically.

 

My apologies for asking an irrelevant question.

3 REPLIES 3
Andy_Tuke
Level 8

Re: When field in list A changes and is a lookup field in List B, need to update all List B items

I once had to deal with something similar.  With my project, the question was whether the SharePoint list being used as a datasource needed to be used from anywhere apart from the PowerApps.  In our case it didnt so we used PowerApps to control the logic and not the SharePoint lookup field.

Essentially, have a column in List B of type integer, when the app writes a record it can write the ID of the relevant row in List A into this column in List B.  You can then filter List B using a simple check on the ID column matching the ID of List A.  The advantages of this approach is that lookup columns generally fail delgation so you might have scaling issues, and more importantly, the link between the lists is made on the row ID not on the Title field.  The row ID will never change so you can change the Title to your hearts content and not have to worry about it.

If you check out my blog, linked below I have written a post this week about lookup columns, which I will be continuing in the next couple of days.  This type of solution is probably a good thing for me to add at some point, hopefully early next week.

 

regards

Andy
Read my new blog at -
www.powerappssolution.com 

Re: When field in list A changes and is a lookup field in List B, need to update all List B items

Andy

 

Your blog post is nicely done, and thanks for the response. I am pretty much doing what you are doing with some exceptions:

  • I have three lists: clients, projects and tasks, so that projects point to clients, tasks point to projects
  • I didn't use ID to link records but did use a lookup in projects=client(title) and tasks=projects(title).  

 

Your comment about IDs not changing is correct, and I also use PowerApps to enforce business rule behavior among SharePoint lists (I have one that handles four different lists linked by ID).  However, in this case, it made sense to me to put the client or project title in the "linked" records so if it were necessary to view the lists in SharePoint, the association would be clear.  And to get the filtering to work on the main screen (select a client to see projects, select a project to see tasks), I still need that title in the items.

 

Right now, the app works just fine EXCEPT that I have changed DisplayMode to View on the title fields because I haven't figured out how to do the sweep PATCH (or whatever) if the title in the hiearcy changes.  By that I mean: if the client name (title) were to change, I would have to update all the project records to match; if the project name changes, then update all task records.

 

 

I just did some testing, directly using SharePoint, and I realize I am asking the wrong question.

 

  1. I edited an item in client changing the title.
  2. I refreshed the project list, and the lookup field in the items also changed the display value to the changed value.

Perhaps all I actually have to do is to refresdh the datasouce in my app.  It directly uses the SP list, but maybe it would make things easier if I did a ClearCollection and then filtered on that collection.

 

Thoughts/

Re: When field in list A changes and is a lookup field in List B, need to update all List B items

I simply added REFRESH() and CLEARCONNECT() functions to the app and it works perfectly.  If the title chnges in an item higher up the chain, it changes on all the records automatically.

 

My apologies for asking an irrelevant question.