Building Flow that retrieves Data from SQL Database

[dompilione]

Hi all, 

I am in the process of building a Flow that retrieves data for a custom entity in Dynamics for Sales from the equivalent table in our SQL Database that our ERP app runs on for the sake of continuity, but I am really struggling to figure it out. Ideally, the end users would have the Primary Field of our custom entity in Dynamics typed in, they press the button, and all relevant data is populated with the Primary field acting as a key

I figured using the Get Rows V2 action would be the best way to do this. I have already built the connector via a Gateway but for whatever reason, nothing is getting returned when I run the Test workflow. It gives me some weird output where the Status Code is 200, but then nothing is in the dbo.example.output[].

Any advice on if this is the best way to approach this?

[Loosersic]

Hi,

My flows use local SQL views on ou local SQL server.
Our DBA built the gateway so PowerAutomate could see and query it, and I must say it works well (autopopulate fields in a Sharepoint List when a staff member's staffkey is entered).
If you have built the gateway, perhaps the Gateway is the issue?
This is what my Get Rows V2 looks like

Not sure if this helps. Perhaps the Gateway is the issue.
Good luck.

[dompilione]

Hi, thank you for your response. It looks like this is similar to what I am trying to accomplish as well. The gateway is not the issue, because if I do a Get Tables step before, it gives me a return. I am curious what your Filter Query is doing, and how you knew how to do that, because every time I try to enter something into that space it says the formatting is incorrect. Hopefully you have some guidance.

[Loosersic · Accepted solution]

Hi,

I hate paper forms and am slowly moving all of our school's forms to an automated Sharepoint/PowerAutomate/Adobe-Sign process.

In this process, if a relief/casual teacher comes in for a day, the admin staff used to fill in a paper form, have the relief teacher sign it, take it to the relevant leadership person to approve then take it to the finance building for payroll people to do pays each month. Lots of paperwork and room for error.  The payroll person then looks up the Preferredname/Surname, Pay Code and Payroll number, manually enters this info (and the hours worked) into a CSV file and uploads the CSV each month into the payroll software.

With this form, things are much more simple.

IN a Sharepoint list form, Admin staff member enters the Staff Key (SFKEY), hours worked, Campus and Pay Class (I used PowerApps to customise the form and make it look a bit nicer, which has been a bit of a steep learning curve as I am not a developer) but I was happier with the result than a standard SharePoint NEW ITEM form.

Our DBA built a Read-Only View of our school staff database with their Preferredname, Surname, PayrollNumber and Paycode.

When submitted, the flow queries the staff SQL table using the StaffKey, finds the missing information and updates Item to add in the fields the Payroll person needs.
It generates an Approval email  to the person responsible (simple APPROVE button to click).
If approved, there is a field called 'Approval Status' which is then updated.

Éach month the Payroll person exports an excel sheet from the Sharepoint list which has everything in it - and uploads to Payroll software.

The query (image below) matches the SFKEY (staff Key fieldname) in the SQL table to the field in the Sharepoint List Item (I named the field exactly the same).
Because the query needs to be text, I had to include the apostrophes at the start and end of the Sharepoint Field. Took me a while to realise that and a lot of googling.   SFKEY eq 'SFKEY'

Hope this helps. Good luck. This is only the first time I've used the SQL connector but now I have the opportunities are endless.

[dompilione]

This post was so helpful, thank you so much. I was able to figure it out, just tweaking a few things before pushing it to production. Looks like I needed to learn a little about ODATA queries. Thanks again for your help!!!!