7 Replies Latest reply on Dec 12, 2016 11:50 AM by philmodjunk

    Create a Staff Roster

    thehalpeen

      I use an Excel spreadsheet to create rosters - see below

      Roster Excel.PNG

       

      I've attempted to create the Roster with Filemaker with the following Tables

      • People/Staff
      • Shifts
      • Join table (People and Shifts)

       

      I've created a list with a summary showing the shifts - the table is based on the Join table. But the table looks untidy - it is stepped like a stairs  - see below.. I'd like it to appear as in the Excel version above.

      Roster Filemaker.PNG

      The Join Table

      Join Table.PNG

       

      I've created a 'hard code' HTML table and pasted in the Web Viewer to represent a Roster - see below.

      Roster Html table and code.PNG

      My Question.

      I'd like my Roster to appear as in excel. I reckon there must be a way of binding the data in the Roster Join Table to the Web Viewer and using either HTML and/or Javascript to create a nice tidy roster.

        • 1. Re: Create a Staff Roster
          beverly

          you are looking for Pivot Table/Cross tab(ulation). This is not native to FileMaker without some fancy footwork. You might search this forum first for possibilities.

          beverly

          • 2. Re: Create a Staff Roster
            David Moyer

            Hi,

            now that you know it's not native to FM (displaying your data that way), you can focus on how to display the data in your well-formed database.

            There are many ways to go about it.  You could stick to your HTML approach and script it to be dynamic, based on your data.  That might be fun, actually.  I would probably create a grid of dummy worker repeating fields populated by a script.  Others will doubtless suggest other ways.

            • 3. Re: Create a Staff Roster
              philmodjunk

              Set up a list view layout based on your staff table. Add a row of one row portals to your join tables. Use portal filtering and global fields to control which records appear in a given column of these portals.

              • 4. Re: Create a Staff Roster
                thehalpeen

                Philmodjunk,

                 

                I've done the following based on what I think is your advice

                :

                I created a new blank layout based on People table.

                In the body of that table I've inserted eight portals - each portal is based on the Join table called "People_Shifts". The eight portals are:

                • Employee name
                • Seven portals - one for each day of the week - Day 1 to 7

                 

                I've filtered each daily portal as follows:

                If (People_Shifts::gStartDate plus 1=People_Shifts::Shift_Date ;

                 

                People_Shifts::Start_End_People_Shift;

                 

                "")

                 

                Translated: If the Global Start is (say) Monday, and the shift date is Monday - then show the field called "Start_End_People_Shift" - otherwise "blank".

                 

                With one row, you only see one shift. If a person has two shift or even three shifts in a particular day, then you have to change the portal rows to 2 or 3 - so its not dynamic.

                 

                Or have I totally misunderstood you?

                 

                See result below - which is decent enough - but I am showing 2 rows per Daily portal. Had you something else in mind?

                Roster portals.PNG

                • 5. Re: Create a Staff Roster
                  thehalpeen

                  David,

                   

                  That's the problem - How or what kind of script is needed to make it dynamic?

                  • 6. Re: Create a Staff Roster
                    thehalpeen

                    Beverly,

                     

                    My original solution is a 'sort-of' cross-tab solution, in that the days are in columns. But it's not yielding the results. Philmodjunk has made a suggestion and I have created a decent enough looking roster - but its not dynamic.

                    • 7. Re: Create a Staff Roster
                      philmodjunk

                      I had assumed that a given person would only have one shift per given day, but to allow for more simply requires using more portal rows as you have already discovered.

                      1 of 1 people found this helpful