Architectural approach: T-SQL like logic in Dataverse

(0) Share

Report

Posted on by LT-20081525-0

Hi,

I have a client and we want to build a small/simple Order -> Delivery -> Invoice system for him.

Now we are used to work with MS SQL Server and MS Access to build those type of Apps but if we want to build this with:

- Dataverse

- Power Apps Model Driven

Where would you write the logic to for example create a Delivery from an Order?

In SQL Server we would write a Stored Procedure with the OrderID as parameter to create a Delivery.

But in the Power Platform I can't see a place where to write that function.

I was also thinking to use Azure SQL and use virtual tables and still use a SP to execute that function.

But I was wondering if there was an performant option in Dataverse itself..

Thank you for the info!!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

Hi @LaurentzT ,

Dataverse contains native relationships you can setup (1:N, N:N) within the platform. You could create an Order table and then Create the Delivery table with a Lookup column for the Order table. This creates the relationship behind the scenes between the two tables and in the Model App you would be setting the Order Primary Name field on the Delivery. In the model app you could create a tab or subgrid for Deliveries and then choose to add one and it will automatically set the Order on the Delivery and you could populate the additional details.

Is this what you are trying to do? A couple good links here:

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/create-edit-entity-relationships

https://learn.microsoft.com/en-us/training/paths/create-relationships-common-data-service/

Was this reply helpful? Yes No
LT-20081525-0 17 on at

Like (0)

Report

Hi,

I'm have no problem with the database schema but more with the function that needs to be executed.

For example (the real SP will have more logic and code) I have to write this function in Dataverse and call it from my ModelDriven App for the selected Order:

usp_CreateDeliveryByOrderID (@intOrderID int)
begin

insert into Delivery (DeliveryNr, DeliveryDate, CustomerID)
select fDeliveryNR(), getdate(), CustomerID from Order where OrderID= @intOrderID

insert into Deliverylines (DeliveryID, ProductID, Quatity, Description)
select @DeliveryID, ProductID, Quantity, Description from OrderLine where OrderID= @intOrderID
end

So how and where would I write that code in a pure Dataverse environment?
I think it would also be possible with a Power Automate Flow but that will be tomany steps and also slow...

Thank you!

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report

Hi @LaurentzT

I would assume you would want to look at Plugins here... Adding @ChrisPiasecki to the conversation as he is a pro-dev and might have better thoughts...

Was this reply helpful? Yes No
Verified answer

ChrisPiasecki 6,422 Most Valuable Professional on at

Like (1)

Report

Hi @LaurentzT,

If you're looking to encapsulate some specific business function/action that can be run on-demand or automatically, then a Custom Action or Custom API would be the way to go (which one you should choose depends on how you intend to invoke it). These can be invoked real-time via a Dataverse real-time workflow, JavaScript, Plugins, or some external integration. They can also be called asynchronously via Power Automate using the Dataverse connector.

Note that you should not do heavy processing inside the platform and should be kept well under 2-minutes of execution. If you need to do more heavy processing or asynchronous processing that also needs to integrate with other systems, then you should move this processing out to Power Automate, Azure Logic Apps, or Azure Functions, or Azure Data Factory.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
LT-20081525-0 17 on at

Like (1)

Report

Hi,

Thanks for the info!
Here is also a good blogpost: https://www.pragmatic-development.io/blog/implement-business-logic-with-dataverse-custom-api/

Was this reply helpful? Yes No