cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

SQL View into Filtered List within Flow Approval

Hi Everyone!

 

First off, love MS Flow!

 

Second, I have successfully accomplished what I intend to build however the results have some bugs and I'm wondering if I am doing something wrong or its a limitation of the system.

 

Here is the use case:

Table within Azure SQL DB contains UserNames and the Servers they belong to. There are about 10K Users who belong to about 2,500 Servers. I needed to build an approval functionality for the Users to confirm that they should still be on the Servers we have listed for them. So...for each individual user there can be hundreds of servers they have access. In a MS Flow approval, I wanted to automatically send out an email and ask them to approve the list of servers that they have access. 

 

What I built:

First, I created view1 in SQL that was only two columns, UserName and ServerName. With this successfully pulled into Flow using SQL Server connection, I then use Apply to Each on a distinct view of the same SQL results (view2) to be able to go one by one within the next step. I then added a Filter Array connection with the data pulled from view 1 filtered where view1.UserName equals view2.UserName . After that, I used the Parse JSON connection to get it back into a usable form within Flow and pushed that Output into Create CSV Table connection. This allowed me to pull in both columns into the Flow Approval connection Details. 

Results:

So 9 out of 10 times, this works perfectly. I get a nicely formatted list of Servers for that User to approve/reject. 

HOWEVER sometimes if a User has access to 762 Servers, I only end up with 697 (a weird number). There is no rhyme or reason for this. Another User who has access to 545 Servers, accurately shows 545 Servers in approval list. Another one who shows 1,428 Servers, only shows 49 on approval list. 

 

The flow is functionality perfectly as I want however the results are so weird and I cannot understand what is wrong. 

 

Any help/guidance would be awesome. 

 

Thanks!

 

 

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: SQL View into Filtered List within Flow Approval

Hi @nmanselmo,

 

Could you share a screenshot of your flow? About the items that are not returned accurately, will they be returned correctly in a different run?

I am not able to reproduce this issue on my side.

Besides, I am trying to search for related docs on the limitation on Azure SQL DB. I will keep an eye on it.

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: SQL View into Filtered List within Flow Approval

Mabel,

 

See image per your request. 

2018-09-27_8-54-39.png

 

Something interesting I found yesterday was that if I put the same "data output" from Parse JSON into Create HTML table instead of Create CSV Table and then throw that output into a simple Send Email template which is in HTML, I get all records, they are not limited. 

Is it possible there is a limitation on JSON into Create CSV?

Summary to clarify:

Push through raw data from SQL (per individual varies, from about 30 Records to 2,500 Records) push that into Flow, Filter with Apply to Each, Filter Array and Parse JSON. 

 

After the above, which works great I have two different end results:

 

1. Create CSV from Parse JSON and then into Approval - Seems to work with less than 700 records but if its more it gets cut off on Approval Details AND Approval Email Notification. 

2. Create HTML Table from Parse JSON and then into HTML Email (Because Approval doesnt support HTML) - This seems to work 100% of the time with all record counts however I lose the approval functionality.

 

Let me know what you think. 

Highlighted
Regular Visitor

Re: SQL View into Filtered List within Flow Approval

Mabel,

 

See image per your request. 

2018-09-27_8-54-39.png

 

Something interesting I found yesterday was that if I put the same "data output" from Parse JSON into Create HTML table instead of Create CSV Table and then throw that output into a simple Send Email template which is in HTML, I get all records, they are not limited. 

Is it possible there is a limitation on JSON into Create CSV?

Summary to clarify:

Push through raw data from SQL (per individual varies, from about 30 Records to 2,500 Records) push that into Flow, Filter with Apply to Each, Filter Array and Parse JSON. 

 

After the above, which works great I have two different end results:

 

1. Create CSV from Parse JSON and then into Approval - Seems to work with less than 700 records but if its more it gets cut off on Approval Details AND Approval Email Notification. 

2. Create HTML Table from Parse JSON and then into HTML Email (Because Approval doesnt support HTML) - This seems to work 100% of the time with all record counts however I lose the approval functionality.

 

Let me know what you think. 

 

Highlighted
Regular Visitor

Re: SQL View into Filtered List within Flow Approval

Mabel,

 

Did some digging, I think there is BUG in Approvals. Can you confirm?

Output of Create CSV Table: (Shows that All Records (763) are passed through)

 

Output 1.png

 

Output of Approval Detail & Email: (You see that the records get cut off at 546)

 

Output 2.png

 

 

Output of HTML Email After Flow Expires: (This step occurs AFTER the approval if the Approval Times Out AND it pushes all 763 records through!)

 

Output 3.png

 

 

 

 

 

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

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!

Top Solution Authors
Top Kudoed Authors
Users online (10,348)