I need ideas for two custom lists on sharepoint

[Gorilla_8]

hello guys, the problem is as follows, I have two sharepoint lists personalized with powerapps and they have table repeating, however I need the second list to update with the edition of the first list, is it possible?

For example:

I have one list with 1 item: 

ID: 1

Title: Expense
Cost: 89,90

Exchange rate: 5,15

Total converted: 462,99

The second list:

MasterID: 1
ID: 1

Title: Fast food

Cost: 20

Exchange rate: 5,15

MasterID: 1

ID: 2

Title: Restaurant
Cost: 60
Excange rate: 5,15


MasterID: 1

ID: 3

Title: Breakfast
Cost: 9,90
Exchange rate: 5,15

Would there be a way to change the value in the second list, and does it update the value in the first list as well?

Like, if I changed the cost of an item on the second list, and it is updated on the first list and changes the total spent as well

Thanks for the help!

[v-yutliu-msft]

Hi @Gorilla_8 ，

Do you want to update list1 based on list2?

Could you tell me

1)whether do you only want to update list1 of the record with data Title: Expense by using list2?

2)the data type of Cost and 'Exchange rate' field in list2?

I assume that you  only want to update list1 of the record with data Title: Expense by using list2 and these two fields are number type.

If so, try this formula when you update list2:

SubmitForm(Form1);//I assume that you update list2 by using form
Patch(list1,LookUp(list1,ID=Form1.LastSubmit.MasterID),{Cost:Sum(Filter(list2,MasterID=Form1.LastSubmit.MasterID),Cost),
'Total converted': Sum(Filter(list2,MasterID=Form1.LastSubmit.MasterID),Cost)*Form1.LastSubmit.'Exchange rate'
}
)

Best regards,

[Gorilla_8]

Hi @v-yutliu-msft , Thanks again for your reply....

So,

1)whether do you only want to update list1 of the record with data Title: Expense by using list2?

Yes!

2)the data type of Cost and 'Exchange rate' field in list2?

It's number, and I think I have to create a column on another list to update, is that correct?


Thanks again for your reply.

[v-yutliu-msft]

Hi @Gorilla_8 ,

Do you only want to update Cost filed and Total converted field in list1?

If so, you do not need to create another field.

You just need to use this formula to update list2 and list1:

SubmitForm(Form1);//I assume that you update list2 by using form
Patch(list1,LookUp(list1,ID=Form1.LastSubmit.MasterID),{Cost:Sum(Filter(list2,MasterID=Form1.LastSubmit.MasterID),Cost),
'Total converted': Sum(Filter(list2,MasterID=Form1.LastSubmit.MasterID),Cost)*Form1.LastSubmit.'Exchange rate'
}
)

After you update list2, you will update cost column and Total converted column in list1 with the same ID.

Best regards,

[Gorilla_8]

I tries this formula, and I get a warning notification, with some big values they dont work

[Gorilla_8]

Hi @v-yutliu-msft  I put this 'On Save':

SubmitForm(SharePointForm1);;
//I assume that you update list2 by using form
Patch(
    'Reembolso - Itens de reembolso do solicitante';
    LookUp(
        'Reembolso - Itens de reembolso do solicitante';
        MasterID = SharePointForm1.LastSubmit.MasterID
    );
    {
        'Valor do gasto': Sum(
            Filter(
                'Reembolso - Dados do solicitante';
                ID = SharePointForm1.LastSubmit.MasterID
            );
            'Valor gasto_dados'
        )
    }
)

And I got this: 

Delegation notice. A prominent part of this formula may not work correctly on large data sets. "Sum" operation is not supported on this connector.

And th error on print>

Spoiler

[v-yutliu-msft · Accepted solution]

Hi @Gorilla_8 ,

Ok. Let me tell you more about delegation.

Firstly, delegation warning is not an error. It is just a warning.

PowerApps could only deal with at most 2000 records in local. If your data source has more than 2000 records, powerapps will delegate data source to deal with data. To delegate data, you will need to use delegate data source and delegate functions.

If you use delegate data source but use non-delegate functions, you will get delegation warning, just like yours.

How to deal with this?

1)If your data source is smaller than 2000, you just need to change delegation limit to 2000 (default is 500).

Then even if you have delegation warning, your app will not perform badly.

2)If your data source is larger than 2000, you have two choices:
1/save all your data in collection, then use collection as data source. In collection, there will be no delegation problem

2/modify your formula to only use delegate functions

Actually, delegate functions are very limited. I think you couldn't get the feather that you want to only use these functions.

Here's a doc about delegate functions in sharepoint for your reference:

https://docs.microsoft.com/en-us/connectors/sharepointonline/

Here's a doc about delegation for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

Best regards,