Cascading Portals, Seemingly simple but doesn't work! Arg
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,