2 Replies Latest reply on Dec 2, 2015 7:39 PM by bigtom

    Help with unit conversions and searchability




      I've inherited a FileMaker (version 14) database at work that needs more functionality.

      Currently the template uses a Boolean function to populate field labels (gray fields) for specific units of pressure.

      Pressure value (white field) is input by user:


      Example:  4 Boolean functions include: kg/cm^2,    psi,    kPa,  or   bar


      Units are stored in the data base based on selection made by the user.





      My goal is to be able to perform searches based on one pressure unit (example: psi) and have all records returned that are equal to 400 psi regardless of the input unit.



      Any suggestions on how to accomplish this?

      Thank you for your time!





        • 1. Re: Help with unit conversions and searchability

          1) gather the user input in 2 globals, gUnit and gValue;

          2) enter find mode;

          3) depending on unit set by the user, set field <correct field>  to gValue, then perform the search.

          • 2. Re: Help with unit conversions and searchability

            It looks like you have a field for unit (based on a value list) and one for value.


            There are a few ways to do this, but I think you may be best to have one system standard unit in a calculated field specifically for the searching. For example no matter what is entered (PSI, kPa, mmHg...) you still always store a specific SI unit and this becomes the base of your search. This way there is just one extra piece of data for reference.


            You would search by script and calculate the entered value as an SI unit and store that in a variable. Perform the search on a layout that has the searchable SI unit field and you are done.


            How to implement it?


            Considering the three fields would be Unit, Value, and SI_Value and you use kPa as the standard:


            The calulated field (SI_Value) would be:

            Case( Table::Unit = "SI"; Table::Value; Table::Unit = "US"; (Table::Value * 6.89476); "" )


            In a script:

            Set Variable [ $siValue; Case( Table::Unit = "SI"; Table::Value; Table::Unit = "US"; (Table::Value * 6.89476); "" ) ]

            SetField [ Table::SI_Value; $siValue ]


            Just add in the conversion formulas for the units you are using and their unit names. "Case" is a fast calculation function.


            This can be an unstored calculation field or you can set the field by script using a save or commit button for the record to use when it is created or edited. If the data is not changing regularly I would take the time to set the field by script because unstored calcs can slow things down. You would also need to make a script to loop through any existing records and populate the field. For testing you could just try the unstored calc field to see the results.


            If you supply 400 PSI for the search, the scripts converts this to 2757.9 kPa and searches for that value in the SI_Value field. This will give you all the records with a pressure value equal to 400PSI.