Hi @jamescosten ,
Based on what you provided, I built an App connected to a SharePoint List. I know you are using a database so some of your code will be different (such as using Find() instead of StartsWith() in the Filter)
The main Screen has a Search box, a Modern Controls Tab List, and a Gallery:
You said you currently have the "Tab List" - Items set to:
Choices('WTRF Database'.'Assigned To')
Is your "Assigned To" field a Choice Field or a People Picker?
I am using SharePoint as my data source so the above code will not return all the available choices for a People Picker for me. If that is working for you then jump down to where I talk about setting the "Items" property without using a Collection.
I think you should be using a Collection for the "Items" property of the Tab List but you don't have to.
If you were going to use a Collection, then you would do something like:
ClearCollect(colPeopleTabs, {DisplayName: "All"});
Collect(colPeopleTabs, AddColumns(Distinct('WTRF Database20240131', 'Assigned To'.DisplayName), "DisplayName",Value));
This is the complicated code to avoid using a Collection. You need to change it to match your data source names:
With(
{
outputTable: Ungroup(
Table(/* Create the table with child tables. If you want to join more tables, add more items to this table and the formula will work the same way*/
{tableObject: Table({Value:"All"})},
{tableObject: SortByColumns(ForAll(Distinct('WTRF Database20240131', 'Assigned To'.DisplayName), Value),"Value")}
),
"tableObject"
)/* Merges all the tables into a single one, but no duplicates removed */
},
ShowColumns( //Display only the columns needed
GroupBy(
outputTable,
"Value",//add the columns to extract distinct data. any column to be used has to be added here, and in the ShowColumns piece
"Grouped"//Last parameter can be any name as it's just the name for a child table with grouped data
),
//same columns used on the GroupBy piece except the last one (the child table name)
"Value"
)
)
I pulled this together with help from this blog post:
Power Apps: Merge tables/collections and get distinct records (without using Collect) - michelcarlo
by @michelcarlo (Second time in two days he has had a blog post to help out)
This code is for the Gallery - Items Property. It has nested If-Then's to accomplish what you are looking for. I used StartsWith() instead of Find() because that is what SharePoint supports. Since you are using a DB, you can change those to Find():
If(
!IsBlank(Searchbox_2.Text),
If(
WTRFWIPTABLIST.Selected.Value = "All",
//Return items JUST SEARCHING for the Tool Number
Filter(
'WTRF Database20240131',
StartsWith(
'Tool Number',
Searchbox_2.Text
)
),
//Return items using the search for Tool Number AND the User selected in the TAB
Filter(
'WTRF Database20240131',
And(
StartsWith(
'Tool Number',
Searchbox_2.Text
),
'Assigned To'.DisplayName = WTRFWIPTABLIST.Selected.Value
)
)
),
If(
WTRFWIPTABLIST.Selected.Value = "All",
//Return ALL items
'WTRF Database20240131',
//Return items using the User selecte in the TAB
Filter(
'WTRF Database20240131',
'Assigned To'.DisplayName = WTRFWIPTABLIST.Selected.Value
)
)
)
Hopefully this all makes sense and helps.
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up.
Thanks
-Mark
