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

Create statement using data from SP list by populating Word Template

Hello,

 

I would like to create a flow that at the end of the month that creates a statement for all work done for the company.

 

I currently use a SP list to store data from PowerApp. The SP List would look like:

Business NameAddressJob NumSub TotalVatTotal

Trucking 123 Ltd

53 Neptune1800.000800.00
Trucking 123 Ltd53 Neptune2350.000350.00
Mckay LtdOld Dub Road31250.0001250.00
Dublin LtdKednaminsha4999.000999.00
Trucking 123 Ltd53 Neptune588.0012.00100.00
Dublin LtdKednaminsha6458.000458.00

 

So I can create a document template using plain text controls I assume in Word? But how would I get PowerAutomate to look at the current month and then create a single document for Trucking 123 Ltd that would list out in line items the three jobs done in that month, a single document for Mckay Ltd and a single document for Dublin Ltd and so on.

 

So I assume it would need some logic to say ok, lets identify all distinct businesses where jobs were done that month, then add each line item for all the job numbers that were completed.

 

I hope that's clear? I do not know where to start with this one other than it is likely to be a scheduled flow.... Cheers.

1 ACCEPTED SOLUTION

Accepted Solutions
PrasadAthalye
Super User
Super User

Please refer below...

 

  • Create a Variable of type Array.
  • Execute GetItems to get all items for the last month.
  • Run for each on GetItems and add unique Business Names to Array variable. below is an example

PrasadAthalye_0-1621886102047.png

 

 

  • Now loop through array variable.
  • Use Filter Array to filter Get Items Array where Business Name matches the Name from Array. 
  • Then add Select from Array Action and Map fields that you would like to use in template
  • Then you can populate the template that you already defined.
Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

View solution in original post

8 REPLIES 8
PrasadAthalye
Super User
Super User

Please refer below...

 

  • Create a Variable of type Array.
  • Execute GetItems to get all items for the last month.
  • Run for each on GetItems and add unique Business Names to Array variable. below is an example

PrasadAthalye_0-1621886102047.png

 

 

  • Now loop through array variable.
  • Use Filter Array to filter Get Items Array where Business Name matches the Name from Array. 
  • Then add Select from Array Action and Map fields that you would like to use in template
  • Then you can populate the template that you already defined.
Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

View solution in original post

drossi
Helper III
Helper III

Hi @PrasadAthalye  thanks for the response.

 

OK, I'm trying to work it through but the image isn't very clear. For the initial 'get items' how do I filter for only items in current month?

 

drossi_0-1622012152762.png

 

If you are interested in Items created in this month then you an try following expression

 

PrasadAthalye_0-1622032149649.png

Created ge '@{formatDateTime(startOfMonth(utcNow()), 'MM/dd/yyyy')}' and Created le '@{formatDateTime(utcNow(), 'MM/dd/yyyy')}'

 

Please use entire expression of Formatdatetime using expression.. Refer the screen shot how it appears...

 

 

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community
drossi
Helper III
Helper III

Thanks @PrasadAthalye  I also added contains(JobStatus,'Invoiced').

 

OK so I'm slowly making progress. Can you have a look and check if correct?

 

- I've created the recurrence (ignore the start time, it needs amended.

- I've used 'get items'

- I've created variable called BusName

- Now this is where I'm getting confused. What condition should I use? BusName does not contain??

- When appending to array variable, I assume the value is the BusinessName from SP List?

 

Sorry for all the questions

 

Screenshot 2021-05-26 at 21.09.03.pngScreenshot 2021-05-26 at 21.09.50.png

Yes that is correct. If array BUNames does not already contain BU Name, you will add it...

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community
drossi
Helper III
Helper III

Hey @PrasadAthalye  sorry to bother you again. I'm nearly there ( I think) but I have one little issue. Can you please look over and provide some feedback?

 

So, the flow is somewhat working, it successfully loops round each business name but the issue I'm having is:

 

First output:


It returns the first business but only populates the first row of the statement even though there may be six invoices.

Invoice Numdatesubtotalamount
1212/5/21300320

 

 

Second output:

 

It returns the second business but populates two rows of data (123 is the first invoice for the customer) that are duplicates even though there may be four invoices i.e.

 

Invoice Numdatesubtotalamount
1231/5/21100120
1231/5/21100120

 

Third Output:

It returns the third business but populates three rows of data (124 is the first invoice for the customer) that are duplicates i.e.

Invoice Numdatesubtotalamount
1242/5/21200220
1242/5/21200220
1242/5/21200220

 

And so on.... It seems it is only adding the first row of data for that particular customer and duplicating it as many times as the flow has ran?

 

This is my flow:

 

Screenshot 2021-06-02 at 09.14.11.pngScreenshot 2021-06-02 at 09.14.23.png

 

 

 

 

Can you incorporate following steps? I see that your filter array is in the same loop where you are finding unique BU Names. You need to create another loop on this array and then filter inside..  Refer below steps from above...

 

  • Now loop through array variable.
  • Use Filter Array to filter Get Items Array where Business Name matches the Name from Array. 
  • Then add Select from Array Action and Map fields that you would like to use in template
  • Then you can populate the template that you already defined.
Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

Sorry @PrasadAthalye , when you say loop through array variable, what exactly do you mean?

 

I added another apply to each but doesn't seem to work either.

 

Screenshot 2021-06-02 at 13.58.29.png

 

 

 

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (2,676)