3-deep portal help needed
I need some help developing a layout to track parts in products. Here's the hierarchy:
There are several products.
Each product has several assemblies.
Each assembly has numerous parts.
Each part has numerous parameters and an inconsistent number of photos.
I set up a table to hold all the products & their info (including id(PK), product number, and product name).
I set up a table to record all the assemblies & their info (including id(PK), assembly number, assembly name, and product id(FK)).
I set up a table that records each part's info (including id(PK), product id(FK), assembly id(FK)).
I set up a table that records photographs (including id(PK), part id(FK), and container for photo).
I managed to successfully set up a Layout for the parts table that shows every part and all its parameters, a portal to show the active record's photos, and a portal to a copy of the parts table that shows all the parts for the assembly to which the active record belongs. The parts list portal has a "button" setup such that clicking on the description of the part makes that part become the active record in the main view.
Since I am brand new to FileMaker, I was happy with my success (and thankful for the help provided to others on this and other forums that allowed me to find answers to my questions without having to post), and what I'd done worked as intended. I needed just one more feature: a way to show only the parts for a particular product.
So, I created a new Layout based on a "dummy" table that has only an id(PK), product id(FK), and assmbly id(FK). It has only one row, and is modified as needed for filtering parts records using a drop-down menu to select a product. I had to do that because I couldn't find a way to create a drop-down menu that wasn't associated with a table field (this does work, and will show only the relevant parts for that product).
The previous Layout is recreated as best as I could in three portals (the main part view portal (#1), the part list by assembly portal (#2), and the photo portal (#3). Portal #1 now has Previous/Next buttons (with shift-key modifiers for first/last) because the records slider at the top shows only 1 record (from the dummy table used for record filtering). So, the problems I now have:
- The part list portal (#2) doesn't update its parts list by assembly based on active record in portal #1 when I use my Previous/Next buttons until the second record. Everything is always a record behind. I'm using a script tied to onRecordLoad to set a global variable with the assembly name. The assembly name won't update until I advance to the next record, and it is then potentially not correct.
- In the part list portal (#2), I can't consistently get the selected part to become active in portal #1. Similar to problem #1 above, the global variable I set from the active record with the current part id(PK) doesn't change until after the next record change, so it's always one record behind. I suspect that, if I could solve the first problem, this problem would also be fixed.
- The photo list portal (#3) doesn't reflect the active record in portal #1. Instead, it always shows the photos from the first part (id(PK) = 1).
I have used the relationship window to tie the tables according to the related fields (PK's to FK's, and other related fields), and everything works in my first Layout.
My part info portal (#1) has the following filter:
subset = MiddleValues ( $$IDs ; $$portalrecordnumber ; 1 ) ; FilterValues ( subset ; parts::part_id ) ≠ "" )
where $$IDs is a list of all part_id for the given product and $$portalrecordnumber is the found record number of the active record. This portal also has the following Script Trigger for OnObjectEnter (it is also attached to the Layout Setup as a Script Trigger for OnRecordLoad):
Set Variable [$$IDs; Value:List(parts::part_id)] Set Variable [$$maxParts; Value:ValueCount($$IDs)] Set Variable [$$part_id; Value:parts::part_id] Refresh Window [Flush cached join results]
It looks right to me, but the third "Set Variable" doesn't cause any updates when the new record loads.
Now, after all that, if my second effort (with filtering by product) is ill-conceived, and there's a better way, I'm all for abandoning that layout and starting in a different direction. If it can be salvaged, that's great, too. Since I'm new to all this, I'm certainly open to suggestions from the experienced users.