Your Inventory 2 table is related to Inventory via the foreign key _kf_Item_ID. Therefore, you are seeing the behavior that you should expect to see...If your base table is Inventory, and you show a portal of related records from Inventory 2...you'll see all of the records which match the _kf_Item_ID of the record you are looking at.
Change the relationship between Inventory and Inventory 2. Make it so that if _kf_Set_ID = _kf_Set_ID. Then when you are looking at a particular Inventory item, in your portal you will see all of the Inventory Items which are in the same set. Turn off portal filtering first. That way you'll know you're seeing all of the records you're expecting to see. That's step 1. We'll work on filtering after you've got something good to filter.
I have other portals connected to Inventory 2. Would creating an third occurrence of the inventory table work? then set the portal up the same as I have for the other portals I have used?.
I have tried this but the portal without filtering shows the correct number of items in the set but they have incorrect information showing. (for instance I have one of Item A in the set and three of Item B, the portal shows four lines but all as Item A when I am on Item A in the main layout)
There are a couple of suggestions that might help.
First, "Inventory 2" doesn't really tell you much. Double click on the Inventory 2 table occurrence (TO), and rename it. You are linking the Item to the item, so maybe that TO should be named Inventory_Item or Inventory_ItemLink. That way, as things build, your names will help you understand what it is that you're doing. No big deal there...It won't change the way anything works but it'll help you organize and document your schema.
Second, before starting to filter, make sure your portal is showing correct information. You're saying it's showing the correct number of items but they have incorrect information. That means that you've connected properly to Inventory 2 (the related records have been found). BUT...In a portal, any field from tables OTHER than the main table for the portal (Inventory 2) will show only the first related record's information.
For instance, Inventory 2 has 4 records which have the same kf_Item_ID. That's why 4 records are in your portal. Let's take record 2 from Inventory 2. It's kf_Item_ID is the same as four records in the Inventory table. Any field you use from the Inventory table will show you data from the first record in Inventory that is related to the record in Inventory 2. In other words...Record 1 from Inventory 2 will show you related data from record 1 of Inventory. Record 2 from Inventory 2 ALSO relates first to record 1 of Inventory. Same with Record 3 and Record 4 from Inventory 2. They all relate to the same exact record in Inventory, so any field you reference from any table connected to Inventory will show you information for whatever that record is. That's confusing...but critical to understand.
In the portals showing Inventory 2, stick with fields from Inventory 2.
OK I have renamed Inventory 2 to Inventory_Items. If I am to link Inventory_items to the main table via _kf_SetID_ surely this would void my other tables connected to Inventory_Items. As such I have created a third occurrence named Inventory_Set which is linked to the main table by _kf_SetID_
If I am to understand you correctly the information on my portal only shows the first result for items that match my set ID number and then fills in the correct number of records with this information. How do I get it to show individual items that are in the set? (ie record 2 ,3 and 4 shown as well as record 1)
Is it just easier for me to set up a separate layout for this ? and if so what would you recommend.?
Thanks for all your help?
Renaming Inventory 2 to Inventory_Items is good enough for that for now. Since you have other TO's linked to Inventory_Items, leave the rest alone.
So let's say now you're trying to relate Inventory_Set to items. Let's say there are four items in an Inventory Set.
k_ID_Inventory ; kf_Item_ID ; kf_Set_ID (these are fields from your Inventory table).
Inv001 ; Item123 ; Set100
Inv002 ; Item123 ; Set100
Inv003 ; Item456 ; Set 100
You can see that these three Inventory items all have the same Set ID. They will all show up in a portal showing Inventory_Set.
You can see that two of these Inventory items have the same Item ID. When you're looking at Item123 on the Inventory layout, two records will show up in your Inventory_Items portal.
You can see that Inv001 would link to an Item Detail Line record that would have Inv001 for the k_ID_Inventory field.
Now then...Let's focus on the Inventory Item Inv002. If you try to show something from "Items" in a portal based on Inventory_Item...follow up from Inventory_Item. Since the Item_ID is "Item123", it will look for something in Items with Item_ID 123. Perfect. BUT if you place a field from "Item Detail Line" into the Inventory_Item portal...You'd see data from Inv001 because Inv001 is the FIRST record from that table which matches up with Item123. ALL records with Item123 would show only data from the Inv001 record of Item Detail Line because for that TO, Inv001 would be the FIRST related record.
Look carefully at the data example above and follow your TO's. To show the individual "Items" data in the set, your Inventory_Set table will work.
I am sorry I am a complete dunce and still entirely confused.
Here is my current relationships table.
Item detail line is not required for this portal it shows the history of each individual item ( when it was taken out for which job etc) this portal is only for a list of what is included in the current set.
This is the portal as it is currently. Item, cost Kp_Item Id are from Items table. K_ID_Inventory and Serial Number are from Inventory Table.
Where am I going wrong?
My suggestion is to work through the FMTS before you try to build an accurate inventory tracking system.
It might be difficult to understand the answers herein with out common frame of reference (which the FTS will give you).
The Filemaker Training series that coherentkris referenced is very helpful. I'll second that notion, and it's free.
What is the TO that the layout is based on? To find that, make sure you're seeing the status bar. My screenshot below shows that the TO MY layout is based on is "Invoices". (upper right).
So as I look at a record, it's an Invoice record. A portal would show data from a table related to the specific Invoice record I'm looking at.
So in your layout, you want the referenced TO for your layout to be Inventory. It probably already is. You add a portal which will show records from Inventory_Set. Start simple. Put just kf_Item_ID, kf_Set_ID, and Item into the portal. Make sure that ALL of those fields are from Inventory_Set. There you go. As you navigate to an inventory item, you will see all of the OTHER inventory items which are from the same set as the Inventory record you're looking at.
When you see that...congratulate yourself. You'll have gotten a good beginning on what you want.
You might want to try below 2 method:
1. I notice you are using on tab entry to set your $$SetID variable, I would suggest you to try adding a "refresh window" script at the end of your script. (and ticking both the option inside the script)
2. Removing the portal filter should do exactly what you wanted (showing other item that is in the same set as selected record) I don't think the filter is needed.
what is the table that you use in layout?
Thanks everyone for your help. CoherantKris I will certainly look at the training - to be honest I was just a filemaker user but I am quite tech savvy and my boss just dropped this one on me so needs must!
dtcgnet - The layout is based on the inventory table , I have set all of the portal fields to come from Inventory_set unfortunately this means that the fields showing the item name/description and item cost are now empty (these are usually supplied by the Items table) . The correct record ID (k_ID_Inventory) is shown for each of the records as is the correct Item reference number (_kf_Item_ID). I don't think I can congratulate myself on something that appears to be quite an simple fix but I am still oblivious!
sunnychu - I have added this to my script thank you and removing the portal filter still results in the above^
carlosilvia0 - I am using the Inventory table
I will add yet another picture to better illustrate what I mean
Here is what I was seeing Item 401 should say flashing warning system and a different cost
This is what I am seeing after making the changes you suggested.
If the "Item" field was from Inventory_set, it would fill in properly. I do see that field in your Inventory (and Inventory_Set) table. The Serial Number and the Cost are coming from the Items table. BUT...from the perspective of Inventory_Set...all items are only for the FIRST related record from Items. Why? Because Items is linked to Inventory via Item_ID.
Rename Items to Inventory_ITEMS. Duplicate that TO. Rename the duplicated TO Inventory_SETS. Link to Inventory via kp_SetID. In your portal then, you could list Item, SN, and Cost from Inventory_SETS. That should show you all of the correct data.
OK This must be very frustrating for you as it is for me however, I tried this and now it shows nothing in my portal at all. I know it must be something I'm not getting what it is escapes me. I will try to lay out what I planned below and see if you can make sense of what it is I have tried to achieve.
The serial number field is provided by the inventory table and is unique to that particular item. Any information such as Item name, cost etc is supplied by the Items table and is linked to the main database (Inventory) via Item_ID. And so the information I want in this portal should come from the following sources.
Inventory table - K_ID_Inventory, Serial Number, _kf_Item_ID (So to connect with the items table and provide the below)
Items Table - Item name, Item cost
The portal is meant to show all other Items in the current set connected by a set ID number which is stored in the inventory table. Each individual item is to be listed in order of their Inventory ID number and show what the item is and the approximate cost of each individual item in the set. This sounds like it should have been very simple to make I just don't know where I am going wrong.
There are fields in Inventory (and subsequently Inventory_set) for item and cost but these do not actually have anything in them as the values shown in layouts are provided by the Items table. Set ID is in the Items table but not actually used as that is left over from previous stupid attempts at creating a sets system. (I should really do a clean up of unused fields but I am loathe to delete things in case I break something I have forgotten)
Okay...Do this, and it'll work.
1. Inventory is the base for your layout.
2. Inventory_Set is the base for the portal.
3. Leave the Items TO connected to Inventory as it has been, via Item ID.
4. Your new TO is based on the Items table, and it is called Items_InventorySet. It is linked by Items_InventorySet::Kp_Item_ID = Inventory_Set::kf_Item_ID.
I didn't realize you had blank fields. Sorry about that.
This new relationship will have the following hierarchy:
When you are looking at an inventory item, in the portal, you will see all other inventory items that share the same Set ID. Each of the Inventory items will have a kf_Item_ID, and therefore each Inventory_Set item in your portal will be related to one and only one Item from Items_InventorySet.
In the portal, you'll have a few fields from Inventory_Set. You'll make sure that Item, SN, and Cost come from Items_InventorySet. Turn off portal filtering until you see the data that you want to see. THEN worry about that part.