Virtual Column Drop Down List with SQL Data

[JNGIDS]

I have a normalized SQL database connected to the app.

I have three tables, Register, Staff, Location

Staff table has a Foreign Key reference to Location with a column called location_id

The Register Table only uses staff_id

How do I create a drop-down list to show unique locations, and then filter staff for that location?

[JuneCorpuz]

Add the following formulas in Valid If on the location_name and employee_name column

JNGIDS:

How do I create a drop-down list to show unique locations

SELECT( table_name[location_name], TRUE)

JNGIDS:

then filter staff for that location?

SELECT( table_name[employee_name], [location_name] = [_THISROW].[location_name]

[JNGIDS]

Thanks, @June_Corpuz, how do I create the virtual column/dropdown in the Register Table

SELECT( table_name[location_name], TRUE)
This code returns a list, how do I make this be a drop-down list as a virtual column?

[Steve]

JNGIDS:

how do I create the virtual column/dropdown

help.appsheet.com

Search results for: dropdown

JNGIDS:

Should this be a VALID_IF or SHOW_IF in the Register Table?

help.appsheet.com

Valid_If Column Constraint

How to check if a given form input is valid based on its type.

help.appsheet.com

Show_If Column Constraint

Conditionally show or hide a column value.

[JNGIDS]

I have reviewed the documentation, the SELECT statement returns a list, but not a drop-down list.

For a virtual column, it requires app formula, where the select statement is entered which returns the data as expected, but in LIST format, how do I now change this to be a drop down, when I currently do this, the field is greyed out. I want the user to be able to select an option from this selection

[Steve]

JNGIDS:

the SELECT statement returns a list, but not a drop-down list

This statement makes it clear you didn’t understand the documentation. Please review it again.

JNGIDS:

I want the user to be able to select an option from this selection

A virtual column cannot receive input of any type.

help.appsheet.com

Virtual Columns

Check out how you can use virtual columns to add more information to your app.

[JNGIDS]

Steve:

This statement makes it clear you didn’t understand the documentation. Please review it again

Hi Steve, this is where the instructions are a bit unclear

JNGIDS:

how do I now change this to be a drop-down, when I currently do this, the field is greyed out. I want the user to be able to select an option from this selection

This is the part that I am trying to achieve, I am seeing a ! for the drop-down list when I change it to a Ref

[Steve]

Please post a screenshot showing the problem.

[JNGIDS]

removed image

[JNGIDS]

[JNGIDS]

[JNGIDS]

June_Corpuz:

SELECT( table_name[employee_name], [location_name] = [_THISROW].[location_name]

Should this be a VALID_IF or SHOW_IF in the Register Table?

[Steve]

JNGIDS:

How do I create a drop-down list to show unique locations, and then filter staff for that location?

In what non-virtual column of which table do you want the dropdown menu to occur?

[JNGIDS]

Steve:

In what non-virtual column of which table do you want the dropdown menu to occur?

I want the dropdown menu to occur in AttendanceRegister[VC_Dept]

Register Table has the columns staff_id and other columns, staff_id is a REF column and shows a drop-down list as expected. The Staff Table has a FK Reference to the Department table.

[Steve]

JNGIDS:

I want the dropdown menu to occur in AttendanceRegister[VC_Dept]

This will never work, as I noted previously:

Steve:

A virtual column cannot receive input of any type.

[JNGIDS]

Steve:

This will never work, as I noted previously

Oh wow! All of what you are saying is now coming together as I was thinking I was missing a step.

Will I then need to restructure the table design then? or is another way to include columns that have deep links to enforce data integrity?

[Steve]

Without suggesting how you imagine you might accomplish it, describe what experience you want the user to have. Are they filling out a form? Is location an input on the form? You want this list of locations from which the user can choose limited in some way? If so, in what way? Can the user choose multiple locations?

I’m entirely confident we can get you what you want, I just don’t fully understand what you want.

[JNGIDS]

Hey Steve, I greatly appreciate your responses so far. Basically, we had the app using Google Sheets and we migrated the data to Azure and applied greater normalization rules in the SQL database.

Users are entering the register form (date, department, staff, status) the staff column should be visible after the department is entered, the department column should filter the list of staff in that department.

The SQL register table only stores the staff_id in the Register table, given department_id is foreign key in the staff table, so it isnt in the register table. I was hoping to make it work without including the department_id in the Register table, but it seems I may have to restructure the table to include the department_id unless there is a better way to accomplish this in appsheet.

[Steve · Accepted solution]

Yeah, the department_id will need to be captured by the Register table, there’s no way to avoid it if you want the user to be able to choose a department. This is one of AppSheet’s unfortunate shortcomings: there’re no variables, so any value you want to work with must occur in a table, and if you want the user to input the value, the column must be physical, not virtual.

[JNGIDS]

Okay, Thanks much Steve! I hope they will consider doing this in the future, as it will definitely help with normalized databases.