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

collect with show column and filtering

I need to collect filtered data from a table into a collection.   I do not however need all the columns.  

 

If I do this: 

 

ClearCollect(HolidayCollectionPY, Filter(HolidaySchedules, DropdownCountry.Selected.Name in Country And Year = Text(varPreviousYear)))

 

It brings in the records but only 2 columns have data in them.  The remaining columns in the new collection are blank even though they aren't blank in the table.

 

Dataverse TableDataverse Table

 

HolidayCollection from dataverse tableHolidayCollection from dataverse table

 

If I do this however, I get an error on the filter and an unspecified error on the ShowColumns

 

ClearCollect(HolidayCollectionPY, ShowColumns(HolidaySchedules, "crc39_country", "crc39_region", "crc39_holiday", "crc39_prefix", "crc39_suffix", "crc39_halfday", "crc39_freebusy", "crc39_holidayname", (Filter(HolidaySchedules, DropdownCountry.Selected.Name in Country And Year = Text(varPreviousYear))));

 

ShowColumnsError.png

 

 

Thanks in advance for the assist

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

I was able to use your example to get the proper syntax for the Collect with ShowColumns and Filter and that worked.

 

ClearCollect(HolidaysTemp,

ShowColumns(

Filter(HolidaySchedules, DropdownCountry.Selected.Name in Country And Year = Text(varPreviousYear)),

"crc39_country", "crc39_holiday", "crc39_holidayname", "crc39_region", "crc39_prefix", "crc39_suffix", "crc39_halfday", "crc39_freebusy"

)

);



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

Could you please share more details about your table? What are the column types of those blank columns in the Dataverse table, LookUp type?

 

If they are LookUp type columns, then you will not be able to create collections with data in those columns. Below is a thread that talking about the same issue:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Saving-lookup-field-in-collection/td-p/43467...

 

So, it is possible to create the collection with LookUp column value, with the workaround in below thread:

https://community.dynamics.com/365/b/linn-s-power-platform-notebook/posts/bug-using-the-clearcollect...

 

So, in your case, you need to modify your formula to be like:

ClearCollect(HolidayCollectionPY,
           DropColumns(
                       AddColumns(
                                   ShowColumns(
                                                Filter(HolidaySchedules, 
                                                     DropdownCountry.Selected.Name in Country And Year = Text(varPreviousYear),
                                                     "LookUpColumn1", " LookUpColumn2", ...
                                                ),
                                  "TempColumn",
                                  First(HolidaySchedules). LookUpColumn1
                       ),
            "TempColumn"
            )
)

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

They are all text columns.

 

holidayschedulescolumn.png

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.
Super User
Super User

I was able to use your example to get the proper syntax for the Collect with ShowColumns and Filter and that worked.

 

ClearCollect(HolidaysTemp,

ShowColumns(

Filter(HolidaySchedules, DropdownCountry.Selected.Name in Country And Year = Text(varPreviousYear)),

"crc39_country", "crc39_holiday", "crc39_holidayname", "crc39_region", "crc39_prefix", "crc39_suffix", "crc39_halfday", "crc39_freebusy"

)

);



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

View solution in original post

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Users online (99,121)