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

Filter by name and by month

Hello All,

 

I had an item list as below:

ID            Name                    Date                          Paid

1              A                           26/02/20                   5.5

2              B                           26/02/20                    4

3              A                           25/02/20                    3

4              C                           23/02/20                    1

5              A                           22/01/20                    10

6              A                           10/01/20                     5

 

I want to a total of paid by filter by name and by month in order to get the total of paid for each users.

For example: Name: A in February, he will get paid in Total: 5.5+3 = 8.5 and the same user A in January, he will get paid in Total: 10+5 = 15

 

I want to create a flow to make this calculation by the end of each month.

 

any help on this?

 

thanks,

BL

2 ACCEPTED SOLUTIONS

Accepted Solutions
Dual Super User
Dual Super User

Re: Filter by name and by month

Hi!

My suggestion is:

1.-Get items from last month on your sharepoint list by means of an ODATA filter

2.- Build a 'dictionary' with all your Names

3.- Initialize a Float auxiliar variable, let's call it auxFloat

4.- iterate through your Dictionary with an 'Apply to each'. Inside the apply to each:

4a.- Set auxFloat to 0

4b.- Use Filter array using 'GEt items' as input, the condition should evaluate NAmes matching your current iteration Name

4c.- Implement a second Apply to Each inside, its input shall be Filter array output. INside the Apply to each just 'Incremnt variable' with the Paid column value of current iteration

4d Outside the second apply to each but still inside the first Apply to Each, do whatever you need with the result obtained on your variable (send it by email, store it in another SP list...)

 

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!



View solution in original post

Community Support Team
Community Support Team

Re: Filter by name and by month

Hi @chenboly ,

There is an instance you could have a reference:

  1. Get all items of the paid list. Then initialize an array variable named NameList to collect all names in the paid list.
  2. Then use the union() function  to remove the repeat names: union(variables('NameList'),variables('NameList'))​
  3. Initialize a float variable named TotalPaid to accept the paid. 
  4. Create two apply to each action to loop through the NameListNoRepeat and PaidList in the SharePoint.
  5. If the current item of the NameListNoRepeat array and the Month is 01(January), increase the TotalPaid:formatDateTime(items('Apply_to_each_3')?['Date'],'MM')
  6. After a loop through the PaidList, the result of the TotalPaid will be A total paid in January.
  7. Then you should set TotalPaid variable to 0 for the next loop.

Annotation 2020-02-26 123468.png

 

Annotation 2020-02-26 123469.png

 

Annotation 2020-02-26 123470.pngAnnotation 2020-02-26 123471.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

14 REPLIES 14
Dual Super User
Dual Super User

Re: Filter by name and by month

Hi!

My suggestion is:

1.-Get items from last month on your sharepoint list by means of an ODATA filter

2.- Build a 'dictionary' with all your Names

3.- Initialize a Float auxiliar variable, let's call it auxFloat

4.- iterate through your Dictionary with an 'Apply to each'. Inside the apply to each:

4a.- Set auxFloat to 0

4b.- Use Filter array using 'GEt items' as input, the condition should evaluate NAmes matching your current iteration Name

4c.- Implement a second Apply to Each inside, its input shall be Filter array output. INside the Apply to each just 'Incremnt variable' with the Paid column value of current iteration

4d Outside the second apply to each but still inside the first Apply to Each, do whatever you need with the result obtained on your variable (send it by email, store it in another SP list...)

 

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!



View solution in original post

chenboly
Level: Powered On

Re: Filter by name and by month

@efialttes thanks,

if you don't mind, could you help to give some screen shot? as I am very new to work in microsoft flow.

 

thanks again,

BL

Dual Super User
Dual Super User

Re: Filter by name and by month

@chenboly 

Can you confirm first your SP list column definition?

Is Name a 'single line of text' type column, Is Date a 'Date' type column, is Paid a... type Column?

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!



chenboly
Level: Powered On

Re: Filter by name and by month

Hi efialttes,
Here is the type of column in SP list
Name: People Or Group
Date: Date Type
Paid: Number

Thanks,
Bl
Dual Super User
Dual Super User

Re: Filter by name and by month

@chenboly 

Thanx for the info!

Since you declared you are not experienced in PA flows and the logic you need is a bit complex, my suggestion is: I will share screenshots step by step, so once I share a step, you replicate it and when confirming it works on your side as expected, we move into the next step.

Is it fine for you?



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!



chenboly
Level: Powered On

Re: Filter by name and by month

@efialttes Yes, I just start to work on PA few months and I am very appreciated with the help from the community.

I am really sorry as I missed one point in my scenario, which is another field called Paid Type. I also need to filter by Paid Type as well.

So the list is look like this.

ID            Name                    Date                          Paid                 PaidType

1              A                           26/02/20                   5.5                   AL

2              B                           26/02/20                    4                     AL

3              A                           25/02/20                    3                     AL

4              C                           23/02/20                    1                     AL

5              A                           22/01/20                    10                   SL

6              A                           10/01/20                     5                    SL

 

Please advise, many thanks for your help.

BL

 

Dual Super User
Dual Super User

Re: Filter by name and by month

Hi again

So, you need 2 monthly counters per Name? Total monthly amount paid as AL and Total monthly amount paid as SL?

Also which Column type is PaidType? Choice? 'Single line os text'? Lookup?

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!



chenboly
Level: Powered On

Re: Filter by name and by month

@efialttes Sorry again for the missed.

PaidType is Choice.

"So, you need 2 monthly counters per Name? Total monthly amount paid as AL and Total monthly amount paid as SL?"

Yes, that is right if the AL and SL paid in the same month. for example: if user A paid in February of PaidType AL and SL, then we can see the total of AL and SL separately. 

Thanks again.

BL

Community Support Team
Community Support Team

Re: Filter by name and by month

Hi @chenboly ,

There is an instance you could have a reference:

  1. Get all items of the paid list. Then initialize an array variable named NameList to collect all names in the paid list.
  2. Then use the union() function  to remove the repeat names: union(variables('NameList'),variables('NameList'))​
  3. Initialize a float variable named TotalPaid to accept the paid. 
  4. Create two apply to each action to loop through the NameListNoRepeat and PaidList in the SharePoint.
  5. If the current item of the NameListNoRepeat array and the Month is 01(January), increase the TotalPaid:formatDateTime(items('Apply_to_each_3')?['Date'],'MM')
  6. After a loop through the PaidList, the result of the TotalPaid will be A total paid in January.
  7. Then you should set TotalPaid variable to 0 for the next loop.

Annotation 2020-02-26 123468.png

 

Annotation 2020-02-26 123469.png

 

Annotation 2020-02-26 123470.pngAnnotation 2020-02-26 123471.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Dual Super User
Dual Super User

Re: Filter by name and by month

@v-litu-msft was faster than me. Thanks for sharing!



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!



chenboly
Level: Powered On

Re: Filter by name and by month

Thanks @v-litu-msft and @efialttes ,

 

My requirement also need to filter by PaidType as well. Could you help to recheck again.

 

ID            Name                    Date                          Paid                 PaidType

1              A                           26/02/20                   5.5                   AL

2              B                           26/02/20                    4                     AL

3              A                           25/02/20                    3                     AL

4              C                           23/02/20                    1                     AL

5              A                           22/01/20                    10                   SL

6              A                           10/01/20                     5                    SL

thanks,

BL

chenboly
Level: Powered On

Re: Filter by name and by month

Thank @v-litu-msft  @efialttes ,

 

My requirement also need to group/filter by PaidType as well.

How could we add this paidtype?

 

Thanks,

BL

Dual Super User
Dual Super User

Re: Filter by name and by month

@chenboly 

If I understood you properly, and taking @v-litu-msft example as reference, you need to split current variable TotalPaid in two variables, right?

If so, lets call them TotalPAidSL and TotalPaidAL

 

My suggestion is:

1.- Initialize both variables, the same way Total paid is currently done

2.- On the true branch of the Condition inside 'Apply to each 2' the example currently have an 'Increment variable' acting against TotalPaid. You should remove it, add an additional condition (inside current condition true branch) and evaluate if PAid Type Value equals to AL. True? Increment variable TotalPAidAL. False? Increment variable TotalPaidSL. I am assuming there are only two possible values (AL, SL)

3.-At the end of 'Apply to each 2' you currently have a Compose an a SEt Variable. You should update Compose reflecting values of your two new variables; and also remove current 'Set variable' and adding two 'Set variable' each of them updating TotalPaidAL, TotalPAidSL to 0.

 

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!



Community Support Team
Community Support Team

Re: Filter by name and by month

Hi @chenboly,

 

If you want to sum all paid with the same paid type, it is more simple, you could use the Filter query in the Get items to select items which paidtype equal to AL, then just like @efialttes said, initialize a Float variable to sum the paid:

Annotation 2020-02-28 114822.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons in April

Read the announcement for more information!

firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,814)