Repeating table in "customize form" for SharePoint

[DancingCatx64]

Hi,

I am trying to use the "customize form" option under Power Apps menu in SharePoint to add 2 repeating tables to a form.

Now I have been able to follow a guide from https://www.c-sharpcorner.com/article/create-repeating-section-in-powerapps-new-form-step-by-step-pa... that has what I am after.

Now I have been able to get this working when building a standalone app, but I really need to have this working on the form that you get when you double click on a SharePoint list item (to view or edit) or when using the new link in the Sharepoint list.

Does anyone know is it possible to make that guide with the "customize form" option I have seen plenty of guides for having data stored in several SharePoint lists, but this is something that I want to avoid.

Thank you

[DancingCatx64]

@RandyHayesThank you for getting back to me.

The 2 lists to be connected to the master list is called

Assessment_Data

Action_List_Data

Both of those list has a mix of different field types (single line, multi line, date picker, choice and people picker) Both of those lists also has a column called Base_ID (to aid with linking with the master list).

Have a good evening (10AM here in Australia) and I'm looking forward to getting this big project up and running.

[DancingCatx64]

@RandyHayesI've gone ahead and added 2 galleries to the form, I gave then the same name as the 2 SharePoint lists (ti make it easy to reference. I also added a + icon outside the gallery (I hope that right). Do I need to add all the textboxes etc inside the 2 galleries?

Edit: I also added the 2 SharePoint list in the data source list as well.

[RandyHayes]

@DancingCatx64 

Perfect on the Base_ID...that will be our relation field.

So here is what to go about doing - follow along closely as there are quite a few steps.

1) We will use a collection in this case for both lists.  I don't usually like using them unless specifically necessary and are the right tool for the job, but in this instance they will be helpful to incorporate what needs to be done and thus are the easiest tool for the job.

2) So, put a toggle control in your app (it does not need to be visible). Let's call it tglResetLists

In the OnCheck action of the toggle, put the following formula:

Concurrent(
    ClearCollect(colAssessmentData, 
         AddColumns(
             Filter(Assessment_Data, Base_ID = SharePointIntegration.SelectedListItemID),
             "_state", ""
         )
    ),
    ClearCollect(colActionData, 
         AddColumns(
             Filter(Action_List_Data, Base_ID = SharePointIntegration.SelectedListItemID),
             "_state", ""
         )
    )
);
UpdateContext({lclResetLists: false})

In the Default property of the toggle, put the following: lclResetLists

3) Now, in the OnVisible action of your screen, ADD the following to the end of any formula that is there:

   UpdateContext({lclResetLists: true})

4) Now, create two Galleries (which you've done).  Not sure what you named them, but if they are the same name as the datasource, I would at least prepend them with gal to distinguish them as galleries.  So, one for your Assessment records (galAssessments) and one for your ActionData (galActions).  I'll use these names and you can substitute as needed.

Set the Items property of the galAssessments to : Filter(colAssessmentData, !(_state="D"))

Set the Items property of the galActions to : Filter(colActionData, !(_state="D"))

Set the DisplayMode property of BOTH galleries to :  yourFormName.DisplayMode

5) Adjust your Gallery to add texinput controls and anything else you want to be displayed/edited by the user.

For all of the controls in your Gallery, set the OnChange action properties to : 

UpdateIf(colAssessmentData, ID=ThisItem.ID, {_state:  If(ThisItem._state="A", "A", "E"), columnName1: Self.Text})  

On each individual control in the Gallery, change the "columnName1" in the above to the proper column name for that control.  Also adjust the type as needed - ex. if you have a DatePicker, then Self.SelectedDate, etc.

Repeat the above for the controls in the Action Data gallery except use the colActionData in the UpdateIf.

Also, set the Default properties of all the controls to their appropriate fields : i.e. ThisItem.columnNameForThatControl

6) I would then place a trashcan/delete icon IN both galleries.  In the OnSelect action of that icon, place the following formula:  

If(ThisItem._state = "A", RemoveIf(colAssessmentData, ID=ThisItem.ID), UpdateIf(colAssessmentData, ID=ThisItem.ID, {_state: "D"}) )

and the same again except change to colActionData for the other icon in the action list.

7) Next, I would place an icon OUTSIDE of each gallery (which you have done) to add new items (usually the Add/Plus icon).  In the OnSelect action of that icon, set the following formula:   

Collect(colAssessmentData, Patch(Defaults(Assessment_Data), {ID:Max(galAssessments.AllItems, ID)+1, _state:"A"}))

And again, do the same for Actions except change collection name, gallery name, and list name.

😎 NOW...the big deal!  In the OnSuccess action of the main form, set the following formula:

// ASSESSMENT LIST
With({_assessmentRecords:
    ForAll(colAssessmentData As _items,
        With({_item: LookUp(galAssessments.AllItems, ID=_items.ID)},
            {ID: _item.ID,
             _state: _item._state,
             Base_ID: Self.LastSubmit.ID,
             Column1: _item.controlNameForColumn1.Text,
             ...etc for all columns and controls...
            }
        )
     )
     },

     // Write changed items to list.
     Patch(Assessment_Data, 
         ForAll(
             ShowColumns(Filter(_assessmentRecords, _state="E"), 
                 "ID", "Col1", "Col2", ...etc...list all column REAL names
             ) As _item, 
             Patch(_item, {ID:_item.ID})
         )
     );

     // Create new items
     Collect(Assessment_Data, DropColumns(Filter(_assementRecords, _state="A"), "ID", "_state"))

     // Remove deleted records
     RemoveIf(Assessment_Data, ID in Filter(_assessmentRecords, _state="D").ID)
    
);

// ACTION LIST
With({_actionRecords:
    ForAll(colActionData As _items,
        With({_item: LookUp(galActions.AllItems, ID=_items.ID)},
            {ID: _item.ID,
             _state: _item._state,
             Base_ID: Self.LastSubmit.ID,
             Column1: _item.controlNameForColumn1.Text,
             ...etc for all columns and controls...
            }
        )
     )
     },

     // Write changed items to list.
     Patch(Action_Data, 
         ForAll(
             ShowColumns(Filter(_actionRecords, _state="E"), 
                 "ID", "Col1", "Col2", ...etc...list all column REAL names
             ) As _item, 
             Patch(_item, {ID:_item.ID})
         )
     );

     // Create new items
     Collect(Action_Data, DropColumns(Filter(_actionRecords, _state="A"), "ID", "_state"))

     // Remove deleted records
     RemoveIf(Action_Data, ID in Filter(_actionRecords, _state="D").ID)
    
);

// REFRESH COLLECTIONS
UpdateContext({lclResetLists: true})

And that is it.  Seems like a lot of steps, but they are pretty basic.

The pain comes from having to list out the names of columns that you need in the formulas properly.

Keep in mind that functions like ShowColumns will want to have the REAL name of the column in the formula.

If you get any errors on column names check that the name is the real name as defined in SharePoint.  You can find that in the list settings of the list, click on the column name to review its settings.  At the end of the URL on that column setting page will be the real name of the column.

SO....give the above a go and see where the wheels come off or if all goes smoothly.

I vetted this out in a similar sample app that I had, just modified slightly based on your specific scenario.  All worked great there.

Let me know how it goes. 

[DancingCatx64]

Hey @RandyHayes,

Thank you for the very detailed post. Now I have followed through the instructions and I am stuck on the last part  (OnSuccess action of the main form).

This is the error that I am getting:

And here's is the below code that I have (so far)

// ASSESSMENT LIST
With({_assessmentRecords:
    ForAll(colAssessmentData As _items,
        With({_item: LookUp(galAssessments.AllItems, ID=_items.ID)},
            {ID: _item.ID,
             _state: _item._state,
             Base_ID: Self.LastSubmit.ID,
             Title: _item.TextInput1.Text
            // ...etc for all columns And controls...
            }
        )
     )
     },

     // Write changed items to list.
     Patch(Assessment_Data, 
         ForAll(
             ShowColumns(Filter(_assessmentRecords, _state="E"), 
                 "ID", "Title"//, "Col2", ...etc...list all column REAL names
             ) As _item, 
             Patch(_item, {ID:_item.ID})
         )
     );

     // Create new items
     Collect(Assessment_Data, DropColumns(Filter(_assementRecords, _state="A"), "ID", "_state"))

     // Remove deleted records
     RemoveIf(Assessment_Data, ID in Filter(_assessmentRecords, _state="D").ID)
    
);

// ACTION LIST
With({_actionRecords:
    ForAll(colActionData As _items,
        With({_item: LookUp(galActions.AllItems, ID=_items.ID)},
            {ID: _item.ID,
             _state: _item._state,
             Base_ID: Self.LastSubmit.ID,
             Title: _item.TextInput1_1.Text
             //...etc for all columns and controls...
            }
        )
     )
     },

     // Write changed items to list.
     Patch(Action_Data, 
         ForAll(
             ShowColumns(Filter(_actionRecords, _state="E"), 
                 "ID", "Title"//, "Col2", ...etc...list all column REAL names
             ) As _item, 
             Patch(_item, {ID:_item.ID})
         )
     );

     // Create new items
     Collect(Action_Data, DropColumns(Filter(_actionRecords, _state="A"), "ID", "_state"))

     // Remove deleted records
     RemoveIf(Action_Data, ID in Filter(_actionRecords, _state="D").ID)
    
);

// REFRESH COLLECTIONS
UpdateContext({lclResetLists: true});
ResetForm(Self); RequestHide()

For now, (while getting an understanding on how things come together and making troubleshooting easier), I have just target on the Title field - Once I know that I got the basic covered, I will then go and add all the other fields one by one.

Can you please advise where I may have gone wrong?

Once I get this sorted, I will start to use proper names on all the controls.

Cheers

[RandyHayes]

@DancingCatx64 

Yes, you have a comma at the end of the last column of your record...it is expecting that you are going to include another column.

[DancingCatx64]

Thank you for the head up @RandyHayes , I did remove that spray , after making the post but the error still shows the same message.

After going through the entire code, I have found that was a missing ; on the end of the 2 lines for

// Create new items

Once sorted I then went and tested the setup.

Now I am able to save data but when I went back to view the data, nothing showed up. I went and checked the 2 extra SharePoint lists, and sure enough data is being saved.

I un hidden the tglResetLists toggle (just to see if it doing anything) and I suspect that it not firing off when it should.

I hit the switch (and changed it back) and it brings up the data. Editing works, adding new items works but when it comes to deleting, nothing happens.

Clicking on the trashcan does remove that row on the screen, but doesn't remove it from the SharePoint list when you press save.

I'm in the process of starting from the top down just in case I missed something but thought I would post here to let you know how it going.

Other than that, things are looking pretty good here 🙂

Edit (for the 3rd time), does the toggle control go inside or outside the form?

[RandyHayes]

@DancingCatx64 

Well, progress is always good!

Sorry that there might be errors in the formulas I sent.  It's always a challenge to get the syntax right when typing freehand in the forum editor.  There is nothing to check syntax or correct issues.

First off...sounds like an issue on the toggle.  The toggle should be outside of the form and outside of the gallery, just randomly placed on the screen itself.

The fact that it is not getting "fired" is strange.  We might need to investigate that issue further.  But, make sure you did step 3 in the steps I provided.

I do see the syntax issues in the formula provided.  I have it here below just to post what (I believe) are the corrections needed for that:

// ASSESSMENT LIST
With({_assessmentRecords:
    ForAll(colAssessmentData As _items,
        With({_item: LookUp(galAssessments.AllItems, ID=_items.ID)},
            {ID: _item.ID,
             _state: _item._state,
             Base_ID: Self.LastSubmit.ID,
             Title: _item.TextInput1.Text
            // ...etc for all columns And controls...
            }
        )
     )
     },

     // Write changed items to list.
     Patch(Assessment_Data, 
         ForAll(
             ShowColumns(Filter(_assessmentRecords, _state="E"), 
                 "ID", "Title"//, "Col2", ...etc...list all column REAL names
             ) As _item, 
             Patch(_item, {ID:_item.ID})
         )
     );

     // Create new items
     Collect(Assessment_Data, DropColumns(Filter(_assementRecords, _state="A"), "ID", "_state"));

     // Remove deleted records
     RemoveIf(Assessment_Data, ID in Filter(_assessmentRecords, _state="D").ID)
    
);

// ACTION LIST
With({_actionRecords:
    ForAll(colActionData As _items,
        With({_item: LookUp(galActions.AllItems, ID=_items.ID)},
            {ID: _item.ID,
             _state: _item._state,
             Base_ID: Self.LastSubmit.ID,
             Title: _item.TextInput1_1.Text
             //...etc for all columns and controls...
            }
        )
     )
     },

     // Write changed items to list.
     Patch(Action_Data, 
         ForAll(
             ShowColumns(Filter(_actionRecords, _state="E"), 
                 "ID", "Title"//, "Col2", ...etc...list all column REAL names
             ) As _item, 
             Patch(_item, {ID:_item.ID})
         )
     );

     // Create new items
     Collect(Action_Data, DropColumns(Filter(_actionRecords, _state="A"), "ID", "_state"));

     // Remove deleted records
     RemoveIf(Action_Data, ID in Filter(_actionRecords, _state="D").ID)
    
);

// REFRESH COLLECTIONS
UpdateContext({lclResetLists: true});
ResetForm(Self); RequestHide()

Let me know how you're progressing.  I believe you're almost there.

[DancingCatx64]

Thank you for the head-up @RandyHayes regarding the toggle - I do believe that I have it outside the 2 galleries and form. I also checked step 3 and I did indeed put the formula in the OnVisible section for the screen.

Right now it's Saturday here in Australia, so I'll be taking a few days break from this but I will keep you posted regarding the long OnSuccess formula.

[DancingCatx64]

Ok I am back at this from my nice weekend.

So I double check step 3 and the toggle is outside both galleries and the form. As a test, I logged into Office 365 in a private window, and I was able to get the toggle to go off by itself once. Reloading the entire browser window doesn't do anything. So it not predictable when the toggle will go off.

Also there's still issues with deleting - press the trash can icon will remove that row on the screen but when you press save, nothing happens.

[RandyHayes]

@DancingCatx64 

I'm glad you had a great weekend...now let's get your week started right with getting this all working for you.

So, let's make the following change for step 2 and 3.

First, set the Default property of the toggle to : glbReset

Then in the OnCheck formula, change the UpdateContext({lclResetLists: false}) to Set(glbReset, false)

Now, in the OnEdit, OnNew, and OnView actions of the SharePointIntegration object, ADD the following to the formula already there: Set(glbReset, true)

Let's see if that gets the toggle flipping properly.