Add New Columns to Collection

[slhangen]

Need help with proper verbiage to add a new column to existing collection.  Tried to patch and addColumn but get an error for each.

Collection is colIndicators

Want to add new column called colRiskFactorsPhysicalHealth and populate with values of listbox lbRiskFactorsPhysicalHealth

This is what I have currently:

Collect(colIndicators,
AddColumns(colIndicators,"colRiskFactorsPhysicalHealth",Concat(lbRiskFactorsPhysicalHealth.SelectedItems, Value & ",")))

Think I have punctuation out of place somewhere.

Thx for any help!!!

[WarrenBelz]

Hi @slhangen ,

The first issue I can see is the repetition of colIndicators. The second reference of this needs to be replaced with the data source you are collecting from (you have to add columns to existing data). If that is not the issue, happy to assist further.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[slhangen]

Thank yo for your response. I am very confused.

Maybe spelling out the end result I am looking for will get me going in the right direction:

• I have a collection: colIndicators
• I want to add a column to colIndicators titled colRiskFactorsPhysicalHealth
• I want to the value of this column to be populated from a list box lbRiskFactorsPhysicalHealth.SelectedItems
• I want to modify list box values into text with: Concat(lbRiskFactorsPhysicalHealth.SelectedItems, Value & ",")

Just not sure how to write it.  Thx again!

[WarrenBelz]

Hi @slhangen ,

The Collect bit at the start had me assuming you were adding records to the collection, therefore it had to come from data somewhere. If you simply want to ad a column (with the same value in each row), you do not need the Collect at the start.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[slhangen]

Yes sir, you were correct initially. 
I do want to add to the column with the value created by the following formula:

Concat(lbRiskFactorsPhysicalHealth.SelectedItems, Value & ",")

[WarrenBelz]

Hi @slhangen ,

It depends where you are putting this formula - for instance as the Items of a gallery

AddColumns(
   colIndicators,
   "colRiskFactorsPhysicalHealth",
   Concat(
      lbRiskFactorsPhysicalHealth.SelectedItems, 
      Value & ","
   )
)

would add a column to the collection with each item the same ( a comma-delimited list of the items selected in your combo box). Is this what you are trying to do?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[v-bofeng-msft · Accepted solution]

Hi @slhangen :

Adding a new column to an existing collection is more difficult. There are two reasons:

• The result the AddColumns() function returned is a new table with the transform applied. The original table isn't modified.
• Collect(colIndicators, AddColumns(……)) has a syntax error (to some extent this is a circular reference error).The data structure of a collection is determined by the formula that defines it (even if the formula is not executed). So once you complete this formula, this set actually already has the "colRiskFactorsPhysicalHealth" column, which will cause the AddColumns() function to report an error.

My plan is to add a temporary record containing the colRiskFactorsPhysicalHealth column to the collection, and then delete this record during execution. In this way, you can cleverly add a new column to the specified collection.For example:

Collect(colIndicators,{colRiskFactorsPhysicalHealth:"temporary record"});/*colIndicators adds an auxiliary record for adding ‘colRiskFactorsPhysicalHealth’ column*/
RemoveIf(colIndicators,colRiskFactorsPhysicalHealth="temporary record");/*Delete auxiliary records*/
UpdateIf(colIndicators,IsBlank(colRiskFactorsPhysicalHealth),{colRiskFactorsPhysicalHealth:Concat(lbRiskFactorsPhysicalHealth.SelectedItems, Value & ",")})/*Update all ‘colRiskFactorsPhysicalHealth’ columns*/

Best Regards,

Bof