Hi, Iโm trying to make an App formula in a form field that returns data from another table based on the answer to a previous question. In other words, I have three teams and if I answer Team 1 to the first question, I want it to automatically fill this field with info from a slice I made of people who are on Team 1. I feel like Iโm close, but need a little more guidance. Thanks!
Solved! Go to Solution.
Correct
Then you can use this expression:
//Assuming that you have 5 location columns
//You can alter the expressions as per needs
SWITCH(
[Location],
"KeyColumnValueForLocation_1",SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Quantity at Location1]>0)),
"KeyColumnValueForLocation_2",SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Quantity at Location2]>0)),
"KeyColumnValueForLocation_3",SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Quantity at Location3]>0)),
"KeyColumnValueForLocation_4",SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Quantity at Location4]>0)),
SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Quantity at Location5]>0)) //Default value
)
ANY(SELECT(AnotherTable[ReturnValueColumn],[LookupColumn]=[_THISROW].[LookupValueColumn]))
OR
LOOKUP([_THISROW].[LookupValueColumn],โAnotherTableโ,โLookupColumnโ,โReturnValueColumnโ)
Hey, when I used the ANY or LOOKUP expressions it only returned one personโs value where I want it to basically return an Enumlist separated by commas.
I used this expression
IF([Team]=โTeam 1โ, Personnel List Team is Team 1[Personnel ID], IF([Team]=โTeam 2โ, Personnel List Team is Team 2[Personnel ID], Personnel List[Personnel ID]))
And so far it works pretty well but I understand that Iโll have to keep repeating the formula for when I add more teams. Any advice to simplify this?
To return a list:
SELECT(
AnotherTable[ReturnValueColumn],
[LookupColumn]=[_THISROW].[LookupValueColumn]
)
I have a follow up question to this. I have a similar situation in a later part of the form where I previously selected a Site from a REF and then want an ENUMLIST to display a slice of what is at that Site. How do I have it return a slice based on what I previously selected and not just a value from another table?
@Luke_Vancleave
Can you please elaborate your idea about โโฆreturn a slice based on what I previously selected and not just a value from another tableโฆโ?
Sure! So I have a Large table of tools. Thereโs around 100 columns of data for each row saying the quantity at various locations. To make it easier to view, I made Slices of that table so I can view only what is at those locations.
Iโm making a form that asks what location the user worked at that day and to select which tools at that site they used.
I have a separate table of locations listed too that I use for other purposes.
Iโd like to have the form work so when the user chooses the Location based on a REF to that table, it fills in an ENUMLIST using the Slice of what was at that location for the user to select.
@Luke_Vancleave
I believe below expression should suffice though, however Iโm now aware of your sheet and ref tableโs structure so it might not be an exact fit for you
SELECT(ToolsTable[ToolName],[Location]=[_THISROW].[Location])
So this is saying to display all the Tools from the ToolsTable if their Location column equals what I entered in the formโs previous question?
My table of tools doesnโt have a single location column because different quantities of each tool might be at different locations. For example, there might be 5 "Hammer"s at location one, 2 "Hammer"s at location two, and 0 "Hammer"s at location three. So because there might be different quantities of the same tool at different locations, each tool has columns for โQuantity at location 1โ, โQuantity at location 2โ, โQuantity at location 3โ and so on.
Maybe I could have an expression that says IFS(The previous entry selected Location1, Filter the tool table to only show rows where the โ[Quantity at location 1]>0โ, and then write the same for each location?
Correct
Then you can use this expression:
//Assuming that you have 5 location columns
//You can alter the expressions as per needs
SWITCH(
[Location],
"KeyColumnValueForLocation_1",SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Quantity at Location1]>0)),
"KeyColumnValueForLocation_2",SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Quantity at Location2]>0)),
"KeyColumnValueForLocation_3",SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Quantity at Location3]>0)),
"KeyColumnValueForLocation_4",SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Quantity at Location4]>0)),
SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Quantity at Location5]>0)) //Default value
)
Thatโs outstanding! Thank you.
So just for my understanding of whatโs going on here, Iโm telling this ENUMLIST to switch what it is displaying by filtering from my ToolsTable based on the [quantity at location column] depending on what I had selected previously?
@Luke_Vancleave
Thatโs correct
Iโm sorry to keep doing this, but every time I learn more it opens a door to something else that I could do.
Letโs say I have another question on my form after I enter a location that also filters the list by asking if the items are an Asset or Consumable which all tools have in one column. How could I add this into the equation too so that it filters the list based one the location and type I selected previously?
@Luke_Vancleave
Please check
//Assuming that you have 5 location columns
//Filtering the tools as per location, type and related quantity
SWITCH(
[Location],
"KeyColumnValueForLocation_1",SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Type]=[_THISROW].[Type],[Quantity at Location1]>0)),
"KeyColumnValueForLocation_2",SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Type]=[_THISROW].[Type],[Quantity at Location2]>0)),
"KeyColumnValueForLocation_3",SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Type]=[_THISROW].[Type],[Quantity at Location3]>0)),
"KeyColumnValueForLocation_4",SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Type]=[_THISROW].[Type],[Quantity at Location4]>0)),
SELECT(ToolsTable[ToolName],AND([Location]=[_THISROW].[Location],[Type]=[_THISROW].[Type],[Quantity at Location5]>0)) //Default value
)
I need to rephrase.
I have three questions to filter and select from. The first is Location which filters what Items I can select, which then filter what Actions I can do to them.
User | Count |
---|---|
43 | |
29 | |
25 | |
23 | |
13 |