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

    Calculation Help

    user26927

      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
          taylorsharpe

          Shelf = Left ( Bin Number ; 1 )

           

          Row = Middle ( Bin Number ; 2 ; 1 )

           

          Position = Right ( Bin Number ; 1 )

          • 2. Re: Calculation Help
            taylorsharpe

            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
              user26927

              Thanks everyone! 

              Got it.

              • 4. Re: Calculation Help
                user26927

                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
                  taylorsharpe

                  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  )

                  • 6. Re: Calculation Help
                    user26927

                    Way over my head brother.

                    • 7. Re: Calculation Help
                      erolst

                      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
                        erolst

                        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
                          MaxEh

                          '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
                            taylorsharpe

                            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
                              user26927

                               

                               

                              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
                                erolst

                                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!”