Dependent Dropdowns – Collections – Show All

[SG1990]

Dependent Dropdowns – Collections – Show All

I need 2 dependent dropdows for my gallery which include an “All” Option.

The first dropdown is called ddCompany, which will look up the company column from my Sharepoint List and adds an “All” option. This works fine by doing the following:

1. Created a collection to add all – using the formula:

ClearCollect(collectCompany, {Result: "All"});

Collect(collectCompany, Sort(Distinct('Property Database','Claimant Company'),Result),'Property Database' )

1. On the dropdown ddCompany – using the formula – collectCompany

As a result of the first dropdown having an “All” option, I have split the 2nd dropdown (ddYear), into 2 sections

1. When DDCompany = “All”- my formula for DDYear is collectYear_CompanyAll

The formula for collectYear_CompanyAll is:

ClearCollect(collectYear_CompanyAll, {Result: "All"});

Collect(collectYear_CompanyAll, Sort(Distinct('Property Database',’Financial Year’),Result))

This all works fine

My problem is the second section, this is:

1. When DDCompany = a select item.

If I didn’t want to include “All” in this dropdown, the following formula works fine for the dropdown:

Distinct(Filter('Property Database',ddCompany.Selected.Result='Claimant Company').'Financial Year','Financial Year')

However when I try to add this into a collection, to add “All” as formula below, the collection doesn’t work

ClearCollect(collectYear_CompanySelect, {Result: "All"});

Collect(collectYear_CompanySelect, Distinct(Filter('Property Database',ddCompany.Selected.Result='Claimant Company').'Financial Year','Financial Year'))

Can someone please help me either with this collection formula or a better solution to the second part of the dropdown ddYear

[WarrenBelz]

Hi @SG1990 ,

Your syntax logic is correct and I have just done a test here and got a collection of distinct values with "All" at the top.

However I think you may have two items reversed

ClearCollect(
   collectYear_CompanySelect, 
   {
      Result: "All"
   }
);
Collect(
   collectYear_CompanySelect, 
   Distinct(
      Filter(
         'Property Database',
         'Claimant Company' = ddCompany.Selected.Result
      ).'Financial Year',
      'Financial Year'
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[SG1990-1]

Thanks Warren, I really appreciate your help with this and your general involvement on the forum.

I have reversed the below items as you mentioned, and this works when i enter it straight into a dropdown, however when i use this within a collection it doesn't work.

'Claimant Company' = ddCompany.Selected.Result

Thanks

Stuart

[WarrenBelz]

Thanks @SG1990-1 ,

So I can be clear - this works in a drop-down

Distinct(
   Filter(
      'Property Database',
      'Claimant Company' = ddCompany.Selected.Result
   ).'Financial Year',
   'Financial Year'
)

and this does not work in a collection? What do you get in the collection?

ClearCollect(
   collectYear_CompanySelect, 
   Distinct(
      Filter(
         'Property Database',
         'Claimant Company' = ddCompany.Selected.Result
      ).'Financial Year',
      'Financial Year'
   )
)

[SG1990-2]

Yes you are correct.

In the collection there are no records.

[WarrenBelz]

Thanks @SG1990-2 ,

Very odd - try this and see what is in the collection - make sure there is something valid chosen in ddCompany

ClearCollect(
   collectYear_CompanySelect, 
   Distinct(
      Filter(
         'Property Database',
         'Claimant Company' = ddCompany.Selected.Result
      ),
      'Financial Year'
   )
)

[WarrenBelz]

Hi @SG1990-2 

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[SG1990-1]

Hi Warren, Sorry for the delay in replying, unfortunately your latest suggestion did not work either.

In the end i changed my approach  for this and did not use collections. Instead of using a collection to add in "all", i added a checkbox to say show all and just used formulas.

I would love to mark your comments as an accepted suggestion but think it could be confusing for other people reading the question. I have 'liked' all of your comments if this is any consolation.

Thank you again for your help and general community involvement.