If you have a table of 21 records with a number field for x and a calculation field for y, you can easily chart this data. The trick is to avoid having to enter initial cost and energy used more than once. There are two ways to do that.
You can put these values into global fields. The value in a field with global storage will be accessible to all the records in your table. Then your calculation for y becomes:
gInitialCost + X * gEnergyUsed //Iuse 'g' as the first letter of all fields for which I select global storage.
You can put these two values in a related table with a relationship that matches all 21 records in your table of chart data to the same record where you have entered that data. That will produce a slightly different expression:
RelatedTable::InitialCost + X * RelatedTable::EnergyUsed
Either way works and which is "best" will depend on other design and use factors that I can't perceive from the information posted thus far in this thread.
Thanks for the quick reply.
The problem is the there are hundreds of records and each record has a chart on it. Also the number of periods changes depending upon the use per year. ie Life time of 50000 hours used 9 hour a day requires 21 periods the same light left on continuously only requires 6 periods.
I did think that I could create 20-30 fields on each record, each one calculating a specific period then a final field to combine all the results into a single series, but I'm sure it must be able to do the calculations in one go and generate the delimited field with the required number of values.
Can a loop be made in a calculation field or is it only in scripts. I think I could do this with a script.
This looks like data that should exist in a table related to your current table of hundreds of records. There's a data source option for related data that you can use for this purpose. And these records can be any number of records that you need for that specific record.
And a looping script can take the data in a record in your current table and loop to produce the needed related records for your chart data.
Thanks for your reply,
I ended up writing a script that looped the required number of times to build the delimited string I needed for the chart.
I then added a script trigger to run the script when the data changes.