cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bmmiller1989
Frequent Visitor

Issues with Inserting/Patching Large Data Sets

I am building an app that is taking data from an existing on-prem SQL datasource and performing manipulations/calculations on that data and then inserting it back into a different SQL table with new columns added that powerapps is responsible for creating.

 

I am looking for some help to get around the 8-10 min load times and latency issues when filtering and moving the data throughout the canvas app and specifically around patching to the datasource after the manipulations have been completed within the gallery.

 

There are over 150,000 records in the originating SQL DB. The main functions of the app I am creating is filtering through the data to find the following characteristics from Territory > Division > Unit Name. The unit name will give me specific details of what that unit has in terms of details but I have to filter down to get to that level.

 

Here are the current steps in place:

1. Screen that shows dropdown filters that allow to select a Territory > Divisions in that Territory > Unit Names inside that Division.

2. OnSelect button creates a collection of the data for that Unit Name and takes me to another screen full of galleries

3. There are 4 galleries that contains 10+ textboxes across where some of the data is being pulled from the Collection above and some of it is based on calculations from the Collection therefore creating a new column

4. The Save Button on this screen is currently taking the data from the Galleries, doing a ForAll > Patch into into a SQL table. The issue here is that it is needing to loop through each individual line of the gallery in order to patch it into SQL. These are all net new to the table (Defaults). Below is the current ForAll statement for one of my galleries. This statement DOES work but the issue is that it takes over 5 min to patch 30-40 rows of data. I need to find a more efficient way to get the data from these galleries into the final SQL datasource. 

 

ForAll(
Gallery.AllItems,
Patch(SQL,
Defaults(SQL),
{
AppealName: 'Appeal Name',
AppealType: 'Appeal Type',
AppealYear: 'Appeal Year',
AvgGift: Value(AvgGift.Text),
EstimatedQTY: Value(NumberMailed.Text),

Status: "Pending",

+ 15 more columns in the same format
}
)
);

I have tried to build a collection from the gallery and then collect it into the SQL table, but the issue I am having here is that it is combining the initial collection from the button click in step 2 above with the new columns created inside the gallery on the page load and making the new columns in the collection into a group which prevents me from being able to run the collect statement due to it trying to insert a control (the grouped item) instead of a text value.

ClearCollect(colTest,Gallery.AllItems);
Collect(SQL, colTest)

 

Capture.PNG

11 REPLIES 11
bmmiller1989
Frequent Visitor

@Eelman @mdevaney Thank you both for your insight.

 

I'm so very close but hopefully I can explain the issue I am still having.

 

So with the statement @mdevaney provided above, the ShowColumns data is showing correctly in the Collection. This data from ShowColumns is essential the same data that was in the 1st Collection since that is what is the datasource of the Galleries.

 

The issue is with the Add Columns. The data in the Collection with the AddColumns data is basically a duplication of the 4th row of data in the gallery that I am pulling from instead of pulling each unique row and putting it into the collection. How can I get 4 rows of unique data from the gallery and avoid the duplication of that 4th row?

 

Hopefully this vizio diagram can help visualize what needs to happen here.

 

Capture.PNG

Here is the statement I am currently running and the collection output. I have marked up what should be showing in the collection based on what is showing in the gallery:

ClearCollect(
colTest,
AddColumns(
ShowColumns(
Gallery_Acquisition.AllItems,
"DataSource",
"Division",
"CMS_Budging_ID"
),
"AvgGift",AvgGift.Text,
"EstimatedQTY",NumberMailed.Text
)
);

 

Capture.PNG

@bmmiller1989 

Let me have a go at explaining what I think is happening here:

 

1. Collection 1 created

2. Gallery 1 Items = Collection 1

3. TextInput fields (or they maybe labels?) are added to Gallery 1 for AvgGift and EstimatedQTY

4. Data is manually added to these input fields

5. Button click runs your code listed above

 

If this is correct, then the reason why AvgGift & EstimatedQTY are receiving row 4 data is because that's the last row selected before clicking your button ie Row 4 was the Gallery1.Selected item. If you were to add 4 rows of data THEN select, say, row 10 with your mouse then click the button, you'd find row 10 in your collection instead of row 4.

 

As I explained initially, any controls (labels, textinput, etc) that are added to a gallery that are not tied to the Items property of the gallery are SINGLE controls ... ie they are not adding new columns to your data they are just holding the data in these controls in memory (somewhere). 

 

The [easiest] way to get around this issue is build the collection schema FIRST as a NEW COLLECTION, then use this as your Items property of Gallery 1. 

 

So, in your diagram you'd insert a collection creation AFTER the creation of Collection 1 using your code above with these slight changes:

ClearCollect(
       colTest,
          AddColumns(
             ShowColumns(
               Collection_1,
               "DataSource",
               "Division",
               "CMS_Budging_ID"
             ),
         "AvgGift","",
         "EstimatedQTY",""
         )
);

Gallery 1 then uses this collection (colTest) as it's Items.

 

The textInputs controls, AvgGift and EstimatedQTY, in Gallery 1, can then be referenced in the DEFAULT property as ThisItem.AvgGift and ThisItem.EstimatedQTY.

 

Then for these textinputs, add this code to the OnChange property of each:

AvgGift

Patch(colTest, ThisItem, {AvgGift: txtAvgGift.Text})

** Note: I've called the input field txtAvgGift here. If it has the same name as the column heading your code will fail (same goes for txtEstimatedQTY below).

 

EstimatedQTY

Patch(colTest, ThisItem, {EstimatedQTY: txtEstimatedQTY.Text})

 

Now, when you add values to these fields in your gallery, colTest will get updated. You can then simply use @mdevaney  final Patch statement to send your data to your source ... no need for the AddColumns(ShowColumns ... etc before you do.

 

Hopefully I've explained this well enough? Let me know if you need further assistance.

 

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (43,272)