5 Replies Latest reply on Jul 25, 2013 7:59 AM by RichardStrohman

    Value List Help

    RichardStrohman

      Title

      Value List Help

      Post

           I am fairly new to FileMaker and am creating an estimating database for work.  We custom manufacture different sized fiberglass vessels.  That being said each size has a different cost.  What I need is a way to select a size and also use the same value to figure the gallons in the tank.  

           I can sucessfully do this if I use generic numbers such as 1-10, but I need to be able to use expressions such as 5'-0".  When I make a value list like that the formula to calculate gallons comes up with an error becuase it cant read the feet and inch marks.

           I have done something similar in Excel and had two lists of values.  One was the list with feet and inches and the other was matching generic numbers.  I used the lookup function to put the matching generic values in the gallons caluculation.

           Is there a similar way of doing this in FileMaker, or is there anyway I can do this at all?

      Screen_Shot_2013-07-24_at_4.22.01_PM.png

        • 1. Re: Value List Help
          philmodjunk

               I think you want to enter or select feet and inches in the same field so that the user enters or selects a dimension such as:

               5'-6" and the calculation correctly computes a volume in gallons.

               IF that's what you want, it's mainly a matter of parsing the feet and inch portions of the measurement from the text expression entered into the field such as:

               Let ( [ D = Filter ( YourTable::YourDimension ; "9876543210'\"" ) ; //filter out all characters except digits 0-9 and the " and ' characters
                        FtPt = Position ( D ; "'" ; 1 ; 1 ) ;
                        Ft = Left ( D ; FtPt - 1 ) ;
                        In = GetAsNumber ( Right ( D ; Length ( D ) - FtPt ) )
                       ] ;
                        Ft * 12 + In
                       )

               This converts a measurement expression such as 5'-0" into 60 inches (5 x 12 + 0 )--which should get you to a point where you can plug that value into a formula for computing the volume.
                       

          • 2. Re: Value List Help
            RichardStrohman

                 That helps me get closer.  Thanks.  The only problem is that when I select a size like 10'-0" the output is 1.0.  It also does 20'-6" as just 26 instead of 20.5.  Is there anyway I can match the number with feet and inches (5'-6") to a number in decimal form (5.5)?

            • 3. Re: Value List Help
              philmodjunk

                   Better double check what you have against what I posted previously.

                   When I input 10'-0", I get 120 inches. And 20'-6" yields 246 inches--both correct conversion into inches. I tested this by copy and pasting your example values.

              • 4. Re: Value List Help
                philmodjunk

                     And if you want results in feet rather than inches:

                     Let ( [ D = Filter ( YourTable::YourDimension ; "9876543210'\"" ) ; //filter out all characters except digits 0-9 and the " and ' characters
                              FtPt = Position ( D ; "'" ; 1 ; 1 ) ;
                              Ft = Left ( D ; FtPt - 1 ) ;
                              In = GetAsNumber ( Right ( D ; Length ( D ) - FtPt ) )
                             ] ;
                         Ft + In/12
                             )

                • 5. Re: Value List Help
                  RichardStrohman

                       Okay.  I missed the ' symbol between on one of the lines.  Everything works as I need it to now.  Thank you!