12 Replies Latest reply on May 11, 2016 12:12 PM by erolst

# Calculation Help

Hello

I have the following fields on a table:

• Bin Number
• Shelf
• Row
• Position

The bin number corresponds to the shelf, row & position where the bin is physically located.

For Example-

Bin A11 is on SHELF A, ROW 1 POSITION 1

BIN A21 is on SHELF A, ROW 2 POSITION 1

BIN A22 IS ON SHELF A, ROW 2, POSITION 2

Is there a way to make the shelf, row and position fields calculation fields that will get the information from the bin number field?

Shelf would always correspond to the 1st character in the bin number field.

Row would always correspond to the 2nd character in the bin number field.

Position would always correspond to the 3rd (last) character in the bin field.

The bin number field will only be 3 characters always.

I don't know if this should be a calculation or script.

• ###### 1. Re: Calculation Help

Shelf = Left ( Bin Number ; 1 )

Row = Middle ( Bin Number ; 2 ; 1 )

Position = Right ( Bin Number ; 1 )

• ###### 2. Re: Calculation Help

FYI, I would make these field calculations usually.  You could choose to make these calculation fields or else text fields that auto enter these calculations.  Since it is the same table, I would use a stored calculation field.

You can do the same calculations in a script and store it back in fields.  But if you do this, the calculations are only good for the records you ran the script on.  If you do it as a field calculation, then you know it is correct for all records in the table.

• ###### 3. Re: Calculation Help

Thanks everyone!

Got it.

• ###### 4. Re: Calculation Help

EDIT

Actually...there are two cases where the shelf number is TWO digits.

Shelf 10 and 11.

How do I edit this calculation "Shelf = Left ( Bin Number ; 1 )" to make it account for the possibility of two digit numbers?

Thanks

• ###### 5. Re: Calculation Help

It depends on how you want to do things.  Personally, I would have 3 separate fields that you put Shelf Row and Position and then have a calculation field that concatenates those three for when you want to see them all together such as Bin Number = Self & Row & Position

But you could  do such things as test the length of the the Bin Number and if it is 4 digits, then use the left 2 digits, if it is 3 digits, then use the left 1 digits to be the Shelf.

Let ( [

F1 = Bin Number ;

F2 = Length ( F1 ) ;

F3 = Case (

F2 = 4 ; Left ( F1 ; 2 ) ;

F2 = 3 ; Left ( F1 ; 1 )

)

] ;  F3  )

• ###### 7. Re: Calculation Help

IMO, you're doing this the wrong way around.

Store the three facts as discrete bits of data, then calculate the string from them.

• ###### 8. Re: Calculation Help

Taylor Sharpe wrote:

test the length of the the Bin Number and if it is 4 digits, then use the left 2 digits, if it is 3 digits, then use the left 1 digits to be the Shelf.

Left ( string ; Length ( string ) - 2 ) ?

• ###### 9. Re: Calculation Help

'The bin number field will only be 3 characters always.' That lasted about 6 hours!!

You could script it as the replies indicate. Can you provide examples of where/how you have the 10 and 11 to help us understand.

Do you mean a code of 1011 or 1012 etc and 1111, 1125 etc? or do we still have an alpha at the beginning? or codes like A1,10 and A1,11 or A10,1 A10,2 and A11,1 A11,2  (the previous examples without commas) where A111 is different than A111?

I would be tempted to go with 2 characters for each e.g AA; AB etc and 01 - 99 for rows and position. Lots of room for expansion or stick with a single alpha if that works.

• ###### 10. Re: Calculation Help

Do what erolst is saying.  Put them in discrete fields.  Then you can have a calculation field connecting them together in one field called Bin Number  = Self & Row & Position    That way you just have one calculation field.

You might want to do some training or maybe consider a consultant to help you out.  Check out the FileMaker Training Series (Basic).

And we'll be here to help with little specific questions like this.

• ###### 11. Re: Calculation Help

Ok so here is the format

A101

Shelf A

Row 10

Position 1

A102

Shelf A

Row 10

Position 1

A111

Shelf A

Row 11

Position 1

I am just trying to reduce data entry by having the shelf, row and position auto entered because it is a factor of the Bin Name.

Thanks

• ###### 12. Re: Calculation Help

MaxEh wrote:

'The bin number field will only be 3 characters always.' That lasted about 6 hours!!

Isn't RL just a b… messy old place?

"We will never, ever open a second branch in any one town. Just go ahead and use the city name as a primary key!”