How to retrieve data from a different SharePoint list than the linked DataSource

[SilannaIT-Dale]

Hi there,

I am trying to build a SharePoint form in PowerApps that has a repeating table that users can add multiple entries to.

When the user submits the form, all of the generic text/choice fields get saved to SharePoint List 1 and all of the data in the dynamic table gets saved to SharePoint List 2 with a column called "Master ID" where each row's "Master ID" is the same as the SharePoint List 1's "ID" of the form that the data was submitted with (See images as example).

SharePoint List 1:

SharePoint List 2:

SharePoint List 1 Form (dynamic table):

Whenever a user clicks "Save", the items in that row are added to a Collection and a new row in the table (Vertical Gallery) is added.

So for each row in SharePoint List 1, there can be many rows associated with it in SharePoint List 2 (One to Many relationship).

I'm currently using a Collection on the dynamic table (Vertical Gallery) to temporarily store the data and once the user hits submit on the SharePoint List 1 Form, I use the Patch() function to update SharePoint List 2 with the items in the Collection.

The problem I'm having is not when the form is in New mode but when it is in View mode.

The data source that is linked to the SharePoint Form is Share Point List 1, which is good as it retrieved the values for all my other fields in my form, however it can not populate my dynamic table (Vertical Gallery) as the data is in SharePoint List 2.

I thought this would be an easy fix by setting the Items tab on my dynamic table (Vertical Gallery) to be SharePoint List 2 however, for whatever reason, the data won't populate if the Vertical Gallery is in a data card of the form. It will only populate with SharePoint List 2 data if the Vertical Gallery is not in a data card of the form.

So my question is how can I have my SharePoint Form linked to SharePoint List 1 as the DataSource but populate my dynamic table (Vertical Gallery) with SharePoint List 2 data while nested inside a data card of the form?

Yes, I have linked both SharePoint Lists to the form. The dynamic table (Vertical Gallery) will auto populate when it's not inside the SharePoint Form, but the fields go blank when it's inside the form?

Things to note:

I have 3 identical dynamic tables but only 1 will show at any given time based on the Form Mode, New, Edit or View.

This allows me to have different attributes/code on each of the tables depending on the Mode (I know there is probably a better way of doing this but it's just how I have set it up).

Here is the tree view of my PowerApp (SharePoint Form):

Dynamic Table when nested in a data card:

Note that in the image above the Gallery is nested inside a data card. When I have it like this, the Share Point List 2 data does not auto populate.

If I copy and paste the Gallery outside the Share Point Form, the Gallery automatically populates with the correct data (See Below).

What do I need to do to the Items tab on the dynamic table (Vertical Gallery) in order for the Gallery to populate while nested inside the Form?

Note: [@'General Shipments - Items'] is SharePoint List 2

Sorry, I know this is a lengthy question but I'm trying to be as thorough as possible to make the problem clear.

Let me know if you need anymore information.

Thanks in advance!

Dale

[SilannaIT-Dale]

Sorry, one important thing I forgot to add/ask is what the Text formula on my labels in the dynamic table need to be?

They are currently set to ThisItem.Text

I'm assuming the data does not pupulate when its nested in the Form because ThisItem gets overwritten to be SharePoint List 1 instead of SharePoint List 2 as set in the Gallery's data source?

Do I just need to change this code to make it work?

[v-jefferni · Accepted solution]

Hi @SilannaIT-Dale ,

An easy trick to achieve your need is just update the collection with SP list2 entries when going to the form screen in View mode:

ClearCollect(ItemDetails, Filter([@'General Shipments - Items'], 'Master ID' = Value(ListIDValue.Text)));
ViewForm(EditForm1);
Navigate(FormScreen);

The collection is always the data source (Items) of the vertical Gallery, there would be no difference of the form in New mode or View mode.

Best regards,

[SilannaIT-Dale · Accepted solution]

Hi @v-jefferni,

Thanks for your reply!

I tried to do that but for whatever reason, it said 'Master ID' was undefined, even though its a column in my SharePoint List. And no it is not my Title column.

I guess my PowerApp is just bugged.

Anyway, I have tried a new method that works now.

I have enabled the experimental feature "ParseJSON()" and just store my table results in one cell in my SharePoint List 1 in JSON format and just Parse the data into a collection onStart.

If(
    SharePointForm1.Mode <> 1,
    Set(
        tmpCol,
        ForAll(
            Table(ParseJSON(SharePointIntegration.Selected.JSONItems)),
            {
                Length: Text(ThisRecord.Value.Length),
                Width: Text(ThisRecord.Value.Width),
                Depth: Text(ThisRecord.Value.Depth),
                Weight: Text(ThisRecord.Value.Weight)
            }
        )
    )
);
ClearCollect(
    ItemDetails,
    tmpCol
);

Another problem I had with that method was that it would update the collection with the values from SharePoint List 2 but would not update the items in the gallery even when the items tab was set to the ItemDetails collection. It would only render the items if I had a refresh button.

I think this problem is just specific to my App and your solution should theoretically work so thank you for the reply!