3 Replies Latest reply on Sep 10, 2013 11:03 AM by philmodjunk

    Milestones on Gantt Chart

    deathrobot
    deathrobot Sep 8, 2013 10:28 PM

      Title

      Milestones on Gantt Chart

      Post

           I have a Gantt chart to show project timelines. It uses the method shown here:

           http://coresolutions.ca/update-gantt-charts-in-filemaker#codesyntax_4

           Each line is a repeating field with conditional formatting (see the screen shots), based off of one global field (STARTDATE), and is based on a table called "Projects". This is all working well. However, each project can have one or more milestones (from a child table called "Milestones"). Each Milestone has a date, a type (i.e. "call", "demo due", "final due", and "other"), and a field for more details. What I'm trying to do is overlay another repeating field to show milestones so that for each related milestone, if the date in the particular gantt square matches the milestone date, show a letter or icon depending on what the type is (e.g. "C" for "call", "D" for "demo due", etc.). The formula below works for just the first child, but I'm not sure if this can be modified to include all the children, or I need something different. And, it does not look at the milestone type:

           Let ( [
                currentDate = Extend ( STARTDATE ) + Get (CalculationRepetitionNumber ) - 1;
           ];
                Case ( currentDate = Extend ( Milestones » Projects::milestoneDate );
                     "•"
                )
           )

           Basically, I want to see (where "-" are blank squares):

           - - - - - C - - - - - D - - F - - - - - - - - - -

           Is this possible?

           Thanks,
           Michael

      Gantt.png

      • 335 Views
      • Tags:
        • 1. Re: Milestones on Gantt Chart
          philmodjunk
          philmodjunk Sep 9, 2013 8:49 AM (in response to deathrobot)

               I think you'll need to not use a repeating field for the milestones. Instead, try overlaying the repeating field with one row portals to the related data.

          • 2. Re: Milestones on Gantt Chart
            deathrobot
            deathrobot Sep 9, 2013 10:00 PM (in response to deathrobot)

                 Hi Phil,

                 Thanks for the suggestion. How would the portals correspond to the date in each gantt square, and update whenever there was a new STARTDATE? I di manage to get the chart working by scripting nested loops - go through each found project record, then loop through all the related record, and set a repeating global text field. Works fine, but is on the slow side; takes about 2 seconds with just 30 records (each with 1 to 3 child records).

            • 3. Re: Milestones on Gantt Chart
              philmodjunk
              philmodjunk Sep 10, 2013 11:03 AM (in response to deathrobot)

                   Yes and a very complex system that will be difficult to modify and maintain.

                   I'd probably get rid of all the repeating fields altogether and use one row portals for everything instead, but what I had in mind are calculations in the portal filters that extract data from the correct repetition of the repeating field. THe first portal's filter might use GetRepetition ( RepeatingFIeld ; 1 ) and the next uses GetRepetition( RepeatingField ; 2 ) and so forth. (And you can also reference repetitions in this format: Table::RepeatingFIeld[Index])