cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trjackson77
Helper II
Helper II

AddColums to a collection not working

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Eelman
Super User
Super User

@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))

 

 

View solution in original post

7 REPLIES 7
Eelman
Super User
Super User

@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))

 

 

View solution in original post

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)

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.

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.

@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.

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!

@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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,701)