Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id : pD9JnDxKQfolnc1kD4XMVQ
Power Platform Community / Forums / Power Automate - Using Connectors / Assign value from SQL ...
Power Automate - Using Connectors
Answered

Assign value from SQL table to variable

Like (0) ShareShare
ReportReport
Posted on 16 Aug 2019 19:25:49 by TerryR 51

I am creating a report that updates an excel table to be emailed daily from data in a SQL table.  The report needs to be able to determine which records were added after the previous report and only show those on the new report.  That works well by just deleting the old records and adding the new ones.  I have a separate table that could be used to hold the last record ID from the previuous report.  With that I was hoping to pull only the record ID's greater than the previous day. 

The problem is to get that ID number into a variable that I can use to filter data with.  

The second part of the problem is to update the table with the "new" last record.

 

The other option I tried was to just filter by time, since the report will go out at the same  but that doesn't appear to work in Flow.

  • Verified answer
    TerryR Profile Picture
    TerryR 51 on 19 Aug 2019 at 17:26:03
    Re: Assign value from SQL table to variable

    I used Compose to pull the value from the array with this formula...

     

    "@first(body('Get_Last_Report_rows')?['Value'])?['InspReportLastID']"

     

    Then used the output in the formula

         ID gt Output

     

    It retrieved the correct number, but when I used the value in the SQL Get Rows filter it did not return all the data that it should.  It only filled in a few rows when it should have about 10.  I had to put a short delay in before the email sent out to allow all the records to actually get into the Excel file.

     

  • TerryR Profile Picture
    TerryR 51 on 19 Aug 2019 at 12:47:05
    Re: Assign value from SQL table to variable

    My data is all in SQL and the flow will copy new data to an excel table to be sent out on a schedule. I'll start with the first step I'm having trouble with.

    I need to be able to get the ID of the last inspection report from the LastReportKey table.

    IndexKey

    InspReportLastID

    1

    568344

     

    This value will need to be used in the filter to get new inspection data rows.  How do I get that number into a variable to use in the query of the inspection data table? The filter would be something like this...

     

    Filter:  ID gt Max(InspReportLastID)

     

  • v-bacao-msft Profile Picture
    v-bacao-msft on 19 Aug 2019 at 03:25:20
    Re: Assign value from SQL table to variable

     

    Hi @TerryR ,

     

    I am afraid I can hardly figure out your specific needs only from your description.

    Would you please illustrate your needs with screenshots?

    Please provide more explanation about your needs.

     

    Best Regards,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Thomas Rice – Community Spotlight

We are honored to recognize Thomas Rice as our March 2025 Community…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,508 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,369 Most Valuable Professional

Leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans