I am trying to create a dynamic staff roster that will compare favourably with other non-Filemaker solutions/apps I’ve found on the internet.
It seems nowadays that any self-respecting app/solution should have a cross tab report.
Below is what I'm trying to achieve in the Web Viewer: I have hard-coded all the html code.
I’m aware that cross-tab reports can be complex using Filemaker. With the help of these forums, I have successfully created a cross-tab ‘staff roster’ using the People/Staff table and some filtered portals. However this report is limited to the number of portal rows I assign.
I would like the roster to be flexible so that it would expand and contract depending on the number of rows of data (shifts per person). This is not possible with the portal method.
However, I’ve seen cross-tab reports done via the Web-Viewer, using variables, scripts, lists, and loops to build up a html table that includes table rows and table data.
I’m trying to create a report that will show weekdays (Monday to Sunday) for a selected week – say Week 10 - across the top (X-axis) and the person/staff names down the side (Y-axis), with the relevant data in the middle.
I have the following tables:
- Shifts – there are 8 different shifts, ie 7am-11am, 11am-2pm, etc.
- Staff/Shift (Join Table) this list all the data I’m trying to display in the Cross Tab table.
With information from the web and from some Filemaker Discussions, I have managed to create the x-axis showing the dates. To do this I needed to create a list/array of the seven dates and parse this list into html table row and table data.
I’ve search the web on this topic and I’ve come across some techniques, but they all seem to involve summaries of data – there would be one row per item. They involved three scripts – one for the x-axis, one for the y-axis and one for the cross-tab data. My situation could have many rows for the one item (Staff member) on the y-axis.
I have a created the report manually in the web Viewer (see image above) to show what I am trying to achieve, but I need help/advice/direction building the dynamic script to produce the Cross-Tab report. I’m aware I will need lists, counters, various loops, variables – so it’s not a normal request. I can create the first row, but I get lost, if I have to insert a 2nd row of data for the same staff member. I’m finding it difficult to determine the loops required to assimilate the cross-tab table.
Any help or direction would be very much appreciated.