Sql Statement to filter Excel Table with dynamic data

(0) Share

Report

Posted on by mcnemare1

214

I have 2 lists that will be changing every day.

List A are Sales requests from clients (From Legacy System)

List B is our CRM System Data Dump (excel b.c. cheap)

I am Creating List C, D, E, F, and G. Using the Sales Requests and Legacy system information.

We are using PAD because everything needs to stay on network.

They only key that links both lists is the account # field.

Originally my flow was using PAD if actions as shown below to see if

IF Excel List A Current Item [Account Number] = List B Current Item [Account Number]

Then do X,Y,andZ

This was taking forever to run, so, I after some research I am thinking a about pulling in the big excel table with 33k rows

Via

1.) Open SQL connection to an excel file.

2.) Execute SQL Statement to filter for rows the Account numbers in List A (which is always smaller)?

3. Use for each to loop through each table as I had it before...???

I am racking my brain attempting to create a filter for the account numbers via the SQL statement, it would have to be a variable since its changing. What are your guys thoughts?

Any issues with #3?

PAD excel if.JPG excel if flow taking forever

Categories:

Power Automate Desktop

I have the same question (0)

All responses (2)

Answers (0)

takolota1 4,974 Moderator on at

Like (0)

Report

@mcnemare1

Are you trying to update the Excel with values or calculated/transformed values from SQL?

What is the reason for needing to stay on network? Some kind of security or something that would prevent the use of cloud flows?

Was this reply helpful? Yes No
mcnemare1 214 on at

Like (0)

Report

@takolota

Sorry for the belated response.

It has determined that since CRM report holds customer sensitive information, that any flows created should be in-network and cloud flows avoided.

Correct I am trying to filter excel with calculated values from SQL statement.

Since List A 'Sales Requests' (excel file) is always going to be smaller than List B 'CRM' Excel File.

I'm thinking it would be easier to filter List B 'CRM' by 'List A' Sales Requests. Via SQL first to reduce the initial loops are taking in PAD. thoughts?

Was this reply helpful? Yes No