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?
Solved! Go to 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.
Add the following formulas in Valid If on the location_name and employee_name column
SELECT( table_name[location_name], TRUE)
SELECT( table_name[employee_name], [location_name] = [_THISROW].[location_name]
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?
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
This statement makes it clear you didnโt understand the documentation. Please review it again.
A virtual column cannot receive input of any type.
Hi Steve, this is where the instructions are a bit unclear
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
Please post a screenshot showing the problem.
removed image
Should this be a VALID_IF or SHOW_IF in the Register Table?
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.
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?
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.
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.
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.
Okay, Thanks much Steve! I hope they will consider doing this in the future, as it will definitely help with normalized databases.
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
17 |