Record Manipulation to SQL Datasource

Looking at MS documentation, they always recommend to integrate the power apps with the CDM (common data model) – as this brings many benefits sometimes I have the need of direct query usage to work with the SQL Server.

E.g. a EventLog Table that “only adds” records and never updates them!

So to add a new record I use the Patch function as followed:

Patch('[dbo].[Finance.Wf.Event]',
    Defaults('[dbo].[Finance.Wf.Event]'),
          {
              CreatedBy: User().Email,
              CreatedAt: Now(),
              Event: "rejected",
              BusinessObject: "Invoice",
              BusinessObjectId: varDialogResponse.originalFunctionRequestID,
              Comment: varDialogResponse.responseData.textInput,
              Status: "rejected"
          }
)
Code-Sprache: JavaScript (javascript)

If you wanna update different tables at once, you can combine as many patches as you like by adding “;” inbetween the calls.

Here an example of updating 2 entities at once by using a lookup to filter:

Patch('[dbo].[Finance.Wf.Event]',
        Defaults('[dbo].[Finance.Wf.Event]'),
        {
            CreatedBy: User().Email,
            CreatedAt: Now(),
            Event: "reject",
            BusinessObject: "Invoice",
            BusinessObjectId: varDialogResponse.originalFunctionRequestID,
            Comment: varDialogResponse.responseData.textInput,
            Status: "rejected"
        }
        );
Patch('[dbo].[Finance.Wf.InvoiceHeader]',
        LookUp('[dbo].[Finance.Wf.InvoiceHeader]',InvoiceHeaderId = varDialogResponse.originalFunctionRequestID),
        {
            Status: "rejected"
        }
        )
Code-Sprache: JavaScript (javascript)