Use formulas to convert values logged in Mapistry’s environmental data platform into the values needed for your permit reporting or environmental program. Formula fields have similar functionality as formulas you may have used in spreadsheets. For example, a paint usage log may use a formula field to multiply the paint usage amount (a numeric field) by each paint’s respective emission factor to compute VOC emissions.
Formula Setup
Set up a formula on a Log in the Data Setup tab on the far left navigation bar. All formula fields added to a Log will be available at all sites using the Log. A formula essentially adds a new column to your Log data–each time you enter log data for a log field that is used in a formula, there will be a formula result. The formula fields will be available for use in Views at any site with this Log turned on.
Click into the Log you want to add formulas for, and then click the Formula Fields tab of the setup wizard.
Click the blue “Add formula” button to create a new formula.
This will open a modal where you can create your formula and give it a name and optional description. We recommend using the Description field to provide any references about the formula you’re using, for example, to justify the source of the formula or emission factors. The field library box at the bottom of the modal shows you the available Log and Resource property fields you have available to use in your formula, as well as any other formulas you can build off of.
Building a Formula
To build a formula, type directly into the box labeled “Type a formula here.” Copy and paste the italicized all-caps available field name tokens from the field library box at the bottom of the modal. Directly type in common operators (+, -, *, /) as you would in a spreadsheet.
Here’s an example: For a paint spray booth source, I record the amount of coating sprayed and the amount of coating wasted in my Log. The sum of the amount sprayed and the amount wasted is the total usage amount that I need to use to calculate associated emissions. For my air permit, I have to report the amount of VOC emissions from the paint spray booth, so I need to calculate the amount of VOC emissions associated with the amount used.
- I can see in my field library box that the field tokens corresponding to these Log Fields are AMOUNT_SPRAYED and AMOUNT_WASTED and that both Log fields are recorded in gallons. I need to sum them together to get the total amount used
- I copy and paste the field name tokens from the field library box and type AMOUNT_SPRAYED + AMOUNT_WASTED. I type parentheses to ensure the total coating usage is calculated before multiplying by the VOC emission factor, so I have (AMOUNT_SPRAYED + AMOUNT_WASTED). - To calculate VOC emissions, I’ll multiply the total used by my VOC emission factor, which I have stored as a Resource property field called COATING.MIXED_VOC with the unit lbs/gallon.
- I type the multiplication symbol * and copy the VOC emission factor Resource property field token from the field library box. I now have the formula (AMOUNT_SPRAYED + AMOUNT_WASTED) * COATING.MIXED_VOC. - When I view the output of this formula, each time I use a coating, the total usage will be multiplied by the respective coating’s VOC emission factor to determine VOC emissions (in lbs) associated with using that coating.
Building Formulas Using Resource Properties
In the above example, I had a coating Resource that had a separate VOC emission factor (stored as a Resource property) for every specific coating used at my facility. (For more on Resources, see the Resources product guide). The example formula (AMOUNT_SPRAYED + AMOUNT_WASTED) * COATING.MIXED_VOC automatically applies the correct VOC emission factor Resource property (COATING.MIXED_VOC) from the specific coating that was logged in each Log entry along with its usage. This means that you only have to create one formula field for the formula to apply to all Resources used in the formula.
If you add a new Resource (ex: there is a new coating being used onsite: Coating C), the formula field will apply automatically, as long as the VOC emission factor has been added for Coating C in the Coating VOC Resource property. For the automatic association of Resource property values to work correctly in formulas, only one Resource for a given Resource type can be recorded in a single Log entry. For example, in my Coating Usage Log, each Log entry could only be associated with a single coating used. If I used both Coating A and Coating B, I would need to record two separate Log entries: one for Coating A’s amount used and amount wasted, and a second Log entry for the amount used and wasted of Coating B.
End of Article
Support
If you have any questions or need further assistance, please e-mail help@mapistry.com or call (510) 313-1422.