PowerApps Canvas app "flash" writing to a SQL database

[Anonymous]

The short form of my question is this:

Can I program my Canvas app to write a bunch of new records to SQL essentially "at once" instead of iteratively? If so, what would be the smart way to do that?

-=-=-=-=-

What I do at the moment:

My app allows a user to collect some number of bar code scans (anywhere from one to a couple hundred), and then for each scan, it adds a transaction ("item has been touched") record to a SQL database. Each transaction is a new row in the database.

The (fragment of) code that does this is:

   ForAll(
       ScanDataCollection,
       Patch(Master_Transaction_Log, Defaults(Master_Transaction_Log); {
           Timestamp: TransactionTimestamp_Value,
           Scan_Code: Result,
           Location: DD_Location.SelectedText.Value,
           Transaction_Type: TransactionType_Value,
           Agent_Name: User().FullName,
           Agent_Email: User().Email,
           Customer_Name: Upper(Cust_Name.Text),
           Customer_Email: Upper(Cust_NTAccount.Text),
           Customer_Signature_Location: CoC_FileName,
           Item_Description: "",
           SN_Work_Note: "Agent " & User().FullName & " performed the following action: " & TransactionType_Value & " against item " & Result & ".",
           SN_Customer_Note: ServiceNow_Customer_Note,
           Flag_Item_Undelivered: "", // we no longer will use this
           Flag_Item_OnTruck: "" // we no longer will use this
           });
   ); // ForAll

Master_Transaction_Log is the table in SQL. ScanDataCollection is the collection of all scan codes collected. Result is the specific scan code identified in the collection ScanDataCollection (that's just how PowerApps split it).

-=-=-=-=-

What it results in:

For small numbers of items, the ForAll logging loop takes a reasonable amount of time, 5-15 seconds, but as the quantity rises, the logging time takes longer and longer.

In specific, our delivery driver -- who is often logging a hundred or more items -- has to sit and wait for several minutes while all his transactions are logged. At his truck.

-=-=-=-=-

What I'm hoping to do:

It seems to me that there must be a way to essentially "flash" that data faster to the database.

Maybe the Patch command is too slow and I am just doing that a dumb way? Maybe there's a way to assemble my matrix of information into whatever PowerApps calls a 2D array and then execute a single SQL command to gobble that up? I dunno.

This is my first PowerApps app and my first database app and my first enterprise deployment, so I realize my learning curve is steep (although I have programmed in many weird languages throughout the past forty years, so I'm not a total slice of bologna). And also that there may be things I am missing that are obvious to a seasoned pro.

Advice is welcome!

Thank you kindly,

Edward

[StalinPonnusamy]

Hi @Anonymous 

We can do bulk operations using JSON, Power Automate, and SQL Stored procedures.

• We need to Convert Collection to JSON
• Call the Power Automate from PowerApps
• Power Automate Receive the JSON
• Call SQL Stored Procedure with JSON Parameter
• SQL
  • Receive the parameter
  • Insert Row(s) from JSON

Convert Collection to JSON

Collect(
    col_Items,
    Table(
        {
            Timestamp: "1TransactionTimestamp_Value",
            Scan_Code: "1Result",
            Location: "1DD_Location.SelectedText.Value",
            Transaction_Type: "1TransactionType_Value"
        },
        {
            Timestamp: "2TransactionTimestamp_Value",
            Scan_Code: "2Result",
            Location: "2DD_Location.SelectedText.Value",
            Transaction_Type: "2TransactionType_Value"
        }
    )
);
Set(
    itemsJSON,
    JSON(col_Items)
);

Power Automate

SQL Stored Procedure

CREATE PROCEDURE [dbo].[SaveJSON]
    @ParamJSON nvarchar(max)
AS
BEGIN
INSERT INTO [ScanItems] ([Timestamp],Scan_Code)
SELECT *
FROM OPENJSON(@ParamJSON)
  WITH (
    [Timestamp] NVARCHAR(100) '$.Timestamp',
	[ScanCode] NVARCHAR(100) '$.Scan_Code'
  );
END

 
Thanks,
Stalin - Learn To Illuminate

[StalinPonnusamy]

Hi @Anonymous 

My Sample contains only 2 columns. Here is the SQL code to test Stored Procedure

DECLARE @json NVARCHAR(MAX);
SET @json = N'[{
            "Timestamp": "1TransactionTimestamp_Value",
            "Scan_Code": "1Result",
            "Location": "1DD_Location.SelectedText.Value",
            "Transaction_Type": "1TransactionType_Value"
        },
        {
            "Timestamp": "2TransactionTimestamp_Value",
            "Scan_Code": "2Result",
            "Location": "2DD_Location.SelectedText.Value",
            "Transaction_Type": "2TransactionType_Value"
        }]';

EXEC SaveJSON @json

Select * from [ScanItems]

Result (2 rows inserted)

[StalinPonnusamy]

Hi @Anonymous 

Please let us know if anything needs on your post. We can help with this.

Please mark the post as Solved If I have answered your question.
Please give it a Thumbs Up if you find the suggestion helpful

Thanks,
Stalin - Learn To Illuminate

[Anonymous]

Hi StalinPonnusamy,

Work kinda gobbled up my brain last week, sorry.

I have only managed to successfully write one Power Automate flow (although I must learn how to use it more), and as far as Stored Procedures, I have never done that before. This is the first time I have ever done anything with a database at all.

I will look up what Stored Procedures are, and try to learn that.

I will experiment with how to create that initial collection such that it can be parsed into a JSON statement (JSON is also a thing I am not terribly familiar with).

In a less roundabout way, I'd like to say that although I am sure your answer is very rich and useful, I will have to work and experiment with several new-to-me technologies before I can see if it will solve my problem. This is not a reflection on you, just a reflection on my programming skills in this new area 😉

(one could ask "why is someone who is not sufficiently familiar with these technologies placed into a position of having to develop in them?" to which I could answer that every other dev resource at our company has absolutely refused to assist in any way with this project or any piece of it or to assist any member of my greater team. Which leaves the choice of downgrading to "tracking thousands of pieces of equipment using sticky notes" or me trying to climb up on new tech. As I really really want to help my team, my path is, awkwardly, "B".)

[StalinPonnusamy]

Hi @Anonymous 

It takes some time to learn new technologies. Sample provided which is completely working on that I created and tested for you.

Let me know if any questions, I can answer them.

Thanks,
Stalin - Learn To Illuminate

[takolota]

People with the same use-case & issues can also try using this batch SQL CRUD templates:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/SQL-Batch-Create-Read-Update-and-Delete/...

https://powerusers.microsoft.com/t5/Community-App-Samples/SQL-Batch-Create-Read-Update-and-Delete-Cu...