8 Replies Latest reply on Jun 12, 2012 2:52 PM by Sprucegum

    How to put serial numbers in numerical order automatically

    PeterMontague

      Title

      How to put serial numbers in numerical order automatically

      Post

      I have inherited an inventory of books in a warehouse. I have imported the locations of the books which are stored chaotically in an excel file using the import function. The locations are name as follows: 3b1, 3b2, 3b3 etc. this stands for bay 3, column b,  box1, 2, 3 etc. with box one stacked on top of box 2 etc. the lower the box number the easier it is ti find the book. To find a book in box 10 would require removing nine boxes from on top. We have many copies of each title and many titles are stored in multiple locations. My location field often looks like this: 3b3, 3b7, 14c1. I would like to sequence the locations based on the number following the last letter in the location code. I know replacing the comma by a carriage return may be a good staring point: what then?Any ideas?

        • 1. Re: How to put serial numbers in numerical order automatically
          Sorbsbuster

          You seem to have one record per book title.  I would suggest you create an additional table of one record per copy of the book, with each copy record assigned to one location.

          At a practical level I would have thought it was easier to number the locations from the bottom up.  When you add a new box to an existing column it would be easiest to bung it on the top.  But in that case you will have to find the records for all the existing books at that location and renumber them.  If you counted the stack from the bottom you need only add one record with the next higher suffixed number.  To avoid renumbering you will have to take all the boxes down and set the new box on the bottom, and re-stack the existing boxes.

          • 2. Re: How to put serial numbers in numerical order automatically
            PeterMontague
            I might try that some other time. But at the moment I would like to sequence the books in ascending order. Is there a way of parsing and sequencing the serial numbers based on the number after the last letter?
            • 3. Re: How to put serial numbers in numerical order automatically
              LaRetta_1

              "3b1, 3b2, 3b3 etc. this stands for bay 3, column b, box1, 2, 3 etc. "

              Hi Peter,

              You have inherited data which violates normalization rules in two ways:

              *  More than one location for a single part (many locations separated by comma )

              *  More than one fact per location ( Bay, Column and Box within same location )

              With current setup, it is now very difficult to pull the data back out as needed whereas if properly normalized once inside FileMaker, it would be piece of cake to get any information in any configuration.  If structured properly, you can easily find, sort, generate reports by location and much more.

              Regardless, you will now need a script to parse the data into records because even if you created numeric 'list' of the last numeric value from each group into a multiline text field, you cannot then sort those values because they will sort as text AND the values within the field will not sort anyway - only records sort (easily).

              I have created a simple file showing exactly what you are discussing and what I am suggesting instead.  It provides a normalized structure where you can now add multiple locations to a part AND track the data within each Location (Bay, Column, Box).  I am using a custom function to make creating the substitute calculation easier (I use it for many different substitute issues particularly scripting).  You could also write out the substitute in its natural multiple-list way if you do not wish to use a custom function.

              Click the Parse button to parse and create your related records.  Note that the Locations table is sorted descending on Box number (in the relational dialog in the graph).  Then by placing the Locations::Box field directly onto your Parts layout (the green field), the 'largest number' box is always displayed.

              I hope this is helpful.  I realize you just want to quickly get results with what you have but that is not going to be the case.  Link to example file: http://www.directlinesolutions.com/downloads/parse.zip

              Also note that, if your data is inconsistent, it will not produce correct results, i.e. if field contains b3 only then script will improperly parse it. :^)

              EDIT:  "the lower the box number the easier it is ti find the book"  Then sort the relationship ascending order to get the lowest number box instead.

               

              • 4. Re: How to put serial numbers in numerical order automatically
                PeterMontague

                Thank you .

                That looks like it will parse out my records for me. I think I will structure my database this way from now on. I think I will also be able to import (not up to date) information about the quantities in each location too which will be very helpful. 

                Is there any way, once the information is parsed, to list the locations for each sku, in ascending order of box number, and then ascending order of bay number to make picking easier?

                Peter.

                • 5. Re: How to put serial numbers in numerical order automatically
                  Sprucegum

                  Hi Peter,

                      The example below sorts Title locations by Box, Bay & Column and details a solution very similar to LaRetta's.

                  Steve

                  • 6. Re: How to put serial numbers in numerical order automatically
                    LaRetta_1

                    Peter asked, "Is there any way, once the information is parsed, to list the locations for each sku, in ascending order of box number, and then ascending order of bay number to make picking easier?"

                    To make picking easier from where?  If from Locations, sort by box number then bay number.  If from Parts, sort the relationship in your graph as I've indicated to make the 'best' location the first location listed.  If this is not what you need then you will need to explain more specifically what you need. :^)

                    • 7. Re: How to put serial numbers in numerical order automatically
                      PeterMontague

                      Hi Steve,

                      I tried using your script. But I think there may be a mistake in it. I'm not able to execute it anyway. It tells me there is an error in the script step

                      Exit loop if [$Value# = $#values]

                       

                      • 8. Re: How to put serial numbers in numerical order automatically
                        Sprucegum

                        Hi Peter,

                             Works fine on my sample copy.  Check that:

                        • Value set for variable $Value# = 1
                        • Value set for variable $#Values = ValueCount(Title::ParseLocation)
                        • Title::ParseLocation calcluation field includes the substitution [", "; "¶"]

                             Please note: The script assumes all records in Title table include 1 or more values in Title::ParseLocation.  If this is NOT the case, amend script as follows:

                             Go to Record/Request/Page [First]
                             Loop
                                  Set Variable [$Value#; Value:1]
                                  Set Variable [$#Values; Value: ValueCount(Title::ParseLocation)]
                                  Loop
                        Exit Loop If [$Value# > $#Values]
                                       Set Field [Location: :Location; GetValue ( Title::ParseLocation; $Value#)]
                                       Set Variable [$Value#; Value:$Value# + 1]
                                       Go to Portal Row [Select; Next]
                                  End Loop
                                  Go to Record/Request/Page [Next; Exit after last]
                             End Loop

                        S