Dependent App Formula

[Luke_Vancleave]

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!

[LeventK]

ANY(SELECT(AnotherTable[ReturnValueColumn],[LookupColumn]=[_THISROW].[LookupValueColumn]))

OR

LOOKUP([_THISROW].[LookupValueColumn],“AnotherTable”,“LookupColumn”,“ReturnValueColumn”)

[Luke_Vancleave]

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?

[LeventK]

@Luke_Vancleave

To return a list:

SELECT(
	AnotherTable[ReturnValueColumn],
	[LookupColumn]=[_THISROW].[LookupValueColumn]
)

[Luke_Vancleave]

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?

[LeventK]

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

[Luke_Vancleave]

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.

[LeventK]

@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])

[Luke_Vancleave]

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?

[LeventK · Accepted solution]

Luke_Vancleave:

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?

Correct

Luke_Vancleave:

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?

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
)

[Luke_Vancleave]

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?

[LeventK]

@Luke_Vancleave
That’s correct

[Luke_Vancleave]

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?

[LeventK]

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

[Luke_Vancleave]

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.

I need help with writing an expression for the suggested values for the Actions, based off what location and Item I selected.
I have a column in each of their tables for the Type that they are. I only want the Actions to “Use Quantity” To appear if I’ve selected an Item with the Type: Consumable. And I only want the actions to “Report as broken” to appear if I’ve selected an Item with the Type: Asset. And I want the actions to “Add” and “Subtract” to appear for both because they are General.

Sorry, I hope I’m being clear with what I’m trying to do.