How to create a field that displays all gigs worked within a range of dates?
I have the following tables Event Details, Staff, and a join called Event_Staff_Join.
My payroll person would like to pull a list of staff that have worked any event between a date range. She would like a custom table view where she can see the following columns: first name, last name, event numbers - in a FX field but separated by commas, and a total # of events worked.
How would I accomplish the list of event numbers in a formula field?
The end goal is so she can see all the people that need to be paid for date range and how many gigs they worked.
7 replies
-
You may want to think about a dashboard. I have mocked up what it could look like in the attached DB.
If you open the dashboard table you will see what it looks like. You can change the start date and end date and see how the number of staff changes.
Things are a bit more complicated cause you want a list of staff, but need to look at date fields in Event Detail. Since there is a join table there is not a simple 1 to many relationship between staff name and Event date. You can look at the staff table and see what Ninox see if you try to go through the relationships. Since one name is linked to many events you can't just do a simple search of date fields.
Anyways, I hope this helps.
-
Thanks for that answer FRED I appreciate it. I use the cloud version of Ninox so i don't have a great way to open the file you sent over.
-
you can download the file, unzip it, then in the browser click on import archive and then select the file you downloaded.
-
Made a mistake in the first DB. This new one fixes the issue.
The first one was using the Staff table which brought up all events the staff that was found worked at, even ones outside of the date range set.
So the view now uses the event_staff_join table so we only bring up the staff and events that fall within the range.
Don't know why I didn't catch that problem earlier.
-
Rafael said:
In Dasboard table I place one tab with the name table is a table in HTML look very good but don't know integrates the event_staff_join to see the staff Name, is possible ?I haven't worked much in HTML so I don't know, right now, how to evaluate your code. Maybe someone with more experience can jump in.
Content aside
- 2 yrs agoLast active
- 7Replies
- 135Views
-
3
Following