cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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
8 REPLIES 8

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

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.

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. 

 

 

Highlighted
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
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 330 members 4,189 guests
Please welcome our newest community members: