First a key question about your design.
How are you currently recording the multiple locations where a given item is store?
Ideally, you should have two tables. Table 1 lists each item in inventory. Table 2 should be a related item where a new record is created for each location where a given item is stored.
That option enables you to display the existing locations in a portal or you can set up a calculation field with the List and substitute functions to get your horizontal list of existing locations.
Thanks for replying. I have Table 1, it currently has 1000 records for all the items in the inventory. If there are multiple locations for an item, they have a seperate record, for example:
Item A: Rack 1 Box 1, is record 1
Item A: Rack 3 Box , is record 2
Item A: Rack 4 Box 4, is record 3
I will start setting up Table 2. I'm sorry to ask, "you can set up a calculation field with the List and substitute functions to get your horizontal list of existing locations". Do you mean that I set the new field in Table 1, lets call it Field 1 to calculate the locations listed for Item A in Table 2? I'm not sure I understand what to do.
Well, what you have is close to table 2. What you don't have is table 1.
Let's call the new table "Items" and the current table "Locations".
Then you can relate them like this:
Items::ItemID = Locations::ItemId
Then you can define a calculation in Items as:
Substitute ( List ( Locations::RackBoxField ) ; ¶ ; ", " )
Rack and Box should probably be separate fields. If they are, you'll need to define RackBoxField as a calculation field in Locations to combine the Rack and Box information.
Thank you for the reply. I am trying to set this up. Rack and Box are seperate fields. So I've created a new field per your suggestion, RackBoxField. The calculation is: RackBoxField= Rack and Box
I'm a little stuck, I created the Items Table. I set the relationship of Items::VialID = Locations::VialID. So why can't I get the records in the Locations table for Item A to create a record in the Items table?
Where you have joined the tables in your relationship graph, click on the box ( probably '=') and allow creation of records in this table via relationship underneath the items table.
Your calculation should be something like Rack & " " & Box (and is a logical operator and won't combine your two field values like you need here.)
Your items table won't load itself. You'll need to use Import records to create one record for each Item in your inventory.
Assuming that the field ItemID uniquely identifies each item, Open Manage | Database | Fields. Find ItemID in the Items table and double-click it. On the validation tab, select "unique values" and "validate always".
Now, go to your Items layout. Select Import Records from the File menu, select the same file you have open as the source file, select the Locations table as the source table and import your records. The validation rule we specified will filter out duplicates and just create one record for item in your inventory.
Okay, I seem to have missed something.
Example: In the Items table,
Record 1 has Item A: Rack 1 Box 1
Record 2 has Item A: Rack 3 Box 1
Record 3 has Item A: Rack 4 Box 4
The new field, termed Additional Locations, now lists
Record 1: 1 1
Record 2: 3 1
Record 3: 4 4
The Additional Locations field calculation: Substitute ( List ( Locations::RackBoxField ) ; ¶ ; ", " ) , is yielding the same Rack/Box location that exists in the record. What I'm hoping to see is
Record 1: 3 1
Record 2: 1 1
Record 3: 1 1
Is this possible?
Yes, it's possible, and that's what you should be getting. There's something not right with the relationship defined linking your two tables.
What fields are you using to relate a record in the Items table?
BTW, you should have only one record in items for Item A, one for Item B etc. The import process I described was intended to use a validation rule to filter out these duplicates during the import.
It also looks like your rack and box fields are number fields. If so, you might need to update the rackBox field to be:
"Rack " & Rack & "Box " & Box
The return type of this calculation field should be set to Text.
Okay, I fixed the RackBoxField calculation.
If I filter out duplicate records for Item A, how will I be able to tell that there are multiple additional locations?
If Item A has Records 1-3 corresponding to Rack1Box1, Rack3Box1 and Rack4Box4 and I filter out the multiples, how will I get the database to tell me that the other locations exist? Should I not filter?
Also, attached is a picture of my Relationship table between the two tables. I have an Items table and an Inventory (Locations) table.
Please take a look at the demo file. The one record for Item A, for example will related to all the location records for item A and thus the calculation field will be able to use the List function to get a list of all the locations.