cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elleeyl
Level: Powered On

Sum of row numbers and patch to another datasource

Hi,

 

I am very new to Powerapps and would really appreciate any help with my issue. Thank you.

 

I have 2 SharePoint lists and 2 screens.

Screen 1 uses datasource "header".

header.PNG

Screen 2 uses datasource "details".

details.PNG

How it should work is...

1. At Screen 1, when I click on "Create" button, it will patch all details entered onto "header" and  then navigate to Screen 2. There is a report ID "ReportID" generated.

2. Then at Screen 2, I would choose an Expense Type from the dropdown list, enter an Expense Amount and Expense Date, then click on "Button". I will repeat Step 2  for nth times until I have submitted all my receipts. Regardless how many individual expenses I submitted, as long as they belong to the same report (i.e. Screen 1 ReportID), each individual expense would carry the same ID as "ReportID".

3.

a) Each clicking of "Button" in Screen 2 should patch all entries back to "details".

b) Also I need to sum up all the individual expense amounts submitted at Step 2. This total sum needs to be patched back to Screen 1 "header" datasource under TotalExpenseAmount.

 

This is the formula I used in Screen 1 "Create" button.

Set(current_claim_amount,0); Set(reportid, Value(DataCardValue9.Text));
Patch(
Header,
Defaults(Header),
{Title: DataCardValue18.Text, ReportID:reportid, Name:DataCardValue19.Text, EmailAddress:DataCardValue20.Text, Purpose: DataCardValue23.Text, Department: DataCardValue21.Text, TotalClaimAmount: current_claim_amount});
Refresh(Header);
Set(LastAddedItem,First(Sort(Header,reportid,SortOrder.Descending)));
Navigate(Screen2,None);

 

This is the formula i used in Screen 2 "Button" button.

Set(current_expense_amount,Value(DataCardValue44.Text));
Set(reportid2, Last(Header).'Report ID');
Patch(Details,

Defaults(Details),

{ReportID:reportid2, Title:DataCardValue37.Text, ExpenseAmount: Value(DataCardValue44.Text), ExpenseDate: DataCardValue45.SelectedDate, ExpenseType: DataCardValue43.Selected});
Patch

(Header,

LookUp(Header,reportid2=LastAddedItem.'Report ID'),

{TotalClaimAmount:current_claim_amount+current_expense_amount});
UpdateContext({clearform:false});
UpdateContext({clearform:true})

 

With the above formulas, I am getting everything patched back onto the 2 datasources, except the TotalClaimAmount. It is still emtpy in "header" datasource. Please let me know how I should write my formulas to overcome this.

 

Thank you very much.

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Sum of row numbers and patch to another datasource

Hi @elleeyl ,

Do you have reportid2 field in Header table?

I found that the formula that you used may have problem.

Patch
(Header,
LookUp(Header,reportid2=LastAddedItem.'Report ID'),   //you should use a field in Header table to compare with value
{TotalClaimAmount:current_claim_amount+current_expense_amount});

I assume that the field that you want to filter based on is ReportID, try this formula:

Patch
(Header,
LookUp(Header,ReportID=LastAddedItem.'Report ID'),   //or LookUp(Header,ReportID=LastAddedItem.ReportID)
{TotalClaimAmount:current_claim_amount+current_expense_amount});

What's more, do you have reportid field in Header table?

You used reportid instead of ReportID in sort function, I suggest you change this too.

Set(LastAddedItem,First(Sort(Header,ReportID,SortOrder.Descending)));  
//or Set(LastAddedItem,First(Sort(Header,'Report ID',SortOrder.Descending)));

I also found that someplace you use ReportID, while some you use 'Report ID'. Could you meke sure the field name?You need to use the same field name.

 

 

 

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.
Highlighted
elleeyl
Level: Powered On

Re: Sum of row numbers and patch to another datasource

Hi @v-yutliu-msft ,

 

I have updated the various ID names of both datasources.

"header" datasource has HeaderID and "details" datasource has ExpenseID.

 

Header screen/Screen 1 now has this formula

Set(current_claim_amount,0);

Set(HeaderID, Value(DataCardValue11.Text));
Patch(Header,Defaults(Header),{Title: DataCardValue18.Text, HeaderID:HeaderID, Name:DataCardValue19.Text, EmailAddress:DataCardValue20.Text, Purpose: DataCardValue23.Text, Department: DataCardValue21.Text, TotalClaimAmount:current_claim_amount});
Refresh(Header);
Set(LastAddedItem,First(Sort(Header,HeaderID,SortOrder.Descending)));
Navigate(Test2_Details,None);

 

Details screen/Screen 2 now has this formula

Set(current_expense_amount,Value(DataCardValue44.Text));
Patch(Details, Defaults(Details),{ExpenseID:HeaderID, Title:DataCardValue37.Text, 'Expense Amount': current_expense_amount, 'Expense Date': DataCardValue45.SelectedDate, 'Expense Type': DataCardValue43.Selected});
Collect(Colexp,LookUp(Header,HeaderID=LastAddedItem.'Header ID'),{TotalClaimAmount:current_claim_amount+current_expense_amount});
UpdateContext({clearform:false});
UpdateContext({clearform:true})

 

The problem now is the total claim amount is always the value of the last expense amount entered, but it never sums up.

Could you please guide me through it? Is it because the current_claim_amount is set to 0 as written in the first screen?

 

Thank you.

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (3,720)