Appending item to Collection generated from DISTINCT

[ManishM]

I want to append an item to a collection that was created using DISTINCT?

ClearCollect(EmployeeList,Distinct(FeatureAccess,EmployeeName));

I want to append "All" to the top of the dropdown list that uses EmployeeList as Items property.

Thank you in advance.:)

[Paul_C]

Hi ManishM,

Let me confirm that I understand the problem. You created a collection using ClearCollect(EmployeeList, Distinct(FeatureAccess,EmployeeName)); and now you want to prepend "All" to the top of the list? ClearCollect lets you specify multiple items to add to the list (in order). So, I would recommend you replace your existing ClearCollect with this, if I understand the problem correctly: 

ClearCollect(EmployeeList, "All", Distinct(FeatureAccess,EmployeeName));

This will clear EmployeeList, then add "All" to it, then add the Distinct() result.

Hope that helps!

[ManishM]

Thanks for your response. Your solution did not work for me. The Dropdown list shows 2 empty entries when the collection name is used:

When I use .Value property it gives me "All" but does not give me the employee names

Here is the screenshot from File->Collection:

Many thanks for helping me on this.

Additionally, how should I go about to append an item to a collection without "clearcollect" it and rebuild it?

Thanks

[Paul_C]

Oh, sorry about that! I see what the problem is. It's adding the list EmployeeName as one item. Apparently, ClearCollect() and Collect() are smart enough to infer that it needs to add an item for each item in EmployeeName as long as it is the only thing being added. As soon as you give it two things to add, it simply treats each as items without inferring anything.

Anyway, the solution is a little odd looking, but very simple:

ClearCollect(EmployeeList, "All"); Collect(Distinct(FeatureAccess, EmployeeName))

Now "All" will be added and then the Collect will realize that we are giving it a list of items to add, and it will each one separately.

[ManishM]

Apologies for the delay in responding. Your approach prepended "All" but also added blanks equal to Distinct list count. Below are  the screen shots. In the Collection screenshot, notice the 2 ways it is holding the info - Values and Results. "All" is in values and rest is in Results. I understand the difference but need to merge the 2. Thanks

[ManishM · Accepted solution]

Yes! I got !

ClearCollect(BCategory, {Result:"All"});Collect(BCategory,Distinct('WPE Budget Category',BudgetCategory))

The above line prepended "All" since I added/tricked the "All" entry to be part of "Result". Not a clean solution but does the job for me.

[SteveMC]

I think you can clean it up a bit like this:

ClearCollect({Result:"All"}, Distinct('WPE Budget Category',BudgetCategory))

[ManishM]

I never tried this way but curious what will be the name of the collection in this case?

[SteveMC]

You're correct, the code is missing the collection name! Sorry about that...

The first argument is the collection name, then you can have multiple items seperated by commas.

ClearCollect( Collection, Item, ... )

• Collection – Required. The collection that you want to clear and then add data to.
• Item(s) - Required. One or more records or tables to add to the data source.

ClearCollect(BCategory, {Result: "All"}, Distinct('WPE Budget Category',BudgetCategory))

[ManishM]

Just tried and it works! Thanks for refining the code.