Optimization of Chained Queries (running on Google Sheets)

Hello, I am looking for advice and best practices for rendering an Admin Control Panel that has the following data structure:

Two separate sheets within a single google spreadsheet wherein…

  • the first sheet (called “employees”) has employee names with a key field called employee_id_number, and
  • the second sheet (called “empDetails”) contains multiple records that relate back to only one record on the employee table via the foreign key called foreign_employee_id… the type of detailed information in this table are things such as certificates and certificate expiration dates.

My admin control panel has one table (called “tbl_Employees”) that pulls information from the first sheet called “employees” via a query (“qry_get_Employees”). Upon clicking a row in “tbl_Employee”, a second query fires (“qry_get_empDetails”), filtering records from sheet “empDetails” where foreign_employee_id == tbl_Employees.selectedRow.employee_id_number . The results of that query are loaded into another table called “tbl_empDetails”. Is there a better way to do this?

Additionally, I have one other issue that is slowing things down. I have a third table labeled “Employee Phones” that executes a javascript filtering of qry_get_empDetails as follows:

{{qry_get_empDetails.data.filter(rec => (rec.recType=="phone"))}}

This is done so that I can have a separate table that formats all of the different phone numbers in a visually pleasing manner… but again it loads pretty slowly.

Should I restructure my data or use different query methods? Thoughts?

Appsmith is pulling all of its data from a single google spreadsheet called “Certifications and HR”. That spreadsheet has the following sheets:

  1. One sheet (called “employees”) has these fields:

    • employee_id_number (the key field),
    • employee name,
    • employment status
    • employee photo (url)
  2. another sheet called “empDetails” has these fields:

    • foreign_employee_id (as a foreign key),
    • detailType with values such as…
      • “date of birth”
      • “H2O Certification”
      • “emergency contact”
      • “home address”
      • “main phone”
      • “backup phone 1”, “backup phone 2”, etc)
    • detailValue, which has values such as…
      • “1990-01-01”
      • “M&A Safety - passed- 2020”
      • John Doe - Brother; 337-123-1234
      • 123 Big Street, Lafayette, LA
      • 337-123-1235, etc

Hey @OSAcadianajobs, firstly, welcome to the Appsmith community.

I like how you’ve explained your application idea; it’s well thought out and structured.

I’ll share some of my thoughts on how I would build this on Appsmith:

I think this will work, you can write a function that filters employees and fetches their certificates, and display them on a widget. And this can be called whenever a table row is selected.

When you select an employee on the tbl_Employees, will you be redirecting him to a different page for showing certificates? or show them on the same page? If it’s on the same page, maybe you can try out lists to make the user experience better.

I would have two buttons, one for fetching your phone numbers and another for fetching your certificates. You can also show these on Modals based on the selection you make from the table. In this way, you need not run all the queries at once (just a suggestion :slight_smile: .)

Feel free to reach out if you have any queries about building the app.

1 Like