Presumably, when you say:
I try to view Locations from the Inventory Items Table
You mean that you have a layout that specifies the T01 occurrence of Inventory Items in layout setup. If you place a field from T01a1 to show the location, you will have a problem. For any Inventory Items record, there could be many related records from Locations (T01a1), but the field can only display one value. In FileMaker, you will get data from the "first related record" of T01a1 that is linked to the first related record of T01a.
To properly show all related locations records on this layout, put a portal to T01a on your layout and include whatever fields from T01a1 that you find useful in the row of this portal. You will then see all locations for a given T01 record.
The "first related record" will be the first record in order of creation unless you specify a sort order for the relationship. Note that the records with the Lavner Number of 001 were the first ones created from what I can see here.
1 of 1 people found this helpful
Are you using the item name (e.g.: "Acer Laptop") as the join field in the relationship?
What is the purpose of the join table? I'm seeing that the Items table has duplicate item names, and they each appear to be assigned to one location each, so there's no need for the join table which would only be useful for a "many to many" relationship. For example, the join table would be useful if you had only 1 instance of "Acer Laptop" or "Projector" in the items table, and you needed to assign it to multiple locations. The "lavner number" would then go in the join table.
I'm confused about why the location is blank - I would expect it to be wrong in all the instances where it's blank (I would expect it to show the first "001" location in all instances), but I have a feeling it would all be clear if we could see the field names for all these layouts in layout mode.
First off, thank you for responding
Secondly, I think I see what you are saying but I want to run through my goal again to be crystal clear. On the business
On the business end, we have many Acer Laptops. Each Acer Laptop has a unique Lavner Number. But Lavner Numbers can be reused for different types of inventory. For example, there can be an Acer Laptop with Lavner#001 but there is also an Epson Projector with Lavner#001.
I want to be able to pull up Acer Laptop-Lavner#001 see all its information, and what location it is currently assigned to(it can only be assigned to 1 location).
I also want to be able to pull up a location and see all the Acer Laptops/any inventory items that are assigned to(which I can! In the screenshot above, you can see all the assigned inventory items for the Katz location.)
The issue is that for some reason, when I try to pull up an Inventory Item i.e. Acer Laptop-Lavner#002, it does not show the assigned location. But for some reason, all the inventory items with a Lavner# of 001 DOES show the assigned location. You can see in the Location screenshot above that Acer Laptop-Lavner#002 is assigned to the Katz location. But like I said, Katz does not show up when I view it from the Inventory Item table.
Below is a screenshot of the whole RG: All relationships are connected via IDs.
P.S. If you are saying its easier to just make one Acer Laptop Inventory Item, and assign the LavnerNumbers to locations, I would be open to giving that a shot.
Thanks for responding!
I understand that displaying a related field will only show the first related record unless you set it up in a portal. Which is fine for me because each Inventory Item will only have one related Location. But the issues is not that each Inventory Item is displaying only the first related record. The issue is that only the Inventory Items with a Lavner Number of "001" are displaying the related record(location). All inventory items with Lavner Numbers of 002 or higher display nothing.
Here is my entire Relational Graph below. Hopefully this helps clear things up.
If I am misinterpretting what you are saying, my apologies. Like I said, I am still trying to wrap my head around this.
Sigh... dumb mistake. Didn't need the Inventory Assignments table(join table) because its 1:1 relationship. You're right, went right over my head. Anyway, thanks for help.
I do understand what's going on with the multiple similar items and the lavner numbers, I'm just not clear on how the data is organized in the database beyond what I can see in the relationship graph, because I don't know which field names correspond to the data I'm seeing in your layouts.
It's helpful to see your relationship graph opened up to see all the fields - thanks for that. It would be additionally helpful to see your "Inventory List" layout in layout mode so I can see the field names. And if this layout truly is based on Inventory Items, then again I don't see the point of having an Inventory Assignments join table, since you already have a record for every physical item right in Inventory Items.
If you want to have 3 tables, which is the more normalized approach, you should only have 1 record for "Acer Laptop", while the join table would have a record for each individual Acer laptop, and the ability to assign it to a location (and the lavner number goes here).
Otherwise, why not just have 2 tables? Inventory Items (item name, characteristics, lavner number, location id) and Locations (location id and location name/address/etc.)
Edit: just saw your last message. But you might still find the above helpful because there is a good argument for having 3 tables especially if there are multiple fields that would be duplicated for additional inventory items of the same type (e.g.: 5 Acer laptops that have the same name, specifications, etc... just different lavner numbers and locations)