Hi
I have created an app to display a roster, by 'Location', which allows a user to click on a 'Position' and make updates (add new employee to a 'Position', remove an employee to a 'Position', and update a current employee at a 'Position'). I am using a SQL table (roughly 1,000 records) connection to record updates. The app works but it is extremely slow on start and to load after a user makes any updates.
I am using a Gallery, which essentially just looks like a grid that looks like this:
Location | Manager | Co-Manager 1 | Co-Manager 2 | Developer | ...
The SQL table supporting this can have multiple records for the same 'Position'/'Location', and I am looking for the most recent "Approved" record. For example, if the table includes 3 records for the Manager position in New York City, it would look like:
Location | Position | Employee_Name | Action | Status | Created_Date
1) 'New York City','Manager','Dave','Create','Approved','7/23/23'
2) 'New York City','Manager','Dave','Delete','Approved','7/24/23'
3) 'New York City','Manager','Steven','Create','Approved','7/25/23'
So, in my gallery for the Manager Position in New York City I would want to display 'Steven' since it is the most recent "Approved" record.
I used this formula to get the list of unique 'Locations' from my SQL table:
Sort(AddColumns(GroupBy(Sort(Filter(SQL_Table,Division="D2D"),Created_Date,SortOrder.Descending),"Location_ID","LocationID"),"Unique_Location",First(Location_ID).Location),Unique_Location,SortOrder.Ascending)
Then, I used this formula to get the most recent employee at each 'Position' for each 'Location' (blank if most recent is 'Action' = "Deleted"):
If(LookUp(SortByColumns(SQL_Table,"Created_Date",SortOrder.Descending),Location_ID = ThisItem.Location_ID && Position = "Manager" && Status="Approved",Action)="Delete","",LookUp(SortByColumns(SQL_Table,"Created_Date",SortOrder.Descending),Location_ID = ThisItem.Location_ID && Position = "Manager" && Status="Approved",Employee_Name))
This formula is repeated 12 times (once for each 'Position' at a 'Location'), for every row/record in the Gallery. There are about 150 unique locations so I'm assuming this is a very inefficient way to handle this.
Is there a better formula I could be using on these employee LookUps? Is there a way to load everything in once and then only run those LookUps again if that specific 'Position'/'Location' gets an update? Could I maybe use Collections to collect this "grid" and then just reference that Collection? Looking for any idea that can help this run significantly quicker.
Thanks
