4 Replies Latest reply on Jul 28, 2015 11:58 AM by philmodjunk

    Methods to create a timeline chart

    grouper

      Title

      Methods to create a timeline chart

      Post

      Hi

      I have a database with the basic 2 tables :  Clients----<Appointments . I 'm trying to create a timeline chart that would have X axis as a timeline : starting from the first appointment and ending to the last appointment, with regular monthly intervals in between (total duration more than a year). For each appointment I 'd like the Y axis to show a specific value from a field, named Score.

      I 've explored various options (Javascripts too advanced for my level) and would like to stick to native FM solution. In order to do it, I 've read about the following :

      Create a dummy table, with a field that is monthly_timeline and a Score field. A script should populate the timeline_field with monthly intervals (starting from first appointment and ending to the last) and the Score field should be "copied"(?) or "looked up"(?) from the related table corresponding to the month. But should the script delete all entries everytime it is run before populating again? How should I set up the relationships so that the "Score" corresponds to the particular client and get all relevant dates?

      To give you an idea about the tables :

      Clients                                           Appointments                                 

      Client 1   ----------                        1/1/2015 ,       Score : 2                       

                                                             1/3/2015         Score  :4                       

                                                             1/5/2015         Score  :3                       

      Client 2   ----------                        1/5/2015         Score 10                       

                                                             1/6/2015         Score : 8                      

      etc

       

      Hypothetical "Dummy table" (for Client 1)

      1/2015      Score 2

      2/2015      -----

      3/2015      Score 4

      4/2015      -----

      5/2015      Score 3

       

      thanks

        • 1. Re: Methods to create a timeline chart
          philmodjunk

          You want the dummy table so as to interpolate between actual data points correct? So that you don't get a zero why value for each month where there is no appointment?

          If so, yes, you'd use a script to generate the records. A relationship to look up (which means copy over) the data and then a script would need to interpolate some points between appointments.

          As long as each record in your charting table stores a client ID, you would not have to delete records from the charting table. you can even set up scripts and triggers that update records in the charting table, each time you add/delete/change data in your appointments table. But you could set this up to delete all records and start over each time you chart data if you want.

          • 2. Re: Methods to create a timeline chart
            grouper

            With your advice I managed to get it to work. I had to create 2 relationships in order to get the lookups to work properly, attached to a script (one with the client ID and the second one with the dates).

             

            thanks

             

            • 3. Re: Methods to create a timeline chart
              grouper

              Ran into 2 issues though :

              - When there are more than 1 appointments per month, the lookup field gets only the first one from the related table. Is there a way to make it get the highest score from all appointments for that month for the given client (or maybe the average?).

              - The chart that appears on form view is empty unless i click with the mouse somewhere on the layout (after that the data appear). Any way around it?

               

              • 4. Re: Methods to create a timeline chart
                philmodjunk

                - you can use an auto-enter calculation with an aggregate function such as Max or Average to get the value you want to chart.

                - that mouse click is committing records. Have the last step of your script do a commit records and you shouldn't need to click the layout.