I am trying to produce a formula to calculating carbon emissions factors.
When inputting a new meter record I need the date to return a value if it is between two dates.
I then need that value to be multiplied by the new meter value.
For this to work I presume I am going to need to set up another table with all of the dates and values I want to be returned. But how do I lay the table out?
Do I do a start date and end date? Month on Month?
Really need some help on the formula and how to set up the new table.
Thank you in advance.
Tom shared this idea
Comments
1 comment
Hi Tom,
To create your formula to retrieve the carbon number, you will first need to create a parent table that contains all the dates and carbon numbers. For setting up your table, you may want to include a start date, end date and the carbon number. You would then set up your Record ID in this carbon numbers table to be a combination of the start date and end date (e.g. "02/01/2014 - 03/01/2014").
Then when you create a meter record in the child, your lookup field to connect to the parent carbon numbers table will list off the two dates, whereupon you can choose the proper parent to link to based on the date you enter for the meter record appearing between the two dates listed in the Record ID in the relational drop down field. Say for example you have two records in the Carbon Numbers table with Record IDs:
2/1/2014 - 3/1/2014
1/1/2014 - 2/1/2014
If I enter in a meter record with a date of 2/18/2014, then you would know to choose the '2/1/2014 - 3/1/2014' parent Record ID.
Finally, in the table where you are entering meter records, you will add a calculated number field. Since you are already linking to the record with the proper carbon number, your formula can simply reference the carbon number field in the carbon number table and multiply it by the new meter value. Your formula might look similar to this:
{Carbon Number Table}.{Link}.{Carbon Number Field} * {New Meter Value}
Please let us know if you have any issues or further questions.
Regards,
Jake
Please sign in to leave a comment.