3 Replies Latest reply on Jan 5, 2011 11:03 AM by philmodjunk

    DB design for resource scheduling

    MartyV

      Title

      DB design for resource scheduling

      Your post

      I am trying to replicate Excel functionality, and am new to FM and NOT a software developer (although I am getting the hang of scripting).

      We use Excel for resource allocation but have pushed Excel to it's limits with lookup fields and named fields that constantly break due to multiple users of the file.

      So far I have just re-created the spreadsheet layout with deliverables/resources in rows and months in columns, we then specify percentage of resource per deliverable per month, then subtotal a range of month columns. However, we like the fill-down and fill-right functions in Excel which allow speedy entry of repeating values. I got some responses from an earlier post about how to create fill down and multi-select capability, but the suggestions sound more complex than just selecting cells and filling (my users are very very basic users and had to be taught how to use excel, so this solution has to be very intuitive).

      I looked at the Resource Scheduling starter solution which yields a weekly report for each resource. I think I might be able to modify this solution to generate a report for all resources and all months of the year, but as I said, I am new to FM and my mind does not work like a software developer....plus, we still want speedy entry of repeating monthly values (both fill down and fill right).

      Any other ideas, or any other suggestions for designing this database to do what I need it to do and keep it simple?

      Thanks!

        • 1. Re: DB design for resource scheduling
          philmodjunk

          Can you given an example of how you currently "fill down" and "fill right" in your spread sheet? It won't be nearly as easy to set this up in FileMaker as it is in Excel. (You'll find you gain some advantages and lose others in the switch from Excel to a database such as FileMaker.) But it can likely be set up to be fairly easy for the user.

          I do suggest that you consider a signficant design change to your database. Simply replicating a spread sheet like layout often gives you the worst of both worlds when you find you can't use the resulting design well as a spreadsheet nor as a database.

          Chances are, you need at least two tables, one for each resource and at least one, likely more for recording the data on a month to month (or even day by day) basis. You put the data needed for your percentages in the records of one table and link them to a specific resource record in the other.

          For data entry, you'll likely find that your spread sheet type table view, with columns for each month, may not be the optimum layout design, but I'm just speculating here based on what FileMaker does best. You may find that your rows of resources with data by month in columns is a layout best generated for reporting purposes after you've entered the raw data for each resource.

          • 2. Re: DB design for resource scheduling
            MartyV

            Current use of fill down/right - I have multiple deliverables on the same timeline. I enter the deliverable attributes and resource one time, then fill down and edit as needed. I enter .5 for the first deliverable, fill down or enter appropriate FTE for remaining deliverables, then fill right for the months.

            So far, I have 4 tables: Resource (FTE names), Deliverables + typical FTE allocation, Schedule - contains all other fields (project name, business unit, etc, and columns for each month. (Question about this design - I was planning to add month columns for 2011 - 2015, but someone mentioned that it could be simpler to add a new record for each month allocation - this does not sound simpler to me. Is it more logical from a design standpoint?) 

            I agree that the spreadsheet view may not be optimum, I have a few ideas about a layout that allows me to define the project and select all associated deliverables, then have the FTEs automatically entered (pointers on how to get the FTEs automatically entered welcome!) But I still need a view that lets me look at all projects assigned to all resources and easily fiddle with FTE allocations to balance.

            Thanks for your help so far. I have also ordered a FM 11 third-party book that will also help me, I hope!

            • 3. Re: DB design for resource scheduling
              philmodjunk

              I enter .5 for the first deliverable, fill down or enter appropriate FTE for remaining deliverables, then fill right for the months.

              The problem here for a FileMaker interface is how to know how far down or how far to the right the data should be "filled". All the way down or just to a specific record? All the way to the right or just to a specific column? You'll need some method of designating the limits of your action or you might redesign your database so that such multiple inputs are no longer needed. Whether or how you would do that might require sitting down with a consultant that knows databases to talk through your current business model to make sure your data model supports it.

              One method is to load your data into some global fields and use a script to make your multiple entries to a group of records you specify. Another method, is to only enter the data once and then link all relevant records to that data so that you don't have to make multiple entries--this is one of the methods inherent to a good relational database design.

              I was planning to add month columns for 2011 - 2015, but someone mentioned that it could be simpler to add a new record for each month allocation

              That "someone" could have been me, though any experieced database developer would likely make the same suggestion. It may seem simpler when you first set up your layout, but trust me, it's not simple at all. Adding columns will require adding a new field or new field repetition for each new column. Once you've defined your new field or repetition, you then have to create or update a layout in order to use it. Scripts and calculations may also have to be updated to correctly reference the data in these new fields...

              If you set up a table so that each new "column" is instead a new record, you don't have to do any of this. The same layouts work, you just pull up a different subset of the total records to show the dates you need. Adding a new one is as simple as selecting New Record from the Records menu or pressing the keyboard short cut for it.

              The part you'll have to think about is that new records easily lend themselves to rows of data instead of columns. There are ways to use multiple portals for this, but it's not a beginner technique. If you can work with a layout where you have a resource record listed and the relevant "month" records for the given resource are listed in a portal (which can be filtered to display different date ranges), life gets much simpler for you.