Enter multiple records into PowerApps and then into SQL

[Gjakova]

Hi there, I was wondering if the following is possible in Power Apps:

I have created an app where our finance department can enter invoices. Let's say that each invoices has like 6 columns which needs to be filled in PER invoice. If you fill each column in it will take you 10 seconds, meaning that 10*6 = 1 minute per invoice.

Now let's say that someone has to fill in 300 invoices in one day, that means 300 minutes/60 = 5 hours of work!

The finance department asked me if it is possible to upload an Excel file into the PowerApp so that their 300 invoices can be uploaded at once into the app.

My first thought was that it was not possible, since I do not have the authority to make changes to the SQL DB myself and Finance neither, I was wondering if there is some way that I could upload an Excel document (based on a template) into e.g. an attachment box in Power Apps, then through Power Automate, run through all those 300 rows and insert them into my SQL DB.

Or perhaps a copy/paste into PowerApps, create a collection and then Patch that collection into my SQL DB.

My last hope is to create a template just for Excel and then let someone from IT import that straight into SQL, but it would be great if it could be done just through Power Apps (in combo with Power Automate if needed). 

Does someone know if this is possible and if someone has any tips/advice?

Thanks in advance!

[BCLS776]

The functionality you've described sounds very possible - using tables of data to populate records in your SQL DB, which will then be used in invoices. Here's the point that brings up concerns: Either the user of the app or the creator of a Power Automate flow, must be given sufficient access to the SQL database to be able to write those records. Without that permission, no app or flow will do this successfully.

Talk to your IT admin to see how this can be done within your org's policies. Perhaps a special user account can be set up that has sufficient privileges to perform the operations?

Hope that helps,

Bryan

[Gjakova]

Hi there, that would be no problem. Through the app they can get access to the SQL DB, my concern is really with the functionality itself since I have no idea how to do it. Would you perhaps know a way? Hope you can help!

[BCLS776]

You'll need to decide first whether you want to use a Power App or a Power Automate flow:

• Use a Power App if you need user intervention in the upload, such as choosing specific records
• Use a flow if you simply want to have it run and forget about it

They each work a bit differently, but the basics are:

• You will connect the app/flow to a specific Excel table and your SQL DB
• You will loop through all the records in the Excel table (ForAll in PowerApps, Apply To Each in flow)
• You will define the record field to copy from Excel to the SQL table (Patch in Power Apps, Create Item in flow)

Lots of options, so I suggest doing some searches on here (and the related Power Automate forum) for examples - lots of users have done this before.

Bryan

[Gjakova]

Hi @BCLS776 I'm not having any luck with this, it is I started at 10:00 and it is 21:00 now, with some small breaks I've spent almost half a day on it and I'm still nowhere, I'm feeling really stupid. Do you perhaps know what I'm doing wrong? I have opened another topic on the Power Automate forum: From SharePoint folder to SQL database - HOW TO? - Power Platform Community (microsoft.com)

I wanted to take the Power Apps route, but the only way I could connect to my Excel file in OneDrive was through 'My Files', but I want the Excel file/template to be on a shared library so that other can use it, instead of tied to one account.

[BCLS776]

Oh dear! That's way too big of a headache - reach out sooner next time!

Here is the basic structure of what you need to do in Power Apps:

• Create a new, blank app and put a single button on the screen
• Connect this app to your two data sources - your Excel table and your SQL table (I will assume names of ExcelTable and SQLTable in the code here)
• Add the following code to the OnSelect of the button:

ForAll(ExcelTable As aSource,
    Patch(SQLTable, Defaults(SQLTable),
        {
        sqlColumn1: aSource.column1,
        sqlColumn2: aSource.column2,
        sqlColumn3: aSource.column3
        }
    )
);

You'll need to change the column names to match what is in your Excel and SQL tables in the three example fields I show in this Patch.

When you run this app and press the button, it will go line by line through the Excel file and copy the corresponding values to the designated columns in a new record in the SQL table. This is crude and has no error checking, but we can work on that after you get a few records moved over.

Note too: The Excel file must be located in your OneDrive for now and it must be there when you are setting up the data connection. This is a reality of data connections in Power Apps, but we'll help you through that too.

Bryan

[Gjakova]

Hey amazing! thanks a lot, I've tested it out and it works! Funny thing is, I already had an fx formula which was 99% the same:

Clear(colTest);
ForAll(
    Gallery5.AllItems,
    Collect(
        colTest,
        {
            ContractID: ThisRecord.ContractID,
            Jaar: ThisRecord.Jaar,
            Omzet: ThisRecord.Omzet,
            Kosten: ThisRecord.Kosten,
            Winst: ThisRecord.Winst
        }
    )
);

But yours worked in no time, I have around 18 columns and for some columns I need to do a lookup to a Primary Key from another table (inside PowerApps), so that works great. For some columns I also use: Today() and User().FullName

Thanks to you I am now able to:

1. Loop through all my rows from my Excel file;
2. Patch them to my SQL database

Now I have some more questions left... if you don't mind:

Since I am building this for an external company, a few issues come to my mind:

1. When I want to create a template Excel file for them, where do I store it?
   1. Currently, the Excel template is located in 'My Files' at OneDrive, if I do this at the client, do I need to pick one of their users and store it on his account?
   2. I also made the following fx formula:

//before looping I will refresh the Excel file located in OneDrive:
Refresh(TableExcel);
//when the Refresh is done, the looping will start
ForAll(
    TestSQLtable As bSource,
    Patch(
        TestSQLtable,
        Defaults(TestSQLtable),
        {
            ContractID: bSource.ContractID,
            Jaar: bSource.Jaar,
            Omzet: bSource.Omzet,
            Kosten: bSource.Kosten,
            Winst: DataCardValue39.Text
        }
    )
);
//once the looping is done and the data is patched to the SQL database, the Excel template will be cleared
//we clear this because then another user or after this session, we can start fresh again
Remove(TableExcel, TableExcel, All);
//refreshing the Excel template again, to make sure that everything is clear
Refresh(TableExcel);

3. I insert my comments to explain why I did those things

4. My fourth question is: is it an idea to create a duplicate SQL table, where I push above data, then I will connect that duplicate SQL table to my PowerApp. The user than can check if everything will be published to SQL as desired. If the user agrees, then he could run another button where a Stored Procedure in SQL will be activated (a flow which actives the SP in SQL). The SP basically copies the data from that duplicate table and inserts it into the main table, then it clears the duplicate table again for another session.

4a): or is there another and better way to have an extra user check before filling the database?

4b); the reason I am asking this is because the users of PowerApp can't login to SQL, they just have a connection with SQL through PowerApp.

5. When a user wants to fill in other data in the Excel template, located in OneDrive, does he needs to do some certain steps, for example: when I was testing and when I wanted to insert new data, afterwards, my FX formula did not work anymore, even when the Excel was closed. It seemed like the 'Format as Table' was gone, but I couldn't restore it anymore...

@BCLS776 Thanks so much, at least I know that I have to struggle less tomorrow, looking forward to hear your input! 🙂 🙂

[BCLS776]

OK, I'll try to answer as many of those questions as I can.

You can put the Excel file in a user's OneDrive, or it can be shared on a Sharepoint site, such as the one behind a team on Teams. When you play with the connector, you'll get a sense of where you can browse into and connect to a file. Note, if the file gets deleted, saved under a new name, or even re-saved under the same name it breaks the connection with Power Apps. You'll need to educate the users about opening the file, copying in the data (copy-paste special-values, without disturbing the table), closing it, and leaving it alone otherwise. In fact, even having the file open while the app runs could cause a problem.

To give your users the ability to check what data they are loading, consider doing this work flow:

1. Display the Excel table first using a gallery or datatable. If you want to directly mimic what the SQL table will look like, pull the data into an internal collection with the same column names as the SQL table.
2. If satisfied, the user activates the button to push the data to SQL

This saves creating another table in SQL that might confuse others.

Also, doing a Remove-All operation is OK until you inadvertently remove data that was not yet posted to SQL. A better practice is to line-by-line compare the data to the SQL version and use RemoveIf() to delete the duplicates.

Finally, do you need those Refresh() operations? They are especially slow on Excel tables and the app reads the table during the ForAll operation anyways.

Glad to hear you're making progress!

Bryan

[Gjakova]

Hi @BCLS776, due to some other unrelated issues I didn't have time to go in-depth to try it all out, but now I was testing it and I do have a question (still)...

I created a template and uploaded it to OneDrive (my files) and I plan on sharing that with other users.

I created a "Master Sheet" in Excel which is uneditable, so other users can only copy/paste their info into it.

Since PowerApps loads at default 500 rows I thought to myself, let me format the table in Excel from row 1 till 499 (including headers). See result:

Also, the chance that colleagues would want to insert more than 500 rows is 0.0001%

The problem now is, I ran the query you provided earlier (with the ForAll loop), but it doesn't stop at row 22, but goes till row 499... Here is my script:

Clear(sqlCollect);
ForAll(BATCH As excelSource,
    Collect(sqlCollect,
        {
            BookingNumber: Value(DataCardValue61_1.Text),
            BookingDate: DatePicker1_1.SelectedDate,
            GrossPremium: Value(eSource.GrossPremium),
            Commission: Value(eSource.Commission),
            ...
            ...
            Note: excelSource.Note,
            Location: excelSource.Location,
            CreatedBy: User().FullName,
            CreatedDate: Today()
            }
    )
);

... = in my real script I have 10 other columns based on excelSource

So if I would Patch this to my SQL database, it would also upload the other 477 rows which are empty (except for the column that PowerApps has created).

Would you happen to know a reason for this and how I could solve it? I thought it had to do with my template, but since it could break so easy I just wanted to create a table of 500 rows and that nobody has to touch it anymore. I thought the ForAll would stop at row 22. Happy to hear your advice 🙂

[BCLS776]

As you've discovered, if an Excel table is defined with many rows (blank or not) and we put all those rows into a ForAll, all those rows go through the same operation.

There is a way to help this. The first argument in the ForAll, "Batch", represents the whole table. We can replace Batch with a Filter that returns all of the non-blank rows. Replace the first line in your code with this:

ForAll(Filter(BATCH, !IsBlank(columnName)) As excelSource,

and substitute columnName for a valid column name from your table that contains data when you have a valid record. This code will restrict the ForAll statement to operate only on records that have something in the column you choose.

Bryan