I have several tables for different types of engagements:
Attendance
'Dress Code'
'Reasonable Suspicion'
'General Feedback'
etc.
Every engagement table has a lookup column called 'Associate ID'.
These lookup columns all point to the 'Associate Info' table with the Primary Column of 'Associate ID'.
I have an Associates gallery and I want it to populate in the following order:
Associates with zero engagements first,
Associates with the oldest engagements next,
Associates with the most recent engagements last.
To do that, I want to use the 'Created on' system column.
On my HomeScreen in the OnVisible property I've set up something like this:
ClearCollect (AllEngagements, Attendance);
Collect (AllEngagements, 'Dress Code');
Collect (AllEngagements, 'Reasonable Suspicion');
Collect (AllEngagements, 'General Feedback');
And that's as far as I'm able to get. Copilot isn't any help, and I've tried using ChatGPT, but any solution I find stumbles over the fact that the Associate ID field is a lookup to the Associate Info field and I just can't figure out what I should do next.
The spoken idea is this:
To populate the Associates Gallery,
Find all records in AllEngagements that match the Associate ID in the Associate Infos gallery.
Determine which record is the most recent.
Extract the Created On date from that determination
Populate the Associate Gallery in the following order:
Associates with No engagement records - Populate first
Associates with the Oldest engagement records - Next
Associates with the Newest engagement records - Last
Finally, in the Associate Gallery, those with no engagements will have a light blue border, those with the most recent engagement between 0 and 7 days old will have a green border, those with the most recent engagement between 8 and 14 days old will have an orange border, and those associates with the most recent engagement of 15 days or more will have a red border.
The whole thing is a part of our new Engagement initiative to ensure that all of our associates are being engaged at least once every 2 weeks as a part of an hr/culture initiative.
