2 Replies Latest reply on May 12, 2016 10:50 AM by lfnunley

    Select record from a related table of related table to the current layout table

    lfnunley

      I apologize for the concluded title, but I do not yet have enough knowledge of filemaker to phrase my question correctly.

       

      I am currently working on a database of items which have to be shipped out. I have a table for items, let's call it 'i'. I have a table of shipment rounds, called 's', a table of boxes which have a fk for its shipment round called 'b', and finally a join table for putting the items in boxes, let's call it j.

       

      I am working on a layout which uses the join table. I already have completed layouts for viewing the boxes and shipment rounds once items have been added. It is possible for our employees to mark an item to be shipped before we know when or what box it will be shipped in. For this I create a join table entry with 'null' as its box key.

       

      I would like to have a dialog from the join table which allows a selection of the shipping round, then shows only the boxes in that shipping round. The user will then select the box and it will be set and the box in the join record.

       

      My question is how would I create such a view, where the user from the join table layout can select a shipment round and then box, keeping in mind that the structure is   i---<j>---b>---s.

       

      Thank you for your help in advance and let me know if I can clarify anything further.

        • 1. Re: Select record from a related table of related table to the current layout table
          erolst

          lfnunley wrote:

          I have a table for items, let's call it 'i' […] shipment rounds […] called 's', […] called 'b' […] let's call it j.

          Why not call them Items, Shipments, Boxes and ItemsInBoxes? What's to gain?

           

          Anyway, IIUC you can create a global field in the join table, create a value list with all shipping rounds (1. field: id, 2. field: something human readable; show second only), use that to format the global field, then create a relationship

           

          JoinTable::gShippingRound_pk = Boxes_forSelectedShippingRound::ShippingRound_fk

           

          so that selecting a shipping round id will relate to all included boxes, which you can display in a portal for selection.

          1 of 1 people found this helpful
          • 2. Re: Select record from a related table of related table to the current layout table
            lfnunley

            erolst wrote:

             

            lfnunley wrote:

            I have a table for items, let's call it 'i' […] shipment rounds […] called 's', […] called 'b' […] let's call it j.

            Why not call them Items, Shipments, Boxes and ItemsInBoxes? What's to gain?

            Fair enough!

             

             

            So I didn't mention that filemaker is synced to our Microsoft SQL database, which doesn't allow global variables.

             

            That aside, I think your idea would work! I could create a field in the join table for the pickup date and then let the user choose the date, which would then populate the portal with boxes from that date. Thank you for your help!