AddColums to a collection not working

(0) Share

Report

Posted on by trjackson77

254

Hi,

I am building a collection from a SQL data source. The collection may have 6-7k records. At the end of the collection build, I wanted to add a column to the collection. I used the following in the OnVisible property of the screen:

AddColumns(colMassImport,"RecordAmended",false)

There were no errors in the code and I reloaded the app and there didn't appear to be any errors, however, the collection does not have the additional column.

Please advise on whether the above is correct or should I be looking at other methods to add a permanent column to the collection.

Many thanks,

Tim.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

@trjackson77

AddColumns isn’t a behaviour formula, meaning it needs to be used with a behaviour formula to work.

ClearCollect (for example) is a behaviour formula so you’d use it with AddColumns like this

ClearCollect( myCol, AddColumns(colMassImport, “RecordAmended”, false))

Was this reply helpful? Yes No
trjackson77 254 on at

Like
a
(0)

Report
Copy link

Link copied!
That worked, thank you very much for the speed reply. However, I did have to create another collection to add the columns to. It would not let me add the columns to 'itself' (the original colMassImport collection). Is this normal? I am concerned about performance when collecting a collection that already has 6k records.

ClearCollect(colWorkingRecords,AddColumns(colMassImport,"RecordAmended",false)

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

You can work it into your original collection call to help with performance. If you need any help, post that code and I’ll see if I can help you re-write it.

Was this reply helpful? Yes No
trjackson77 254 on at

Like
a
(0)

Report
Copy link

Link copied!
Your Offer is very welcome. I'm not seeing any specific issues currently, an initial load time of about 5 secs (acceptable). I am looking to simplify and streamline the app as much as possible though. The if statements are in to ensure the entire data source is loaded into the collection, thereby avoiding the delegation limit when filtering.

//initial load ClearCollect( colMassImport, '[dbo].[tjCostings]' ); //Further loads if necessary UpdateContext({varLastCostRecord: Value(Last(colMassImport).RowID)}); If( Last(colMassImport).RowID >= 2000, Collect( colMassImport, Filter( '[dbo].[tjCostings]', RowID > varLastCostRecord ) ) ); UpdateContext({varLastCostRecord: Value(Last(colMassImport).RowID)}); If( Last(colMassImport).RowID >= 4000, Collect( colMassImport, Filter( '[dbo].[tjCostings]', RowID > varLastCostRecord ) ) ); UpdateContext({varLastCostRecord: Value(Last(colMassImport).RowID)}); If( Last(colMassImport).RowID >= 6000, Collect( colMassImport, Filter( '[dbo].[tjCostings]', RowID > varLastCostRecord ) ) ); UpdateContext({varLastCostRecord: Value(Last(colMassImport).RowID)}); If( Last(colMassImport).RowID >= 8000, Collect( colMassImport, Filter( '[dbo].[tjCostings]', RowID > varLastCostRecord ) ) ); UpdateContext({varLastCostRecord: Value(Last(colMassImport).RowID)}); If( Last(colMassImport).RowID >= 10000, Collect( colMassImport, Filter( '[dbo].[tjCostings]', RowID > varLastCostRecord ) ) ); //addcolumns to data source ClearCollect(colWorkingRecords,AddColumns(colMassImport,"RecordAmended",false) );

The column I am adding needs to default to 'false', rather than 'null' if I add the column at the beginning.

Many thanks again for your offer of help.

Tim.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
@trjackson77

I've been toying with a new method to pull in >2000 records using Sequence() and your question got me to pull my finger out and get it done. I'm happy to say that I think I've solved it 🙂

You should be able to replace all of your code above with this (Note: just comment out your code, don't delete it before you run my code 🙂 )

// Get number of 2000 row batches needed to loop through // OK to ignore delegation warning here Set( vNum2000s, Round(First( Sort('[dbo].[tjCostings]',RowID,Descending) ).RowID / 2000, 0 ) ); // Create Start-End for each loop batch using Sequence() // Rename column "Value" to "Start" to keep things consistent, AddColumn "End" ClearCollect( colSeq, AddColumns(RenameColumns( Sequence(vNum2000s,0,2000), "Value", "Start" ), "End", Start + 2000 ) ); // Empty Collection that will be filled Clear(colSeqALL); // Loop through the Start-End table collecting record, AddColumns as/if required ForAll( colSeq As StartEnd, Collect(colSeqALL, AddColumns( Filter('[dbo].[tjCostings]', RowID > StartEnd.Start && RowID <= StartEnd.End ), "RecordAmended", false ) ) )

Note: This process works on a column in your data source that is either set as a NUMBER or is read as one.

Let me know if it works for you because it did for me.

Was this reply helpful? Yes No
trjackson77 254 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi, sorry, I've been out of the office for a day or two. I will try and get this code incorporated into OnVis next week. It looks like a really cool solution.

Many thanks for your help on this!

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@trjackson77

I found a few little errors in this code I posted above so have tweaked it to get it working correctly. Previously, if you had between, say, 2500 - 3000 records the code wouldn't collect all the records due to the use of Round(). I've added in RoundDown() with Round() to ensure all records are collected.

I've made a blog on this subject (my first 🙂 ) here so be sure to check it out before testing on your end.

Cheers

Was this reply helpful? Yes No