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.
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?
"3b1, 3b2, 3b3 etc. this stands for bay 3, column b, box1, 2, 3 etc. "
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.
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?
The example below sorts Title locations by Box, Bay & Column and details a solution very similar to LaRetta's.
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. :^)
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]
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]
Set Variable [$Value#; Value:1]
Set Variable [$#Values; Value: ValueCount(Title::ParseLocation)]
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]
Go to Record/Request/Page [Next; Exit after last]