5 Replies Latest reply on Feb 15, 2013 9:45 AM by RickWilson

    3-deep portal help needed



      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:

      1.           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.
      3.           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.
      5.           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:

           Let (

          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.

           Thanks all!

        • 1. Re: 3-deep portal help needed

               Is this what you have?

               Products----<Assemblies-----<Parts----<Photos (---< means "one to many")

               Products::id = assemblies::Product ID
               Assemblies::id = Parts::Assembly id
               Parts::id = Photos::Parti id

               WIthout totally restructuring your database, I'd use a layout based on Products to show the assemblies, parts and Photos for that product.

               A global field can be set up with a script for finding a specific product record on that layout. See this thread for examples: Scripted Find Examples

               A Portal to Assemblies will show all assemblies for that Product.

               A Portal to Parts will list all Parts of all assemblies for that Product.

               A Portal to Photos will list all Photos for all Parts of all assemblies for that Product.

               And there are ways to set up Master-Slave portals on such a layout such that clicking an Assembly in the assembly portal causes the Parts portal to filter down to just the parts for that assembly, with conditional formatting highlighting the selected assembly record. The photos portal could also be set up in that fashion.

               If I were to totally restructure database, I'd give very careful consideration to possibly using one data source table for all three of these Tutorial: What are Table Occurrences?: Products, Assemblies, Parts. That would allow a Product to have parts, but no assemblies and for an Assembly to consist of several smaller assemblies and allows you to have photos for a specific product or assembly as well as for individual parts.

          • 2. Re: 3-deep portal help needed

                 Thanks for the reply.

            I also had set Products::id = Parts::Product id so I could bypass the Assemblies table and directly change the record list when selecting a different Product in the drop-down list that changed the dummy table (the menu list is built from the Products records).  The dummy table is ProductFilter and has ::id, ::Product id (=Products::id), and ::Assembly id (=Assemblies::id).  The last field was an experiment to try to help with the assembly parts list portal by updating that field by script when the active record in portal #1 changed, but it didn't do much better than anything else.

                 There's also a Parts 2 table in the Relationship window so I could create the Assembly Parts list portal, with Parts 2::Assembly id = Parts::Assembly id

                 I have a very limited data set at present, so restructuring the DB (if it's the right thing to do) is easily possible.  I'm open to all suggestions, so if I did something that could be done more efficiently, I would like to know.

                 Thanks for the link.  I'll see if a global field does me more good than a global variable.



            • 3. Re: 3-deep portal help needed

                   I suggested a global field in a very specific context: That of putting the field on a layout so that you can use it to find records via a script.

                   But it is possible to get filtered portals to update without the Refresh WIndow [flush...] step if you use global fields instead of global variables. The trick is to include the global field as part of the portal's relationship using the X operator so that it doesn't directly affect the behavior of the relationship.

                   On the other hand, you can often structure your relationship such that no filtering is needed and that can eliminate the refresh issue.

                   I can't tell you whether or not using a single data source table for all parts, products and assemblies is a good  idea or even possible for your specific system. It's an approach that can provide a lot of flexibility to the function of your database, but it also assumes that the basic data you record for a part, product and assembly is very similar in each case.

              • 4. Re: 3-deep portal help needed

                     Thanks again.

                     I modified the onRecordLoad script to include updating the Globals::assembly id and Globals::parts id.  I put a text field with <<Globals::Assembly id>> in it to show its value.  I think the problem must lie in my Prev/Next buttons and scripting, since that value change and the update of Portal #2 (list of items by assembly) still lags by one button click.

                     If it's of any use, Portal #1 is where the part info resides. It is set up to show only one record at a time.  I have Prev/Next buttons with the button setup pointing to a script and an optional script parameter that checks the ActiveModifierKeys for a shift-key press to change Prev/Next to First/Last.
                     The script itself is mainly "If" and "Else If" statements to handle the 4 conditions ("First", "Prev", "Next", "Last") that set a global variable $$portalrecordnumber to the appropriate value.

                     After the global variable is set, the portal filter (the "Let" statement in my original post) changes the visible record.  Could that be where the lag problem originates?

                • 5. Re: 3-deep portal help needed

                       Well, I solved my original post problem #1 (the assembly parts list in portal #2 wasn't updating).  Since $$portalrecordnumber was the only value updating with the Prev/Next buttons, I figured I'd need something that was tied to that value.

                       I found the function GetNthRecord(fieldname;recordnumber), and used that in the portal #2 filter:

                       parts 2::assembly_id = GetNthRecord(parts::assembly_id;$$portalrecordnumber)

                       Now my assembly parts list updates with each navigation change through the parts portal (#1)!

                       Encouraged, I tried the same concept on my photos portal (#3), using photos::part_id = GetNthRecord(parts::part_id;$$portalrecordnumber), and that worked as well, and my photos update! (original post problem #3 solved!)

                       Now, I just need to figure out the navigation issue to show the correct record in partal #1 when clicking on one of the listed parts in portal #2...