Patch to Multi-Select field?

[simms7400]

Hi Guys -

I have a situation where I need to patch to a multi-select field.

I have a field where a user selects 1 or more values from a ComboBox. These values are the "Aliases" to a data element member from our financial systems. Behind the scenes, based on the Alias selections, the unique ID's are also added to a DataCard in comma delimited format int he order the Aliases are selected.

These values are managed in another SharePoint List.  Therefore, if one of the Aliases changes in the SharePoint List where they are managed, I need to be able to scan SharePoint List 2 for the unique ID and then change the Alias accordingly.

Here is a picture to illustrate what I need:

So if an Alias in SharePoint List 1 changes, I need to update SharePoint List 2 accordingly. Is this possible? THere are times this WONT be in a comma delimted list and I have that logic...I need just need this nee logic.

Thank you!

[v-siky-msft]

Hi , @simms7400 

First of all,  PFDAliaslink column should be the LookUp column that allowed multiple value, and you also need to check 'add a column to show each of these additional fields' feature, just choose Name column, and rename to PFDIDlink .

In addition, you can try the following formula to patch multiple alias to that lookup fied.

  -  Patch('SP list' ,First('SP list'), {PFDAliaslink:ComboBox1.SelectedItems}) 

Note: This formula is simple example, it patch selected Alisa to the first record, you should modify to fit your app. Reference: Patch function 

So when PFDAliaslink is patched with some Alias, the PDFIDlink would also be added the corresponding Names.

Best regards,

Sik

[simms7400]

@v-siky-msft 

Hi, thank you for the reply!  So, I don't want to make any config changes to SP side.  I'm populating both SP fields in comma delimited form as I DO NOT have any values to chose from on the SharePoint List for either.  Reason being, its forever changing.

This is how I have it setup on the PowerApps side where I want to patch TO:

PFDAlias_Link:

I have a ComboBox embedded in this DataCard with a collection as the Source.  This collection is a list of all Name/Aliases from SharePoint List 1 i mentioned in my first post.

The DefaultSelectedItems for this property is as follows:

If(InvestmentEditForm.Mode = New,
	Filter(DevicePlatformList, Alias = "Not Applicable"),
	InvestmentEditForm.Mode <> New, Filter(DevicePlatformList, Alias in Split(ThisItem.Device_Platform, ", ")))

PFDID_Link is as follows:

The Default property is set to the following:

Concat(Filter(DevicePlatformList, Alias in CB_Device_Platform.SelectedItems.Alias),Name,",")

"CB_Device_Platform" is the ComboBox embedded in PFDAlias_Link.

Therefore, both fields submit the comma delimited list directly to SharePoint.  What I need to be able to do is if one of those Alias values change (the ID's will never change, obviously) from SharePoint List 1 (the list that is the source of the collection) I need to be able to scan SharePoint List 2 and update accordingly. The plan is to use a patch command on the OnChange property from SharePoint List 1.

Is this possible?

[v-siky-msft]

Hi @simms7400 ,

Model a one-to-many relationships (lookup column) is the best approach of cascading modification. It will be simple and efficient to hit your target.

However, If you stick to your own path, you can use substitute function to replace all changed Alisa when submitting the form.

First, you should save the previous Alias and current Alias as Variables.

Then, set the following code to OnSuccess of form (If using patch, just add the codes behind it.)

ClearCollect(Col,'SP list2');ForAll(Col,UpdateIf('SP list2',true,{PFDAlias_Link:Substitute(PFDAlias_Link,Previous_Alias,Current_Alias)}))

Once you submit the modification to Alisa in SP list1, All same Alisa in SP list2 will also be update.

Best regards,

Sik

[simms7400]

@v-siky-msft 

Thank you so much! I will try the substituion approach first.  And lastly, what do you mean by this?  (If using patch, just add the codes behind it.)

Should Ibe using patch? What do you mean? Thanks!

[v-siky-msft]

Hi @simms7400 ,

I was not sure how you save the data, I meant if you are using Patch function, just add the formulas behind it.

If you are using submitform, Just forget it.

Best regards,

Sik

[simms7400]

@v-siky-msft  I'm just using submit, thanks!

Also, would this formula take awhile ro run? The SP List I am updating (SP List 2) is 500 rows and about 17 columns. I assume the Update command iterates through every record?  is there a way to just have it focus on 1 column?

[simms7400]

@v-siky-msft 

So I added some filtering to shrink the "Col" as much as possible and it definitely helped. Here is the entire "OnSuccess" formula for your viewing pleasure:

ClearCollect(Col, ShowColumns(Filter(rdInvestments,Not(Device_PFD_Link="Not Applicable")),"Device_Platform"));
If(Not(NodeAlias=NodeAlias_OC),
	ForAll(Col,UpdateIf(rdInvestments,true,{Device_Platform:Substitute(Device_Platform,NodeAlias,NodeAlias_OC)}))
))

I am running into one issue though.  The change only works on the first try. I can load my app, make the change, and I see it relfected correctly in "SP List 2".  However if I make another change to the same one that worked previously, nothing is updated in "SP List 2".

I even have a Refresh happening after update to "SP List 2".  Any ideas why?