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
Solved! Go to Solution.
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.
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.
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)} ) )
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
)
)
)
)
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.
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) )
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?
@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.
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.
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
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
User | Count |
---|---|
183 | |
46 | |
46 | |
34 | |
33 |
User | Count |
---|---|
254 | |
83 | |
78 | |
67 | |
66 |