cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Two collections linked SP Lists Repeating Rows - please help me before i lose my mind....

I am trying to get data from a repeating rows table (based on Shane Young's  tutorial  https://www.youtube.com/watch?v=xgznk4XlPCo).

I have collected description data in 2 edit Forms that are patched to a SP list called 'Change Management Master List'. I am using the repeating tables to do a risk assessment. The repeating tables collect the data in a collection called RiskCollection and write it to a second SP list called "Change Management Risk Assessment".

What i want to be able to do is to get the ID and the Change Title from the last submitted item in the Change Management Master List and patch that along with all the data collected in the repeating table (RiskCollection) to the "Change Management Risk Assessment" SP List.  So i want the SP list it to look like this:

 

IDChange TitleHazard DescriptionCurrent ControlsEtc
1New MachineHazard 1Control1 
1New MachineHazard2Control2 
1New MachineHazard3Control3 
2New ProcessHazard1Control1 
2New ProcessHazard2Control2 
2New ProcessHazard3Control3 

 

I have set the OnSuccess of the Edit Form (DetailsForm) to create a new collection (LastRecordDetails) that collects the ID and ChangeTitle from the last submitted record :

 

Rp1.JPG

 

I've created the collection for the repeating row as per the following:

 

RP2.JPG

 

These two steps seem to have worked fine. 

 

The problem i am having is to get the ID & Change Title data from the LastRecordDetails collection to write back to the Change Management Risk Assessment SP List as per the table above.  This is the formula i am currently using. Everything works fine except for the highlighted section where i have referred back to the items in the LastRecordDetails Collection 

RP3.JPG

 

This is what the two SP lists look like - you can see the highlighted columns in the second list that do not update with the ID and changeTitle from the LastRecordDetails Collection

 

RP4.JPG

 

 

 

RP5.JPG

If anyone could help me solve this tricky problem, i'd be ridiculously grateful!!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Anonymous ,

Ok. I'm very glad that you've solved your problem!

But my reply is the same principle.

Just not use Form.LastSubmit, use Last(listname) to represent the last record that you latest submitted.

If you do not have any other question, could you mark my answer as solution?
Thanks!

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yutliu-msft
Community Support
Community Support

Hi @Anonymous ,

Do you mean that you did not update CMMasterID field and 'Change Title' field successfully?
Could you tell me the data type of these two fields in Change Management Risk Assessessment?
I assume that CMMasterID field is number type and 'Change Title' field is text type.

If so, the key point is "LastRecodDetails.ID" represents a table, "LastRecodDetails.ChangeTitle" represents a table too.

While CMMasterID field and 'Change Title' field needs value.

You need to use formula like this to represents a field value in a collection: 

First(LastRecodDetails).ID
//record.fieldname

So please try this formula:

ForAll(....,
Patch(....,
...,
{...,....,....,CMMasterID:First(LastRecodDetails).ID,'Change Title':First(LastRecodDetails).ChangeTitle
}
)
)

 

Next time, please post your formulas in text , not in screenshoot, which will help me modify your formula.

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Phoebe, thanks for your response, i've tried out your suggestion, but it still doesn't seem to work. Both the  'CMMMasterID' field and the 'Change Title' fields are text type fields:

RP6.JPG

 

I entered the formula as per your advice:

 

ForAll(RiskCollection, If(!IsBlank(HazLine),Patch('Change Management Risk Assessessment', Defaults('Change Management Risk Assessessment'),{'Hazard Description':HazLine, Risk:RiskLine, 'Current Controls':CurrentControls, Con1:Consequence, L1:Likelihood, 'Risk Ranking':RR, Planned:Planned, Con2:Con2, Lik2:Like2, FinalRR:FinalRR, CMMasterID: First(LastRecordDetails).ID, 'Change Title': First(LastRecordDetails).ChangeTitle})));

 

But it still didn't update the columns correctly (see highlighted below). Does it make any difference that :LastRecordDetails is a collection created on submission of an edit form?

RP7.JPG

Any other ideas what i might be doing wrong?

 

Here are my other formulas:

 

Editform (onSucess) (to create once form is submitted that contains the ID and the Change Title)

ClearCollect(LastRecordDetails,{ID: DetailsForm.LastSubmit.ID,
ChangeTitle: DetailsForm.LastSubmit.'Change Title'})

 

Save Button OnSelect ( to submit ediforms and create RiskCollection

Patch(('Change Management Master List'), Defaults('Change Management Master List'), DetailsForm.Updates, DetailsForm2.Updates, HazardIdForm.Updates);

ClearCollect(RiskCollection,{HazLine: "", CurrentControls:"", Consequence:"", Likelihood:"", RR:"", Planned:"",Con2:"", Like2:"",FinalRR:"", ID:"", ChangeTitle:""});

Save Button OnSelect (to submit all information from the RiskCollection the the SP List and Update the CMMasterID & Change Title Columns from the LastRecorDetails collection):

ForAll(RiskCollection, If(!IsBlank(HazLine),Patch('Change Management Risk Assessessment', Defaults('Change Management Risk Assessessment'),{'Hazard Description':HazLine, Risk:RiskLine, 'Current Controls':CurrentControls, Con1:Consequence, L1:Likelihood, 'Risk Ranking':RR, Planned:Planned, Con2:Con2, Lik2:Like2, FinalRR:FinalRR, CMMasterID: First(LastRecordDetails).ID, 'Change Title': First(LastRecordDetails).ChangeTitle})));

 

Please let me know if you need any more information. Thanks so much for your help!

 

C

Hi @Anonymous ,

Ok, I think I know where's your problem.

1)CMMasterID field is text type, which ID is number type.

2)You use patch function to submit DetailsForm. However, "DetailsForm.LastSubmit" will only works when you use submitform function to update data.

So to solve your problem, I suggest you not use "DetailsForm.LastSubmit" to create collection.

 

Try this:
1)set the editform's OnSuccess:

ClearCollect(LastRecordDetails,{ID: Last('Change Management Master List').ID,
                                ChangeTitle:Last('Change Management Master List').'Change Title'})

2)Save Button OnSelect

Patch('Change Management Master List', Defaults('Change Management Master List'), DetailsForm.Updates, DetailsForm2.Updates, HazardIdForm.Updates);
ClearCollect(RiskCollection,{HazLine: "", CurrentControls:"", Consequence:"", Likelihood:"", RR:"", Planned:"",Con2:"", Like2:"",FinalRR:"", ID:"", ChangeTitle:""});

3)Save Button OnSelect 

ForAll(RiskCollection, If(!IsBlank(HazLine),Patch('Change Management Risk Assessessment', Defaults('Change Management Risk Assessessment'),{'Hazard Description':HazLine, Risk:RiskLine, 'Current Controls':CurrentControls, Con1:Consequence, L1:Likelihood, 'Risk Ranking':RR, Planned:Planned, Con2:Con2, Lik2:Like2, FinalRR:FinalRR, CMMasterID: Text(First(LastRecordDetails).ID), 'Change Title': First(LastRecordDetails).ChangeTitle})));

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks heaps for your help, but solved it using this post:

 

https://powerusers.microsoft.com/t5/Building-Power-Apps/Why-does-this-formula-not-update-the-SP-list...

 

Variables were the key!!

Hi @Anonymous ,

Ok. I'm very glad that you've solved your problem!

But my reply is the same principle.

Just not use Form.LastSubmit, use Last(listname) to represent the last record that you latest submitted.

If you do not have any other question, could you mark my answer as solution?
Thanks!

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (3,784)