cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

AddColumns not working with collection

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:

powerappsbrokenaddcolumn.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
mr-dang
Level 10

Re: AddColumns not working with collection

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!

Microsoft Employee
@8bitclassroom

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Re: AddColumns not working with collection

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

mr-dang
Level 10

Re: AddColumns not working with collection

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!

Microsoft Employee
@8bitclassroom

View solution in original post

Anonymous
Not applicable

Re: AddColumns not working with collection

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.

Highlighted
mr-dang
Level 10

Re: AddColumns not working with collection

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

Microsoft Employee
@8bitclassroom
memsim1010
Level: Powered On

Re: AddColumns not working with collection

@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
Level 10

Re: AddColumns not working with collection

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

 

_

Microsoft Employee
@8bitclassroom
JustAnotherUser
Level: Powered On

Re: AddColumns not working with collection


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

 

 

MaxDonen
Level: Powered On

Re: AddColumns not working with collection


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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,573)