Create collection for drop-down input

[sudosaurus]

Hello,

I am struggling with creating a collection for a drop-down input box to list categories from a choice column in a SharePoint list.

The text boxes below this are being populated with the text action based on:
ThisItem.'Contract Category'.Value
So these list items are returning the right value from this column.

However I wish to have drop-down in header above with default text of "Category" and can drop-down with the options available in the list - so that I can filter by Legal, Software, etc.

Any help would be appreciated.

Thanks

[RandyHayes]

@sudosaurus 

You don't need a collection for this. 

Set your Items property of the Dropdown to: 

SortByColumns(
    Distinct(yourDataSource, 'Contract Category'.Value),
    "Result"
)

If you want to filter out any blank results:

SortByColumns(
    Filter(
        Distinct(yourDataSource, 'Contract Category'.Value),
        !IsBlank(Result)
    ),
    "Result"
)

If you want to provide an "All" at the start of your list:

With({_items:
    SortByColumns(
        Filter(
            Distinct(yourDataSource, 'Contract Category'.Value),
            !IsBlank(Result)
        ),
        "Result"
    )},
    
    ForAll(Sequence(CountRows(_items)+1,0),
        {Result: If(Value=0, "All", Last(FirstN(_items, Value)).Result)}
    )
)

Then you can use that in your Filter for the Items of the Gallery:

Filter(yourDataSource, 
    'Contract Category'.Value = yourDropdown.Selected.Result
)

IF you use the "All" option for your Dropdown, then this formula for the Gallery Items:

Filter(yourDataSource, 
    (yourDropdown.Selected.Result = "All" || 'Contract Category'.Value = yourDropdown.Selected.Result)
)

I hope this is helpful for you. 

[sudosaurus]

Hi @RandyHayes 

That sort of works for the drop-down, however I wish the default menu text to display as "Category" and then have the available categories beneath to filter by.

Nm, worked it out by eding "All" text to "Categories"

With({_items: SortByColumns( Filter( Distinct(Register, 'Contract Category'.Value), !IsBlank(Result) ), "Result" )}, ForAll(Sequence(CountRows(_items)+1,0), {Result: If(Value=0, "Categories", Last(FirstN(_items, Value)).Result)} ) )

[sudosaurus]

Hi @RandyHayes ,

How would I  add your last option to my gallery items?

If(
    Directorate_dd.Selected.Result = "Directorate" And ContractStatus_dd.Selected.Result = "Contract Status",
    Search(
        Register,
        Search_textinput.Text,
        "ContractName", "Title", "External_x0020_Party", "ContractDescription", "Contracttrackingdetails", "Contracttrackingdate"
    ),
    If(
        Directorate_dd.Selected.Result = "Directorate",
        Filter(
            Search(
                Register,
                Search_textinput.Text,
                "ContractName"
            ),
            'Contract Status'.Value = ContractStatus_dd.Selected.Result
        ),
        If(
            ContractStatus_dd.Selected.Result = "Contract Status",
            Filter(
                Search(
                    Register,
                    Search_textinput.Text,
                    "ContractName"
                ),
                Directorate.Value = Directorate_dd.Selected.Result
            ),
            Filter(
                Search(
                    Register,
                    Search_textinput.Text,
                    "ContractName"
                ),
                Directorate.Value = Directorate_dd.Selected.Result,
                'Contract Status'.Value = ContractStatus_dd.Selected.Result
            )
        )
    )
)

[RandyHayes]

@sudosaurus 

Filter(Register,
    (Search_textinput.Text in ContractName ||
     (Directorate_dd.Selected.Result = "Directorate" && 
      ContractStatus_dd.Selected.Result = "Contract Status" && 
      Search_textinput.Text in Title & External_x0020_Party & ContractDescription & Contracttrackingdetails &Contracttrackingdate
     )
    ) &&
     
    (ContractStatus_dd.Selected.Result = "Contract Status" && Directorate.Value = Directorate_dd.Selected.Result ||
      'Contract Status'.Value = ContractStatus_dd.Selected.Result
    ) &&
    
    (yourDropdown.Selected.Result="Categories" || 'Contract Category'.Value = yourDropdown.Selected.Result)
)    

Not entirely sure on your If...If...logic in your formula, but the above should be functionally equivalent. 

[sudosaurus]

I've tried using this code and nothing in my gallery is displayed.

Filter(Register, (Search_textinput.Text in ContractName || (Directorate_dd.Selected.Result = "Directorate" && ContractStatus_dd.Selected.Result = "Contract Status" && Search_textinput.Text in Title & External_x0020_Party & ContractDescription & Contracttrackingdetails &Contracttrackingdate ) ) && (ContractStatus_dd.Selected.Result = "Contract Status" && Directorate.Value = Directorate_dd.Selected.Result || 'Contract Status'.Value = ContractStatus_dd.Selected.Result ) && (ContractCategory_dd.Selected.Result="Category" || 'Contract Category'.Value = ContractCategory_dd.Selected.Result) )

[RandyHayes]

@sudosaurus 

Yeah, I am guessing that I did not completely understand your logic in the formula you had for the dropdowns.  Can you explain what impact the dropdowns are to have on the filtering?

[sudosaurus]

@RandyHayes 

So I have 3 drop-downs.

- Directorate
- Contract Status
- Category

I may wish (in this example) to filter by "Corporate Services" and then filter by Contract Status and select either "Extended" or "Current". Filtering by either will still retain the Directorate/Department regardless of the Status I have selected. But I also want to be able to filter on "Category".

Additionally I am needing to search across multiple fields in the form view (which works) but doesnt search against category as I understand PA cannot search against a choice column.

[RandyHayes]

@sudosaurus 

Okay, that is not a problem.  Yes, you can search on the choice columns as well.  You may have noticed that I had changed from Search to Filter in the last formula.  This was intentional as it is more flexible and with that you can search on category.

What I am not understanding is, in your last formula you had specifically changed the search to ONLY include the Contract Name in some situations and others in another situation.  I did not understand that logic.

I'm going to throw some caution to the wind and go out on a limb and provide this formula as I believe that is perhaps where you are trying to go.

Filter(Register,
    (Search_textinput.Text in Title & External_x0020_Party & ContractDescription & Contracttrackingdetails & Contracttrackingdate & 'Contract Category'.Value
     ) &&
    (IsBlank(Directorate_dd.Selected.Result) || Directorate.Value = Directorate_dd.Selected.Result) &&
    (IsBlank(ContractStatus_dd.Selected.Result) || 'Contract Status'.Value = ContractStatus_dd.Selected.Result
     ) &&
    (yourCategoryDropdown.Selected.Result="Categories" || 'Contract Category'.Value = yourCategoryDropdown.Selected.Result)
)   

With the above, you might also want to consider changing the Dropdowns (except category) to AllowEmptySelection.  The formula will account for empty values.  So that a user and choose not to filter on a particular department or Status.

[sudosaurus]

@RandyHayes 

A little confused, as its still not working 😕

Filter(Register,
    (Search_textinput.Text in Title & External_x0020_Party & ContractDescription & Contracttrackingdetails & Contracttrackingdate & 'ContractCategory'.Value
     ) &&
    (IsBlank(Directorate_dd.Selected.Result) || Directorate.Value = Directorate_dd.Selected.Result) &&
    (IsBlank(ContractStatus_dd.Selected.Result) || 'Contract Status'.Value = ContractStatus_dd.Selected.Result
     ) &&
    (ContractCategory_dd.Selected.Result="Category" || 'ContractCategory'.Value = ContractCategory_dd.Selected.Result)
)   

I'm thinking maybe the "Items" value on Directorate and Contract Status are set to the following:

Distinct(colDirectorate,Directorate)
Distinct(colStatus,Status)

Should I change the "Items" value for each of these to work in the same way as the Contract Category column?
In this format?

With({_items: SortByColumns( Filter( Distinct(Register, 'Contract Category'.Value), !IsBlank(Result) ), "Result" )}, ForAll(Sequence(CountRows(_items)+1,0), {Result: If(Value=0, "Category", Last(FirstN(_items, Value)).Result)} ) ) 

These were previously setup as collections by someone who originally designed the app of which I've taken over development of.

Name of contract category dropdown: ContractCategory_dd

Name of Directorate dropdown: Directorate_dd

Name of Contract Status dropdown: ContractStatus_dd