Create HTML table/report from multiple SharePoint lists

[nmehta]

I have 3 SharePoint lists as my underlying data source to the PowerApp application.

SharePoint List 1 has details about the record retention codes

SharePoint List 2 has a list of Legal and Regulatory Citations associated with the records.

Each record can have multiple citations and a single citation could be associated with multiple records (many to many relationships).

As a result, I have List 3 that connects the records associated with citations.

Here is an example of how the lists are. There are many more fields in both lists but for the example, I am just limiting to the ones that matter

Record Class List (RRS_DS)

Citations List (Citations_DS)

Record-Citations List (RRS_Citations_Link_DS)

What I am trying to do is create an HTML report (that is will convert to PDF) by using fields from the Record Class List and Citations List (which can only be connected through Record-Citations List) using Power Automate. The flow will be triggered from the PowerApp and all that I am passing is the type of report I want to generate.

Originally, I was doing this by creating HTML within PowerApps and passing that to Power Automate. However, this is giving performance issues because the entire HTML has to be first generated (which lists all record classes from the retention schedule and then all citations associated with each record class)

Is there are a way to create a report by joining 3 lists in Power Automate?

Here is a screenshot of a part of how the report looks like

Btw, I moved the generation of all my reports out from PowerApps to Power Automate and it works fine since in those reports I am just using 1 SharePoint List. I am only having trouble with this report where it requires me to join 3 Lists

[leyburn19]

Are you using Power Automate Premium,  if so you could probably create a template in Word with tables and then use the create PDF to build the report.  

[nmehta]

No. I don't have Power Automate Premium. The issue is not with the conversion of HTML to Word or PDF but more around how would do you create a report joining multiple lists. I saw a video in which you loop through one list (using Get Items) and then have a nested list using the filter for the second 'Get Items' action and add that to an array but that only brings back the filtered results. 

[v-JessicaG-msft]

Hi @nmehta ,

Do you want to use multiple SharePoint lists to create HTML tables in Power Automate?

You could filter the records of the Record-Citations list by judging that the Record Class list and the Record-Citations list have the same “Recordcode” column value, and use the same “CitationsID” column value in the filtered Record-Citations list to filter the Citations list, which will meet the conditions The Citations list record is stored in a variable, and finally the variable is used to create an HTML table.

I have test for your reference, please try the following workaround:

Result:

Best Regards,

Jessica Gu

[nmehta]

Jessica,

Thank you for spending time to test and lay down the steps to join multiple lists. I tried to create similar actions in my PowerAutomate but it seems to be throwing an error in the 'Apply to each 2' step where you filter the results in the 'Citations' step/action.

In that step which 'Citations_x000_ID' are you comparing it with because when I tried the same approach and test the flow the results come out as a blank

CitatationID eq '' instead of the actual Citation id (number) that should be coming from the previous steps where it is filtering the array

[nmehta]

I was able to fix the previous issue I was having. I now have the details of all the Citations associated with an individual record class in the Citation List array. How do I now format it so that instead of all the citation values showing under one 'Citation List' column, they can be associated with different columns as you can see in the attached image?