You aren't limited to a single relationship between a given pair of tables. You can define as many different relationships as you need.
The trick is to use the duplicate button (labeled with ++) in Manage | Database | Relationship to make additional Tutorial: What are Table Occurrences? of your Boxes table so that you can select different sets of match fields for different relationships.
So I started down that route but I think I was thinking of it incorrectly and tried to duplicate the "Inventory" occueence and not the Boxes table.
I also can't get the number of items in a box "Box Count" by setting this field to a summary of "Box Number" I am also not sure how to get a count of the items in a box in the actual box record. "Slots Full" field.
My thought on this would be to set "Slots Full" to a calculation that would count instances of box number that match the box number in the current record in the box table. Is this possible to do?
Slots Full = If (Inventory::Box Number = Boxes::Box Number; Count(Inventory::Box Number; 0)
I tried this calculation and it doesn't work. its just the logic I was thinking
You can't use if like this to selectively count ( or sum, or average or any other aggregate function...) a subset of your related records. Instead, you need the relationship you use to only match to the records you want to count and then count ( RelatedTableOccurrence::NeverEmtyField ) will produce the count that you want. And there are other ways that you can get that sub total also.
Let's back up from all that a moment and look at the big picture. Please answer the following questions. I need to make sure that I am not making any incorrect assumptions about the structure and intended function of your database tables.
What does one record in Boxes represent? Does it represnt a box of a specific size/design or is it a record that documents what items from inventory are being packed in a particular box? I have assumed the second possibility.
What does one record in inventory represent? Does it represent a single, one of a kind item or does it represent a group of identical items that all might be shipped in different boxes to different destinations? In other words, does one record represent the the 50 Ford Mustangs that you have in stock or does it represent one Ford Mustang with a specific VIN#?
So to answer your questions:
1: A record in "boxes" refers to a specific box/container. We have numbered boxes that hold 28 hard drives each (Sometimes we put other items in them and then the count would be different than 28, but that the defualt).
2: A record in "inventory" refers to a specific hard drive or item in a particluar box. So 1 specific hard drive with a specific ID#
The reasoning for this is so I can setup a way to see what box is available to put hard drives into without having to go into our storage room and physically check.
I figured out the value list part, where only box numbers that have a status of "Available" show in the drop-down list, but now I need to automatically switch a box from "Available" to "Full" once 28 records (Or once "Slots Full" = "# Of Slots").
I understand how to make the status change, but I can't figure out how to get the count of each "Inventory" record that matches the particular box when broswing records of boxes in the "Box" table.
I had it working in a layout for "Invenotry" before but now its not even working right there. the relationship was setup to match "Box Number" and then when a record in "Inentory" had a box number of, lets say for examble, 100 then it showed a count of all "Inventory" records that also had a box number of 100.
Since I can't use that relationship in order for my Conditional Value List to work correctly, I am not sure how to acomplish this.
So one record in boxes matches to one slot in the box, not one box with 28 slots?
In which case, I'd add another table here to have:
Boxes::__pkBoxID = Slots::_fkBoxID
Inventory::__pkInventoryID = Slots::_fkInventoryID
You can place a portal to Slots on the Boxes layout to list and select Inventory records for each given Boxes record. Fields from Inventory can be included in the Portal to show additional info about each selected Inventory record and the _fkInventoryID field can be set up with a value list for selecting inventory records by their ID field.
You can use a script performed via a script trigger to update a status field in boxes each time you exit the portal object.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
One record in boxes refers to one box. Each box has 28 slots (By defualt/Auto Enter) This can be overridden though. I had this as a value/field of the box record. So box 1 has 28 slots, Box 2 has 28 slots, Box 3 has 24 slots etc.
One record in Inventory refers to one item placed in one slot in a box. So when we open a new box I want to go in and add a new Box record to the Box table. We would enter the box ID, and number of slots available in the box.
Then when someone has a hard drive that needs to be placed in a box they go in and add a new record in the Inventory table with the hardDriveID(Barcode), Box Number (the box it is to be placed in),user, etc. I want users to easily be able to see what boxes have X number of slots free.
So if I add 25 new records into the Inventory table and put Box number 1 in the Box Number field, I should be able to go to a layout showing records form the Box table and look at the record for Box number 1 and see that Slots Full = 25. I can then use this in my layout for entering Inventory records to show "Number of Slots" - "Slots Full" that way users can see that they can add 3 more hard drives to this box.
So logically planing this out I simpy want the field "Slots Full" to be a calculation of how many Inventory records have the "Box Number" field that matches the current record in the Box record.
I beleive your solution for adding a new table for slots would work but then we would have to go in and create X number of slots each time rather than just adding a new Box record. My first screenshopt shows the fields for each table (Box, and Inventory)
And adding those slots really isn't a big deal as you can automate that process with a simple script.
However, you can also use this relationship:
Boxes::__pkBoxID = Inventory::_fkBoxID
The same scripted update method can be used to update a status field in Boxes via an OnObjectSave trigger on the _fkBoxID field--which you would set up with your conditional value list of available boxes.