AddColumns not working with collection

[Anonymous]

I am working with a list of approved software which users can request to be added to their machines. What I'm trying to do is create a collection which shows all the available software. This collection would be updated via the UI, then any software which is selected would be saved to a SharePoint list with their request. To show which ones the user has selected, I'm trying to add a column with a boolean value to the collection.

I can create the collection easily enough, however, when i try to add the column "isSelected" nothing appears when I check the collection afterwards. Have tried a couple different ways (detailed below) but all either throw errors or simply don't show anything.

The code below fires OnSelect of a button to prepare the collection before navigating to the screen which allows the user to pick the software from this collection via a Gallery.

Attempt 1:

ClearCollect(addSoftwares, 'Source Info - Approved Software List');
AddColumns(addSoftwares, "isSelected", false);

this straight up didn't work. The collection isn't updated.

I thought maybe the column was hidden so tried this:

ClearCollect(addSoftwares, 'Source Info - Approved Software List');
AddColumns(addSoftwares, "isSelected", false);
ShowColumns(addSoftwares, "isSelected");

This gives me an error "the specified column isSelected des not exist" at the ShowColumns method.

Last ditch attempt, I threw a ForAll in there to see if it would make a difference. no dice.

ClearCollect(addSoftwares, 'Source Info - Approved Software List');
ForAll(addSoftwares, AddColumns(addSoftwares, "isSelected", false));

This all I see in the collection for all cases above:

As far as I can tell, I'm following the documentation set out by Microsoft correctly. Any idea what I'm doing wrong here?

[Anonymous]

The same thing is happening with DropColumns and RenameColumns functions... guessing I am using them wrong somehow?

[mr-dang · Accepted solution]

Hi,

You can use AddColumns around the datasource as you are simultaneously Collecting it:

ClearCollect(addSoftwares, 
	AddColumns('Source Info - Approved Software List',
		"isSelected", false
	)
)

This means, "Make a collection called addSoftwares that adds a column to 'Source Info - Approved Software List' called 'isSelected' with every record set to false."

Your formulas did not work because AddColumns is not an 'active' action--it is temporarily applied (unless its result is collected, in which case the collection maintains the column). You can use it in the items property of a Gallery to temporarily add a calculated column without disturbing the original datasource. You can use it in the items property of a Dropdown to make a column that concatenates other columns to show.

Let me know if that works.

Brian

________

Raise the bar on Low Code!

[Anonymous]

Hi @mr-dang,

Thanks for the info. Your suggestion has worked for me.

It appears the same is true for DropColumns and RenameColumns functions as well.

Would be good if Microsoft had this in their documentation, it suggests that this is indeed an "active" function.

[mr-dang]

Also, you can wrap AddColumns() around another AddColumns(datasource,"columnname",Value) to add more columns.

[memsim1010]

@mr-dang

---

@mr-dangwrote:

Also, you can wrap AddColumns() around another AddColumns(datasource,"columnname",Value) to add more columns.

---

I have two calculated columns in a sharepoint list that i need converted into a date value. I can't get it quite right.  I'm trying: 

ClearCollect(Collection1, AddColumns(SPList.CalcColumn1, "DateValue1", DateValue(CalColumn1), AddColumns(SPList.CalcColumn2, "DateValue2", DateValue(CalcColumn2)))

What am I doing wrong?

[mr-dang]

Hi @memsim1010,

If I understand correctly, you would like to have 2 calculated columns for your datasource.

Here is your current formula formatted for readability:

ClearCollect(Collection1, 
    AddColumns(SPList.CalcColumn1, "DateValue1", DateValue(CalColumn1), 
    AddColumns(SPList.CalcColumn2, "DateValue2", DateValue(CalcColumn2))
)

My understanding is:

• You want to create shape your datasource, SPList, by copying it to Collection1 but with two calculated columns, DateValue1 and DateValue2.
• DateValue1 converts CalcColumn1 into a date value.
• DateValue2 converts CalcColumn2 into a date value as well.
• Can you clarify if CalcColumns are the name of the calculated column you want, or if they are the columns you want to operate upon? You may need to swap their spots in my solution if I misunderstood.

This is just a syntax error. You can revise your formula to:

ClearCollect(Collection1,
    AddColumns(
        SPList,
    "DateValue1",DateValue(CalcColumn1),
    "DateValue2",DateValue(CalcColumn2)
    )
)

This means, "Create a collection, Collection1, that includes everything in SPList, but with two columns added. The new column DateValue1 will be equal to the DateValue of the contents in CalcColumn1, and the new column DateValue2 will be equal to the DateValue of the contents in CalcColumn2."

Here's how AddColumns works:

AddColumns(datasource,"newcolumn1",[value for newcolumn1],"newcolumn2",[value for newcolumn2],...)

The first argument in AddColumns (blue) must be a table or a filter upon a table. In your original syntax, your logic was specifying the name of a column instead.

Note that you can add as many calculated columns as you want with a single AddColumns() function. You just need to separate them by commas.

However, the caveat is that if you want one calculated column to reference another column you had added (one calculated column relies on the value of another), then you'll need to wrap one AddColumns() around another because the first one would not exist for the second one to exist yet:

AddColumns(
    AddColumns(datasource,"newcolumn1",value),
"newcolumn2",newcolumn1+1
)

In the example above, newcolumn2 is the result of newcolumn1, but with 1 added to it.

Let me know if this helps.

Mr. Dang

_

[JustAnotherUser]

---

@mr-dang wrote:

Your formulas did not work because AddColumns is not an 'active' action--it is temporarily applied (unless its result is collected, in which case the collection maintains the column).

---

Why call it AddColumns when it doesn't actually add a column to a table.

It should be AddColumns(Source,NewTable,ColumnName,Expression) or the function name should be changed, but the documentation about the temporary nature of the function should be made MUCH clearer.

-------

AddColumns Link"The AddColumns function adds a column to a table, and a formula defines the values in that column. Existing columns remain unmodified."

-------

So this is wrong, it doesn't add a column to a table,

It takes the source table, adds a column to a NEW table which needs to be collected or assigned to a different datasource

Note:

It took me over 4 hours before I found this post to explain why it wasn't working correctly.

The crazy logic I am having to use all because there is no looping is just staggering.

I have Datasource1 with 20 columns 700+ rows, and Datasource2 with 5 columns and less than 200 rows. In order to try to generate a logical view of just the rows in Datasource1 that are related to DataSource2 I am going to have use 5 temporary collections all because there is no foreach;until or do-while or anything similar. And that doesn't even deal with the delegatable data issue. 

[Anonymous]

---

@mr-dang wrote:

Hi @memsim1010,

If I understand correctly, you would like to have 2 calculated columns for your datasource.

Here is your current formula formatted for readability:

ClearCollect(Collection1, 
    AddColumns(SPList.CalcColumn1, "DateValue1", DateValue(CalColumn1), 
    AddColumns(SPList.CalcColumn2, "DateValue2", DateValue(CalcColumn2))
)

My understanding is:

• You want to create shape your datasource, SPList, by copying it to Collection1 but with two calculated columns, DateValue1 and DateValue2.
• DateValue1 converts CalcColumn1 into a date value.
• DateValue2 converts CalcColumn2 into a date value as well.
• Can you clarify if CalcColumns are the name of the calculated column you want, or if they are the columns you want to operate upon? You may need to swap their spots in my solution if I misunderstood.

This is just a syntax error. You can revise your formula to:

ClearCollect(Collection1,
    AddColumns(
        SPList,
    "DateValue1",DateValue(CalcColumn1),
    "DateValue2",DateValue(CalcColumn2)
    )
)

This means, "Create a collection, Collection1, that includes everything in SPList, but with two columns added. The new column DateValue1 will be equal to the DateValue of the contents in CalcColumn1, and the new column DateValue2 will be equal to the DateValue of the contents in CalcColumn2."

Here's how AddColumns works:

AddColumns(datasource,"newcolumn1",[value for newcolumn1],"newcolumn2",[value for newcolumn2],...)

The first argument in AddColumns (blue) must be a table or a filter upon a table. In your original syntax, your logic was specifying the name of a column instead.

Note that you can add as many calculated columns as you want with a single AddColumns() function. You just need to separate them by commas.

However, the caveat is that if you want one calculated column to reference another column you had added (one calculated column relies on the value of another), then you'll need to wrap one AddColumns() around another because the first one would not exist for the second one to exist yet:

AddColumns(
    AddColumns(datasource,"newcolumn1",value),
"newcolumn2",newcolumn1+1
)

In the example above, newcolumn2 is the result of newcolumn1, but with 1 added to it.

Let me know if this helps.

Mr. Dang

_

---

Just wanted to say this worked for me with the same issue-thank you.