Iterate around Acollection

(0) Share

Report

Posted on by NPrice99

1,302

I have a financial collection which I want to iterate around :- Financial Year, CAPEX, OPEX

This collection has four entries :-
FY18/19, CAPEX = 1, OPEX=2

FY19/20, CAPEX = 10, OPEX=20

FY20/21, CAPEX = 100, OPEX=200

FY21/22, CAPEX = 1000, OPEX=2000

So I want to iterate around the collection and do some calculations on the OPEX and CAPEX amounts.

like add the CAPEX and OPEX to a value in a variable.

I cannot use ForAll(FYCollection, UpdateContext({vTotalCAPEX:vTotalCAPEX + CAPEX}); UpdateContext({vTotalOPEX:vTotalOPEX + OPEX});

); nor the Set() equivalent.

How can I achieve my Objective ?

Thanks

Nigel

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

xzDan 350 on at

Like (0)

Report
Copy link

Link copied!

Can you not use an AddColumn(<source>,"Sum Column",CAPEX+OPEX)?

Or Set(myVariable, Sum(<Source>,CAPEX)+Sum(<Source>,OPEX))

Was this reply helpful? Yes No
NPrice99 1,302 on at

Like (0)

Report
Copy link

Link copied!

HI @xzDan

You certainly cannot use Set inside a ForAll.

I will have a look at your other option.

Thanks

Nigel

Was this reply helpful? Yes No
xzDan 350 on at

Like (1)

Report
Copy link

Link copied!

You are right you cannot. Both my proposal would not use ForAll at all

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @NigelP ,

Do you want to calculate the sum of CAPEX column/OPEX column of all records in your FYCollection, and save them into a separated variable?

Based on the formula that you mentioned, I think there is something wrong with it. Currently, setting a variable (context variable or global variable) within a ForAll function is not supported within PowerApps currently.

Based on the needs that you mentioned, I think the Sum function could achieve your needs. I have made a test on my side, please take a try with the following workaround:

Set the OnSelect property of the "Calculate" button to following:

Set(vTotalCAPEX, Sum(FYCollection, CAPEX));
Set(vTotalOPEX, Sum(FYCollection, OPEX))

More details about the Sum function, please check the following article:

Sum function

Best regards,

Was this reply helpful? Yes No
NPrice99 1,302 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xida-msft

Thanks for this.

So now I have two collections each one having a common column - FY.

I now want to compare the collections based upon the common column FY.

So for each value in the common column (FY), I want to compare the values in the other columns.

So if Collection1 has FY, ProjectCAPEX,ProjectOPEX,ProjectID
and Collection2 has FY, BudgetCAPEX, BudgetOPEX,ProjectID

I want to know what Projects (ie ProjectID) where ProjectCAPEX > BudgetCAPEX or ProjectOPEX > BudgetOPEX
for each FY.

Thanks

Nigel

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @NigelP ,

Do the Collection1 and Collection2 have common columns -- FY and ProjectID?

Further, do you want to compare the the values in the other columns based on the common column FY?

I have made a test on my side, please take a try with the following workarund (set the OnSelect property of the Button to following😞

Clear(FinalProjectIDCollection);
ForAll( Collection1, If(
Collection1[@ProjectCAPEX] > LookUp(Collection2, FY = Collection1[@FY], BudgetCAPEX) || Collection1[@ProjectOPEX] > LookUp(Collection2, FY = Collection1[@FY], BudgetOPEX),
Collect(FinalProjectIDCollection, Collection1[@ProjectID])
) )

When you execute above formula, the Projects (ProhectID) which meets your needs would be populated within the FinalProjectIDCollection collection.

More details about ForAll function, please check the following article:

ForAll function

Please take a try with above solution, then check if the issue is solved.

Best regards,

Was this reply helpful? Yes No