2 Replies Latest reply on Oct 23, 2013 9:21 AM by taylorsharpe

    pulling data from a large table


      Need to pull a specific value from a 10 x 20 reference table based on the axis values which varies by record. I could create a field for each of the 200 values and then use a very long if...then equation to pull the correct data, but think there might be an easier way, I just don't know what it is.


      Table is age adjusted hearing loss based on frequency, age and gender (https://www.osha.gov/pls/oshaweb/owadisp.show_document?p_id=9741&p_table=STANDARDS. Table has 20 years with 5 frequencies adjustments per gender (10 across and 20 down).


      Could anyone point me in the right direction? Thanks.

        • 1. Re: pulling data from a large table

          Hello, doct.


          What you have is essentially a return delimited list. Think of it as a series of values separated by carriage returns. FileMaker has a number of functions specifically tailored for dealing with data like this, notably, LeftValues, RightValues, MiddleValues, and, most importantly (IMHO), GetValue.


          My recommendation would be to pull the entire list into a variable. Then, loop over it until the leftmost two characters are equal to the age you want. You know the position in the string you want (based on the number of characters in the string); hence, the Position function should allow you to extract the correct value.





          • 2. Re: pulling data from a large table

            You could import the data and leave it as is and have calculation fields grab data from each record.  That way every time you import it, all your FileMaker separated fields are ready to go.  As Mike said, just use character manipulation functions to grab the data you want. 


            For example, Table F1 from your web page shows the following data that I imported into FileMaker into a single field called Data.  From that I wrote calculation fields that grab the data from the Data field so you can work with those calculation fields however you want.