I am working on a CSV report generator for our timecard app. In this CSV, we are compiling data from three separate tables (Employees, DailyTimesheets, and TimePunchSets) that have a creation date within the date range provided (this is typically a one-week span).
DailyTimesheets contains time off data, if an employee had a vacation day during the week. One record represents one day off.
TimePunchSets contain records with a clock-in and clock-out time. Most employees will have two of these records per day.
These two tables are not linked to each other in any way, they only link back to the Employees table via an employeeId. Employees contains records of every employee that has ever worked for us. We can't filter by this however because we need to make sure even inactive employees with punches or time off in the date range are caught in the report (use case: employee is let go halfway through last week).
TLDR:
All I need is a list of unique employee id's that have records in either DailyTimesheets or TimePunchSets with dates that are in the specified date range. From there, my goal is to go employee by employee and pull the relevant records to generate a report.
I am running two separate queries on both DailyTimesheets and TimePunchSets filtering by the date range. The issue I am running into is I can't figure out how to merge both lists together and remove any duplicate employeeId's. I'm new to Xano so it's very possible I just am not setting it up properly. If someone could walk me through how to do this, I'd really appreciate it.
Thanks!