I need advice on how to render an Admin Control Panel that has a two-sheet structure within a single Google spreadsheet. The first sheet has employee names with a key field, and the second sheet contains multiple records that relate back to only one record on the employee table via foreign key. When clicking a row in the employee table, a second query fires, filtering records from the second sheet and loading them into another table. However, this loads slowly. I am wondering if there is a better way to structure the data or use different query methods.
The solution for rendering an Admin Control Panel that has the specified data structure involves using two separate sheets within a single Google spreadsheet named "Certifications and HR". One sheet, called "employees", contains employee names with a key field called employee_id_number, while 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 detailed information in this table includes things such as certificates and certificate expiration dates.
To build this on Appsmith, it's recommended to have a table (called "tbl_Employees") that pulls information from the first sheet called "employees" via a query called "qry_get_Employees". Upon clicking a row in "tbl_Employee", a second query fires 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".
In addition, a third table labeled "Employee Phones" executes a Javascript filtering of qry_get_empDetails, which may slow down the rendering process. Therefore, it's recommended to have two buttons, one for fetching phone numbers and another for fetching certificates. These can also be shown on modals based on the selection made from the table. In this way, all queries need not run at once, improving performance.
Overall, this solution involves restructuring data and using different query methods, such as lists and modals, to improve the user experience.