Conditional Dropdowns: Or How I Learned to Stop Worrying and Love Table-to-Table Relationships.

You may have thousands of employees, customers, vehicles, tasks, invoices, leads, or whatever it is you track on a daily basis. Sometimes you want to attach a new piece of data to one of these thousands of records. How can Quickbase make that process as painless as possible? How can we make selecting that one record simple and manageable? We want our Quickbase solution to make life easier for our users.

Our client is a global department store brand with thousands of employees all over the world and their Quickbase solution is a one-stop shop for all of their needs. So, how can we give their central office the ability to enter a new time sheet for a single employee quickly and accurately?

Our app currently has four tables: Store Locations, Departments, Employees, and Timesheets.

The relationship diagram looks like this right now…

Each location has many departments. Each department has many employees. And each employee has many timesheets. Let’s see what happens when we try to enter a timesheet. From the Timesheet table, click “New Timesheet”.

Now click the dropdown for Employee Name.

Sheesh! Look at all those employees! We’ve scrolled all the way to the bottom of our alphabetical list and we’re only in the B's! We could click “Browse Employees”, but then we’d have to sift through a thousand records to find the right one. If we remember that each employee is associated with just one location and one department, we know we can simplify this process by allowing our user to narrow the employee choices based on Store location and Department.

The first step is to create a couple additional table-to-table relationships. We will need to create a one-to-many relationship between the Store Locations and Timesheets tables. We will then create a one-to-many relationship between the Departments and the Timesheets tables.

From the Timesheets table, click on the Settings icon. (Circled in red below)

Click on “Table-to-table relationships”

Click “+ New Relationship”.

From the dropdown, select “Store Locations”. Each Store Location has many Timesheets. Select this option and click Next.

Defaults are good here. Click Next.

Here we add a lookup field. Make sure that Lookup 1 is “Store Locations – Store Location” and click Create Relationship.

We can now repeat this process and build a relationship between the Timesheets table and the Departments table. Try it! Remember that each Department has many Timesheets, our reference field should be “Related Department”, and our Lookup 1 field should be “Departments – Department”.

Once we’ve created these two additional relationships, our diagram should look like this…

Now let’s create a new Timesheet. By default, our new lookup fields have been added to the form. Let’s re-position them and apply the parameters that will allow us to narrow our selection of employees by Location and Department. Click “Customize this Form.”

Let’s move Store Location and Department to the top of the form. Highlight both using the shift key and click the up button until the Form Element list looks like this.

Click save.

Right click next to Employee Name and select “Edit the field properties for this field.” Under the section “Reference field options”, tick the box next to “Conditional values”. This makes the value in the Employees field dependent on a selection in another field. We want it to depend on the selection in the Department field of the Timesheets table. Select “Timesheets: Department.”

We want to show choices for the Employee where “Timesheets: Department” = “Employees: Department.” Select it from the dropdown menu.

We can now test what we’ve done. Go to the timesheets table and select “New Timesheet.” Notice that we can’t enter an Employee name until we enter the Department. Select any Department from the dropdown menu. Now click on the Employee dropdown, you will only see Employees from that department.

Record ID? Related Store Location ID? Our end users don’t need to see this. Let’s clean that up. Click “Customize this form.”

Under the Elements tab Highlight “Department”. For the “When used for data entry” dropdown, select “Department” instead of the default record picker.

Now click save and check out that New Timesheet form. Click on the Department dropdown. Now we just see the name of the department, no record IDs.

Now, when we select the proper department, the employee dropdown menu will only include those employees who work in that department. However, we are still having to choose from many departments all around the world. We can narrow this down even further.

Let’s make the selection in Departments dependent on the selection in Store Location. Right click next to Departments and select “Edit field properties for this field”. Under Reference field options, next to “Conditional values”, tick the box to make the value in this field dependent. Set the dropdowns so that a selection in “Timesheets: Store Location” shows choices where Timesheets: Store Location = “Departments: Store Location”

Now, let’s clean up that Store Location dropdown on our New Timesheet form. Click “Customize this form”. Make sure Store Location is highlighted and set the “When used for data entry” dropdown to “Store Location” instead of the default record picker.

Let’s look at our results. Now, on the New Timesheet form, we can’t edit the Department field dropdown or the Employee Name dropdown until we’ve selected a Store Location.

Select any location you like.

Now that we have selected a Store Location, the Department dropdown is no longer grayed out. We can now use it to select a Department.

Go ahead and select a Department.

We now only have choices for Employees that work in that department at that store location.

Because of our conditional dropdowns, instead of thousands of employees to choose from, we now have a very manageable number of eight. Our HR Manager couldn’t be happier! By saving time and simplifying workflow, Quickbase once again shows why it is one of the most powerful and user-friendly relational databases available.

200 views0 comments