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:
-
One sheet (called “employees”) has these fields:
- employee_id_number (the key field),
- employee name,
- employment status
- employee photo (url)
-
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
.)