cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
charlesanand
Frequent Visitor

Sharepoint list item-based calculation using Flow

I have a simple reconciliation problem and would appreciate if you could provide a simple way of handling it in flow. I am very new to Microsoft flow and still learning the various query syntax and odata rules. I found a way to run it but i feel its way too complex and the flow runs for over 20 mins.. 

Sharepoint List: Table1

DateCustomer NameTxn AmountFromMatching status
2/1/2020ABC109.14Source 1 
2/1/2020XYZ109.14Source 1 
2/1/2020AAA109.14Source 2 
2/1/2020PQRS109.14Source 2 
3/1/2020AAA75Source 1 
3/1/2020BBB72.76Source 1 
3/1/2020CCC72.76Source 2 
3/1/2020KKK172.19Source 2 

 

For each date,

 If sum of amount for Source1 = Sum of amount for Source2, then all the rows for the date should be marked as Matched in the 'Matching status' column. If not, update as 'Not Matched'.

 

In the above example, the rows of 2nd Jan 2020 should be marked as 'Matched' and 3rd Jan 2020 should be marked as 'Not Matched'. 

3 REPLIES 3
efialttes
Super User
Super User

Hi!

"I found a way to run it but i feel its way too complex and the flow runs for over 20 mins."

 

As far as I know, there is not any WDL function that allows you to add values from an array, the maximum nr of elements that can be added is 2. So this means you probably need to use a loop and a variable... Power Automate has some performance limitations: Apply to Each combined with variables is usually slow.

 

Anyway... can you share a screenshot from your curent flow design? So hopefully we can improve a little bit, but it will not be as fast as a powerBI DAX expression

Thanx!

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



charlesanand
Frequent Visitor

Thanks @efialttes. For some strange reason, I am not able to paste the screenshots of the flow here. (may be there is some specific method of doing it and I dont know it yet). The logic I am following for the flow is as follows: As you can see, there are too many select statements and hits to the back end data - thats what causing the time taken. I am using sharepoint list and so this may not the most efficient way to deal with this.  It works, but its not efficient. Any other ideas are welcome! Thanks a ton!

 

<start>

Select data from the table where 'MatchingStatus' is 'not matched'

Declare variable GroupDate, Sum1, Sum2

Apply to Each

Set Variable GroupDate = 'Date'

Reset Variable Sum1

Reset Variable Sum2

Select data from table where 'From' = 'Source1'

Apply to each

Increment Sum1

Select data from table where 'From' = 'Source2'

Apply to each

Increment Sum2

If Sum1 = Sum2

Select data from table where 'Date' = 'GroupDate'

Apply to each

Update MatchingStatus = 'Matched'

else

Select data from table where 'Date' = 'GroupDate'

Apply to each

Update MatchingStatus = 'Not Matched'

B,then update the Matching status for the N rows as Matched
Else, Mark them as Unmatched

<end>

Hi!

THanks for the effort. UNfortunatelly, text description do not help to precise if your 'Apply to each's are nested (i.e. each of them inside the previous one) or not, which has a strong, negative inpact on performance. WE also need to understand which input you assigned to each 'Apply to each'

Iif you need to share a screenshot from Power User editor, make sure you are writing from a desktop browser client (not every desktop browser client is compatible, and I believe mobile browsers does not display this menu) and do the following:

 

Flow_UploadImage.png

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,900)