cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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
Highlighted
Community Support
Community Support

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
Helper III
Helper III

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
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (3,493)