5 Replies Latest reply on Feb 28, 2013 10:16 AM by philmodjunk

    Update Portal viewing table1 based on field in table 2



      Update Portal viewing table1 based on field in table 2


           Setting up a new database and trying to get everything working properly - it's been a while since I've worked with filemaker and never anything this complex (probably simple for most):

           I have the following tables:

      Show, Shop Order, ShopOrder Line Item, Equipment

           Each Show can have multiple Shop Orders, Each Shop Order has multiple Line items, Each Line Item has Data from the Equipment table.  Much like an invoice sytem based on customers, invoices, and products.

           I can create and edit new Shop Order records, and have setup layouts that have conditional value lists based on the Equipment table so that each Shop Order record displays ShopOrder Line Items based on Category and Type fields.  Shop Order has the fields OrderID (auto enter serial) and Revision# in order to keep everything straight.  Shop Order is related to Show through the fields ShowID and Showname.

           MY PROBLEM IS THIS:

           The way my business works there is only ever One current Shop Order, which undergoes many revisions.  I need to be able to display, edit, and delete, in my SHOWS:DETAIL layout, only the ShopOrder Line Items that belong to the CURRENT Shop Order.

           My Shop Order table has the field IsCurrent for each record which is a simple boolean in order to facilitate this task.  However, the portal that I setup in the SHOWS:DETAIL layout only ever displays the ShopOrder Line Items from the first created Shop Order that relates to the Show record I'm working on.  Everytime I've tried to restrict the portal via the IsCurrent field I've gotten nothing displayed at all, or some other unexpected result.

           I think I've screwed something in the relationship setup somewhere, but can't figure out where.  I'm also possibly setting up these portals wrong, but at this point I feel I've tried enough options that I believe I have more than one problem.


           Can anyone help me set up the portal on the Show record so that it only displays the ShopOrder Line Items that belong to the related Shop Order when the IsCurrent field on that Shop Order = "Yes"

           Can anyone take a look at this relationship table and let me know what I've done wrong? (Or maybe multiple things)

           Related:  Is there a way to restrict the data in the IsCurrent field so that only one Shop Order record per show can be "Yes" at a time?  I've made a rather clumsy script that will reset the previous related shop orders so IsCurrent="No" that fires when a new Shop Order is created (it also fills in the "Revision# field with current Revision#+1), but I can't figure out how to make a rule that validates the data this way, and the script itself is probably a clumsy solution.




        • 1. Re: Update Portal viewing table1 based on field in table 2

               Why does your relationship between Shows and Shop Orders match by two fields that are individually unique? (Name and Show ID#). Only SHowID# need be used to link records in the typical one to many relationship from shows to shop orders. The whow name field does not, as far as i can see, need to be defined in Shop Orders at all.

               If you are putting a portal to Shop Order LIne Items on a layout based on Shows, it would normally show all Shop Order LIne Items for all Shop Orders Records that are linked to the current Shows record.

               If I am understanding what you are doing correctly, you may have several Shop Orders records for a given show, but only one Shop Orders record "is current" at any given time.

               I'd turn the relationship around and use this relationship:


               Shows::CurrentOrderID = CurrentShopOrder::Order# UniqueID
               CurrentShopOrder::::Order# UniqueID = CurrentShopOrderLineItems::ShopOrderMatch#

               CurrentShopOrder and CurrentShopOrderLineItems are new Tutorial: What are Table Occurrences? of Shop Orders and Shop Order Line Items.

               Now a portal to CurrentShopOrderLineItems will list only the line items for the shop order record currently selected as the "current" shop order.

               See the first post of this thread for an explanation of the notation that I have used: Common Forum Relationship and Field Notations Explained



          • 2. Re: Update Portal viewing table1 based on field in table 2


                 Thanks for the quick response. To answer your first question:it's a leftover from troubleshooting an earlier problem.  You're correct, I don't need both the Name and Show ID# linking.

                 I'll give the rest of this a shot and see how far I can get. 


                 Thanks for the link to the notation - I'll go with that nomenclature from now on.  No need to reinvent a wheel.

            • 3. Re: Update Portal viewing table1 based on field in table 2

                   It's one of many options for how to name things in a database. I include that link not as a way to convince others to use it (note how it is discussed in that same thread), but as a way to help others decode what I am trying to say in as brief a fashion as possible.

              • 4. Re: Update Portal viewing table1 based on field in table 2

                     RE:naming It makes perfect sense and I'm happy to adopt something at least one other person uses rather than make up my own.

                Regarding the validation question:

                Any ideas on how to setup validation/scripting so that changing a radio button on one ShopOrder::isCurrent field to "yes" will set ShopOrder::isCurrent field to "no" on all other records in the ShopOrder table that have the same Shows::ShowID?

                I made a script that finds all records with current Shows::__pkShowID and sets ShopOrder::isCurrent to "no" before setting the last one to "yes" and exiting the loop, but this seems clumsy, not fool proof, and isn't validating.

                The goal is that for any one Show::__pkShowID There are multiple ShopOrders, but only one of them can have isCurrent to be "yes" at any one time.


                • 5. Re: Update Portal viewing table1 based on field in table 2

                       I would suggest that you remove that field from your table.

                       Use the Id field in Shows that I described previously to identify which Shop Orders record is "current". This relationship only permits a single shop order record to link to any given record in Shows.