cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sudosaurus
Post Partisan
Post Partisan

Create collection for drop-down input

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.

 

sudosaurus_0-1615383210536.png

 

Any help would be appreciated.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
sudosaurus
Post Partisan
Post Partisan

Hi @RandyHayes 

I managed to sort with this formula and its working as expected:

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

 

Although its a shame that the header also shows in the sub-menu, but I dont think there's a way around that?

 

Filtering and search is working as expected.

 

sudosaurus_0-1615493921615.png

 

 

View solution in original post

16 REPLIES 16
RandyHayes
Super User
Super User

@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. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
sudosaurus
Post Partisan
Post Partisan

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
Post Partisan
Post Partisan

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
Super User
Super User

@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. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
sudosaurus
Post Partisan
Post Partisan

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
Super User
Super User

@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?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
sudosaurus
Post Partisan
Post Partisan

@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.

 

sudosaurus_1-1615396115875.png

 

 

RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,252)