Sum of row numbers and patch to another datasource

(0) Share

Report

Posted on by elleeyl

121

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".

Screen 2 uses datasource "details".

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".

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.

Categories:

Building Power Apps

All responses (2)

Answers (0)

Sort by

v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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,
elleeyl 121 on at

Like
a
(0)

Report
Copy link

Link copied!

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.