5 Replies Latest reply on Sep 26, 2014 10:55 AM by philmodjunk

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

    SeanPinto

      Title

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

      Post

      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

      Untitled.png

        • 1. Re: Cascading Portals, Seemingly simple but doesn't work!  Arg
          nihmbrisby

          Re-writing this post after having read your case more closely.  First of all cOrderID is unstored since it's defined in the Store Shipment table but references the Line Item table.   Therefore as the child in the relationship, it cannot be the match field.

          Assuming your layout is based on the "store" TO, create a new line_item TO and connect it to the store TO like so: gOrderIDSET ------ Order id.  Now create a new store_shipment TO and relate it to the new_line item TO like so: LineItemID----LineItemID.

          Now base the cascading portal on the new store_shipment TO.  Hope that works.

          You may/will run into the issue of the portal not updating since the parent match field is a global.  A Refresh Window with Flush Joine Cache results will update the relationship but has performance penalties.  For an alternate approach read this:

          http://www.teamdf.com/weetbicks/42/ditch-those-flush-caches-use-cartesian-join-instead

          • 2. Re: Cascading Portals, Seemingly simple but doesn't work!  Arg
            SeanPinto

            Nihm,

            Thank you so much for your response. I've been killing myself over this. I actually tried what you suggested. I related my store TO to a line_item TO with store::gOrderIDSet-li2::orderID and then li2::lineItemID-ss2-lineItemID and based my portal on that.  The problem was that it gave the store_shipments for ALL the stores on the lineItem - since many store_shipments relate to one line_item. To remedy that I put a portal filter of store::storeID = ss2.storeID and that gave me the correct raw data.  Now my only problem is the ability to reach my ultimate goal - displaying on the store layout what sign versions were ordered by that store on the clicked order.  

            The signVersionID is on the line_item TO so I tried creating a sign_version TO (sv2) and connection it to my newly created li2 TO with li2::signVersionID-sv2::signVersionID.  But when I added sv2::signVersionID to the portal it gave incorrect results.  I think this is because I had created a "fork" in the relationships (see screenshot).

            I'm not sure if you're a SQL person but that's what I am most comfortable with.  To translate what I'm looking for in this portal ultimately is below.  Is there any way to program this in using ExecuteSQL or something?

            SELECT sv.desc AS signVersionDesc, ss.shipQty, ss.inStoreQty

            FROM store s

              JOIN store_shipment ss ON store.storeID = ss.storeID

              JOIN line_item li ON ss.lineItemID = li.lineItemID

              JOIN sign_version sv ON li.signVersionID = sv.signVersionID

            WHERE s.storeID = [store::storeID] AND orderID = [store::gOrderIDSet]

            Thank you again for taking your time to look at this with me, its much appreciated!

            Sean

            • 3. Re: Cascading Portals, Seemingly simple but doesn't work!  Arg
              nihmbrisby

              Try a third line item TO (li3) and relate it to ss2 like so: li3::lineItemID--------<ss2::lineitemID.  Then create a new sign_version table (sv3) and relate it to li3 like so: sv3::SignVersionID------<li3::signVersionID.  Now in the field picker choose fields from sv3 to place on the portal that you currently have to ss2.  

              It's super late so I may be talking gibberish, but I know whenever I've got an issue I want answers asaplaugh.  Basically the point is to get to your sign version data through ss2 whereas right now the data you're accessing is before ss2.  Hope that helps.

              • 4. Re: Cascading Portals, Seemingly simple but doesn't work!  Arg
                mattman

                Sean, your familiarity with SQL will actually help you in this situation. There are a few ways to get at the data. The "normal" (if that's what you want to call it) way of getting to the data is via the Relationship Graph and creating the relationships. Each connection between two TO's (Table Occurrences) is a JOIN.

                However, using ExecuteSQL, you don't have to have the occurrences existing on the graph - unless you want to display data within a portal. FileMaker's ExecuteSQL is like a SQL interpreter for the internal query engine of FileMaker.

                This means you can query any number of keys which will get what you want to show within a portal.

                The thing to remember with FileMaker is that it has this notion of a many-to-many relationship even when using a single global field. You can have a return delimited list of keys (multi-key) within one global field which is used by the relationship which a portal uses. However many keys are in that global field will relate to the matching records.

                This means you can use ExecuteSQL to get any number of key values you want and then push those into a global field which is used in a relationship on the Relationship Graph.

                If you need to see examples of this, you can always ask for other devs to implement for you so you can learn. Just save a clone (with no data - or bogus data) and upload the file.

                I hope this helps out.

                • 5. Re: Cascading Portals, Seemingly simple but doesn't work!  Arg
                  philmodjunk

                  You'll find examples of Return Delimited Lists used as match fields--some generated by ExecuteSQL, some not, in the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.

                  Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
                  Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

                  Caulkins Consulting, Home of Adventures In FileMaking