cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JR-BejeweledOne
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
JR-BejeweledOne
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
v-jefferni
Community Support
Community Support

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.

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.
JR-BejeweledOne
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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Users online (1,991)