Writing SQL data rows into dataverse table by hitting a command bar button

(0) Share

Report

Posted on by Community Power Pla...

Hey y'all,

we would like to sync some sql data on-demand into dataverse tables as child records.

The scenario is:

The user is in the form of a record in a model driven app. The record has child records shown in a subgrid. The user wants to refresh the child records from a sql database, so that all of the missing data rows will be written into the dataverse table for the child records and connected via lookup to the parent record.

We use the following setup: An Azure function that requests the data via Hybrid Connection Manager from On-Premises SQL database.

Now we need the ad-hoc request by hitting a command bar button. How would you solve this? We need to request the Azure Function by hitting the button and the Azure Function (or a plugin?!) needs to write data to the dataverse table. After writing all of the data, the form should refresh with the newly created child records in a subgrid.

Thanks in advance for your ideas 🙂

All responses (1)

Answers (0)

manojpatel20dec 17 on at

Like (0)

Report

Re: Writing SQL data rows into dataverse table by hitting a command bar button

@Anonymous here's a simplest approach that I would have done:
- Install On-Premise data gateway on your sql machine and register it Use the on-premises data gateway app | Microsoft Learn

Sign-In with your dynamics AD account

- Create a instant flow, use action either execute SQL query or stored procedure
- Configure the connection details to be windows/ SQL authentication and select the gateway you configured previously
-Enter the sql query/ procedure
-use request response and return the query results from previous action as json string

- Save the flow and get the http URL
- Create a webresource with a function to call http flow url and get response json back, parse json object and loop through the data and create it as records setting the current lookup too, on completion call
formContext.data.refresh(save).then(successCallback, errorCallback);
-Use ribbon workbench and call the webresource method on custom button to sync DB records