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.
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).
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?
- 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.