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

Value List Help

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?

• 1. Re: Value List Help

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

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

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

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

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