Enumlist, Select and Dynamic Dropdown

[anonymous]

New to AppSheet and building out a Directory with employees and clients. Running into an issue when matching employees to clients based on specific criteria which includes data from a Enumlist.

I need to match the client to an employee based on input values from [State], [Service], [Type]. What I’m trying to do is once those fields have been filled out, it will populate an enum of employees that match all input values.

Below is a sample of tables I am using. For the Employee Table, [Team] represents the Client Table[Service] they cover, [Service Area] represents the Client Table[State] they cover, [Location] represents employee’s residing state. If a Client Table [Type] is “Onsite”, then only an employee who resides in that state can be matched. However if Client Table [Type] is “Virtual”, [Location] does not apply and any employee who has the Client Table[State] under their [Service Area] can be matched.

So an example: I’ve filled out the form for ABC Store is in “Georgia”, Service is “HR”, and they need someone “Onsite”. The employees that would be available to match and include in the list to select from would be Andrew, Ann, Charles. (Barbara or Chelsea are not included because their [Location] is outside of Georgia.

Second example: XYZ Store is in “Texas”, Service is “IT” and Type is “Virtual”. Employees that match and populated into the list to choose from are Aaron, Alan, and Brian. (Alan and Brian are included because their [Location] does not apply since Client Table[Type] is "Virtual’.

Any Suggestions or Help would be greatly appreciated!

Employee Table

ID	Employee	Team	Service Area	Location
EM0001	Aaron	IT	Missouri , Texas , Georgia	Texas
EM0002	Alan	IT	California , Minnesota , New York , Texas, Wisconsin	Wisconsin
EM0003	Amanda	Sales	Texas, Georgia	Texas
EM0004	Andrea	Sales	Maine , New York , Pennsylvania , Texas	Pennsylvania
EM0005	Andrew	HR	Georgia , South Carolina	Georgia
EM0006	Ann	HR	Georgia, Alabama, Tennessee	Georgia
EM0007	Barbara	HR	Colorado , Georgia , Idaho , Illinois , Indiana , Kentucky , Maryland , Michigan , Montana , Nebraska , New Hampshire , Oklahoma	Colorado
EM0008	Brian	IT	Idaho , Illinois, Texas	Idaho
EM0009	Charles	HR	Alabama, Florida, Georgia, Indiana , Michigan , Montana , Nebraska	Georgia
EM0010	Chelsea	HR	California , Georgia, Texas, New York	California

Client Table

ID	Client	Employee	Service	Type	State
CL00001	ABC Store		HR	Onsite	Georgia
CL00002	XYZ Store		IT	Virtual	Texas

[Suvrutt_Gurjar]

Hi @barnabre ,

I see that this is your first post in the community. Welcome to the AppSheet community. I appreciate that your post is appropriately detailed with all the right information in place. I think it could be regarded as a reference to “how a query should be posted.” Well done.

If understanding of your requirement is clear, you could try the following expression in the valid_if of the [Employee] column of the Client Table

SELECT(Employee Table[Employee],

AND( [Service]=[_THISROW].[Service] ,

IFS([_THISROW].[Type]=“Onsite”, [Location]=[_THISROW].[State],

[_THISROW].[Type]=“Virtual”, IN([_THISROW].[State], [Service Area]))

)

)

If you are referencing the Employee Table in the Client table’s Employee column , then the valid_if in the [Employee] column of Client table can be as follows ( assuming [ID] is the key of the Employee table.)

SELECT(Employee Table[ID],

AND( [Service]=[_THISROW].[Service] ,

IFS([_THISROW].[Type]=“Onsite”, [Location]=[_THISROW].[State],

[_THISROW].[Type]=“Virtual”, IN([_THISROW].[State], [Service Area]))

)

)

Edit: Made correction to the expressions- added a missing parenthesis.

[anonymous]

Thank you for the quick response. I appreciate the help. So the SELECT(Employee Table[Employee], sort of worked. ID is just a UNIQUE value. For some reason it will show the available “Employees” for preexisting data when only table Updates are selected, but disappears when I select Adds allowed. Should I be putting this into the column “Formula” field for the [Employee] row under Client Table or somewhere else? And should the type be Text or Ref, List?

Maybe this will better explain my questions. When adding a new client to the App, I want it create a list of just the employees that match and I can select one of them for the list to be the value.

Here are the steps on the app:

From the Client Table View, I select Add New button. The Client Table_Form View open up. I fill out the form in the following sequence: 1. Type in [Client] text field, 2. I select value for [State] from Enum. 3. I select value for [Service] from Enum. I select value for [Type] from Enum.

What I would like to happen next is to select a value for [Employee] from an Enum that only includes a list of the Employee Table [Employee] data that match the values from Client Table [State] [Service] [Type].

Example based on the tables I previously provided.

I Add New to Client Table.

Client

RST Store

State

Georgia (selected from Enum = list of all 50 United States)

Service

HR (selected from Enum = IT or HR or Sales)

Type

Onsite (select from Enum = Onsite or Virtual)

Employee (will show list of employee who match all values from [State] [Service] [Type]) (If [Type] = Virtual, do not include as value to match. Only match based on [State] [Service] values)

Select One

Andrew

Ann

Charles

(If “Virtual” was selected for [Type], then the Enum would of included Barbara and Chelsea)

So now when I got to the Client Table_Detail View it will show the following for this Client;

ID (Unique ID)

Client RST Store

State Georgia

Service HR

Type Onsite

Employee Ann (employee that was selected.)

Hope this provides more clarification

[Suvrutt_Gurjar]

Thank you. I think all is in place.

You may want to make the field [Employee] in the Client table as ref type that references the Employee table. Then in the valid_if constraint of the [Employee] column, you can use the suggested expression of

SELECT(Employee Table[ID],

AND( [Service]=[_THISROW].[Service] ,

IFS([_THISROW].[Type]=“Onsite”, [Location]=[_THISROW].[State],

[_THISROW].[Type]=“Virtual”, IN([_THISROW].[State], [Service Area]))

)

)

Please ensure in the “Employee” table, [ID] is the key and [Employee] is the label.

You may need to put in another constraint to avoid duplicate booking of same employee during the same time period on two jobs, but that will depend on your other app configuration.

@barnabre

Updates are selected, but disappears when I select Adds allowed.

Please elaborate this more-for which table and where you see this?

Please refer the below articles for more context

Check form input validity (Valid_If) - AppSheet Help

The “lookup/drop-down” data pattern - AppSheet Help

References between tables - AppSheet Help

Add row labels - AppSheet Help

[anonymous]

When apply that formula. It gives me the following result.

“Parameter 2 of function IN is of the wrong type”