AnsweredAssumed Answered

Methods to create a timeline chart

Question asked by grouper on Jul 27, 2015
Latest reply on Jul 28, 2015 by philmodjunk

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

Outcomes