1 2 Previous Next 18 Replies Latest reply on May 29, 2012 12:57 AM by tnfink

    How to model a staffing problem?

    tnfink

      I am new to Filemaker but have some experience with relational database systems. I investigate to use Filemaker for a small application to manage the staffing of projects. But I am still not sure what the best approach is.

       

      I use a simple model for evaluation. I have two tables

       

      Project (id:Number, name:Text)

      Employee (id:Number, name:Text)

       

      and a relation table:

       

      Staffing (project:Number, employee:Number, week:Number, persondays:Number)

      .

       

      Now I would like to have two yearly overview reports, one for the projects one for the employees. The one for the projects should be something like this

       

      NAME KW-1 KW-2 KW-3 ...... KW-52

      project1 12 11 5 ....... 0

      project2 0 0 4 ........ 4

      ...

       

      For each project the sum of all person days of all staffed employes per week in a year is displayed.

       

      For me, the problem is here, that the columns of the report are not the columns in any table. This seems to be hard to express in Filemaker.

       

      My current sollution is to add 52 calculated fields to the project table. But, this does no feel like a maintainable solution.

       

      Any other idea or comment would be appreciated.

        • 1. Re: How to model a staffing problem?
          Malcolm

          My current sollution is to add 52 calculated fields to the project table. But, this does no feel like a maintainable solution.

           

           

          You could create a single calculation field with 52 repetitions.

           

          malcolm

          • 2. Re: How to model a staffing problem?
            tnfink

            Thanks, that makes the database design better readable.

             

            Additionally I came up with a solution that did not use a calculation field but a script to calculate the numbers. I think that approach is ok. It would have been nice to avoid the script, bit it is not that complicated.

             

            In my standard environment (some RDBMS + Java) I would also have to implement a small procedure for the overview report. Nevertheless, the FileMaker script language is pretty ugly and pretty hard to debug.

            • 3. Re: How to model a staffing problem?
              Malcolm

              Thanks, that makes the database design better readable.

               

               

              Additionally I came up with a solution that did not use a calculation field but a script to calculate the numbers. I think that approach is ok. It would have been nice to avoid the script, bit it is not that complicated.

               

               

              In my standard environment (some RDBMS + Java) I would also have to implement a small procedure for the overview report. Nevertheless, the FileMaker script language is pretty ugly and pretty hard to debug.

               

              You can do quite a lot with it. It isn't a full scripting language but you can call other programs for help on both Windows and Mac. 

               

              So long as your data is well structured the issue is, how do I present it. You're running into problems because you want cross-tab reports. Filemaker doesn't do cross-tab reporting out of the box. It does do sub-summary reporting out of the box.

               

              Often we are asked for cross-tab reports because that is the Excel mind set. When we show the client the power of subsummary reports they prefer them. In filemaker re-sorting a subsummary report is like pivoting an excel table. All the same data, just re-arranged.

               

              A subsummary report can have many breakpoints, eg, Year, month, week, project, branch, unit, staff. Each sort will rearrange those so that one report produces information on any of the items. 

               

              malcolm

              • 4. Re: How to model a staffing problem?
                tnfink

                Don't get me wrong. I actually like FileMaker. A lot of stuff is quite simple to do.

                 

                I tried subsummaries. My main problem was that I do not want a vertical list of every week, which is not readable. A subsummary with a horicontal list would have been fine too.

                 

                I had two concrete problems with the scripting language. First, there seems to be no container data type. I had to use a kind-of-list made out of a string using a newline separator to separate the entities. Second, I had to iterate over the weeks. Something as "for i = 1 .. 52 { ... }" would have been fine. But instead I had to:

                  i=1

                  Start-Loop

                  if i>52 break-Loop

                  ...

                  End-Loop

                 

                That reminds me of the good old BASIC-days of my youth.

                :-)

                • 5. Re: How to model a staffing problem?
                  Malcolm


                  I tried subsummaries. My main problem was that I do not want a vertical list of every week, which is not readable. A subsummary with a horicontal list would have been fine too.

                   

                  Then you need to create a few structures to shape the data. Think of it as a trellis. Repeating fields work well when you need to display data horizontally.

                   

                   

                  I had two concrete problems with the scripting language. First, there seems to be no container data type.

                   

                   

                  Is that a different type of container to the Container Field and the calculations that allows you to generate a container type?

                   

                  I had to use a kind-of-list made out of a string using a newline separator to separate the entities.

                   

                  That's the default method, carriage return separated lists are Filemaker's standard array format.

                   

                   

                  Second, I had to iterate over the weeks. Something as "for i = 1 .. 52 { ... }" would have been fine. But instead I had to:

                   

                    i=1

                    Start-Loop

                    if i>52 break-Loop

                    ...

                    End-Loop

                   

                  That reminds me of the good old BASIC-days of my youth.

                  :-)

                   

                  Well, that can't be bad can it?

                  • 6. Re: How to model a staffing problem?
                    comment

                    tnfink wrote:

                     

                    I do not want a vertical list of every week, which is not readable. A subsummary with a horicontal list would have been fine too.

                     

                    If you are using version 11, have a look at:

                    http://fmforums.com/forum/topic/71836-getting-more-out-of-filtered-portals-in-version-11/

                     

                    Note that what you describe is a display issue; it should not influence your data model.

                    • 7. Re: How to model a staffing problem?
                      danshockley

                      One trick to use fewer script steps for the "loop using incrementing variable" is this:

                       

                      Loop

                      Exit Loop If [  Let( $i = $i + 1; $i > 52 )

                      ## Do Stuff here with your loop variable, $i

                      End Loop

                       

                      When you consider that the "Exit Loop If" step is acting like the "for ( i=1;i<52;i++)" construct in other languages, and the Loop/End Loop are acting like the curly brackets for the "loop code," this is the same as what you're asking for.

                       

                      Just one caveat: since this does not explicitly set $i to 0 or 1 at the beginning, you need to be sure you haven't used it earlier in the script. For example, if this is a sub-loop in a nest loop. Other than that, you're all set.

                      Also, since $i is somewhat hard to read, you might wnat to use $loopIndex or something like that instead.

                      • 8. Re: How to model a staffing problem?
                        Malcolm

                        One trick to use fewer script steps for the "loop using incrementing variable" is this:

                         

                         

                        Loop

                         

                        Exit Loop If [  Let( $i = $i + 1; $i > 52 )

                         

                          1. Do Stuff here with your loop variable, $i

                         

                        End Loop

                         

                         

                        Well done Dan! That's clear and concise.

                         

                        malcolm

                        • 9. Re: How to model a staffing problem?
                          tnfink

                          sorry for this late response (first busy then holiday).

                           

                          Thank you for this hint!

                          It improves the loop a little bit.

                           

                          .. it still feels pretty old

                          • 10. Re: How to model a staffing problem?
                            cortical

                            try a weekofyear table intermediate and 52 ( single row) portals using the same rel

                            the REL sorted WOY

                            and the iterations using

                            initial row 1 number of rows 1

                            initial row 2 number of rows 1

                            initial row 3 number of rows 1

                            etc

                            • 11. Re: How to model a staffing problem?
                              bumper

                              The FileMaker Training Series in module 7 (at least it's 7 in the 11 book) has a lesson for creating a crosstab report, which is what it sounds like you are trying to achieve. It's very simple but effective, and more advanced versions can be built from there.

                              • 12. Re: How to model a staffing problem?
                                tnfink

                                thanx for the hint.

                                 

                                I experimented with this approach, before I startet scripting. The disadvantage is to maintain 53 (sometimes there are actually 53 weeks in a year) portals. I have to use these 53-data-sets also in other contexts which would make it even harder to maintain.

                                 

                                Actually, I like the repeated field approach. It introduces arrays to a relational data base.

                                 

                                If it would be possible to aggregate element-wise the repititions of related elements, I could reduce the scripting significantly.

                                • 13. Re: How to model a staffing problem?
                                  tnfink

                                  Malcolm schrieb:

                                   

                                  I had two concrete problems with the scripting language. First, there seems to be no container data type.

                                   

                                   

                                  Is that a different type of container to the Container Field and the calculations that allows you to generate a container type?

                                   

                                  I had to use a kind-of-list made out of a string using a newline separator to separate the entities.

                                   

                                  That's the default method, carriage return separated lists are Filemaker's standard array format.

                                   

                                  sorry, the term "container type" is ambiguous. I meant concepts such as lists or maps. The newline-separated string feels pretty old-stylish. I would guess that it is also pretty expensive to access a list element by its index.

                                  1 2 Previous Next