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.