Cascading Portals, Seemingly simple but doesn't work! Arg

[Sean Pinto]

Hi,

 

FMP newbie here and I apologize in advance for a long winded post.  I am creating an store signage distribution database.  Basically what I'm working on is creating orders that contain lineitems of signs.  Then each lineitem is shipped to many stores.  Here are the tables involved:

 

sign_version - has a signVersionID

store - Has a storeID

order - Has an orderID

line_item - line_items marry signVersionID and OrderID

store_shipment - store_shipment marries lineItemID and storeID

 

*See item 1 in attached screenshot*

 

Everything work great until I'm working on the Store layout where I am trying to create a set of cascading portals.  The first one would show the orders that the current store is involved in via the store->store_shipment->line_item->order chain

 

*See item 2 in attached screenshot*

 

That worked great so then I wanted to create the next portal where when you clicked on a button at the end of the portal row that would populate a gOrderID_SET global field in the store table and then show rows from store_shipment pertaining to the current store and the selected orderID in gOrderID_SET.  So I needed to create a filtering relationship I figured. The data I want to ultimately get at is in store_shipment but that table doesn't have an orderID to use in the relationship.  So I figured I could just create a calculated field in store_shipment whose expression was just line_item::orderID and I called it cOrderID.  That worked as expected when I looked at the rows in store_shipment. Next I created a new TO for store_shipment (store_shipment 2) and related store to it via store::storeID = store_shipment::storeID AND store::gOrderID_SET = store_shipment 2::cOrderID.  The noticed something was weird immediately when the store_shipment 2 side of one of the relationship didn't have the crow's foot.

 

*See item 3 in attached screenshot*

 

When I load my layout, gOrderID_SET is being set properly via my button script but nothing was happening in the bottom portal.  If I create the exact same portal using the relationship to store_shipment it shows the the correct information but not filtered.  I'm pretty sure this has something to do with me trying to create a relationship using a calculated field but I didn't really know how to do it otherwise since I need to match on the orderID field from line_item but display stuff from store_shipment.

 

If someone could point me in the right direction I would be eternally grateful cuz my head hurts from banging it against the wall for the last few hours.

 

Thanks in advance,

Sean

 

[eos]

this has something to do with me trying to create a relationship using a calculated field

 

Not per se. You can use any calculated field on the left-hand side of a relationship, and you can use stored calculated fields on its right-hand side; but since a relationship matches against an index (or indices), you cannot use an unstored field – calculated or otherwise – on the right-hand side. And since the definition of cOrderID involves a related field, it is par force unstored.

 

store_shipment - store_shipment marries lineItemID and storeID

 

Why call it store_shipment if it joins a shipment and a line item? Also, there's no need for a join table, because a store is a direct attribute of a line item a shipment …

 

Put the storeID into the Shipments table, instead of LineItems. Then your relationships are

 

Stores --< Shipments --< LineItems >-- Signs

 

which lets you see all shipments of a store directly in a portal, and by extension all line items of those shipments.

 

You can use your above scheme to select a specific shipment ID in a field that you use as a match for another relationship with line items.