Skip to main content

Returning Value based on date being between two dates


1 comment

  • Permanently deleted user
    Jake Marwil commented  ·    ·  

    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.




Please sign in to leave a comment.

Powered by Zendesk