7 Replies Latest reply on Sep 17, 2015 1:14 PM by dtcgnet

    Question about filtering using self-referencing table

    StevieP

      I thought this would be easy to do but Filemaker makes it a lot harder than it needs to be.

      The situation is as follows:

      I have a table of work orders. During production we combine several work orders into one - one work order then becomes the "head work order" which contains all the job info. The other work orders have a field called "headworkorder" which contains the number of the main work order. Using a self-referencing table, this works great.

       

      Now, what I want, is to make it so I *only* see the head work orders when I push a certain button. Basically, if the orderID and the headorder are the same, then the work order = headorder.

       

      I thought a simple "Perform Quick Find" would be possible but it's not. I tried the follow Quick Find:

       

      orders::headorder = orders::orderID

       

      But Filemaker does not like this for some reason since the button doesn't work - I assume it's because orders::orderID is a variable that changes per record.

       

      I tried a Find Request which didn't work either. Anyone have a simple solution to this? It's odd how something so simple seems to be so hard to solve (at least for me). Right now I have to use a field that gets "ticked" when it's a head order and then I filter by that field but that's a sloppy way to do it.

        • 1. Re: Question about filtering using self-referencing table
          erolst

          Enter Find Mode [ no pause ]

          Set Field [ Orders::headorder ; "=" ]

          Perform Find

           

          I tried the follow Quick Find:

          orders::headorder = orders::orderID

           

          I don't think you can use expressions in Quick Find; but even then, such a record does not exist, because either the record itself is a headorder and so that field is empty, or it has the id/number of another record – its headorder

          • 2. Re: Question about filtering using self-referencing table
            StevieP

            Thank you for your reply but your idea only works if the "headorder" field is empty for the headorder itself which it isn't. With your script, it only shows unprocessed orders which haven't been assigned yet.

             

            Let me give you an example:

             

            Order 14001 has as head order 14001 - it's its own head order

            Order 14002 has as head order 14001 as well.

            Order 14003 has as head order 14001 too.

             

            So order 14001 has three sub orders (including itself) but I need a quick way to filter it so Filemaker only displays the first record where order ID = head order ID. Such a simple thing and Filemaker has no apparent solution ... .

            • 3. Re: Question about filtering using self-referencing table
              erolst

              StevieP wrote:

              Such a simple thing and Filemaker has no apparent solution ... .

               

              "Simplicity" and "Obviousness” are both in the mind of the beholder …

               

              Create a calculation field cIsNotHeadOrder as

               

              orderNo ≠ headorderNo

               

              and search for records where this field = 1 and orderNo ≠ "".

              • 4. Re: Question about filtering using self-referencing table
                StevieP

                Thank you for your reply but that's my current method:

                 

                "Right now I have to use a field that gets "ticked" when it's a head order and then I filter by that field but that's a sloppy way to do it."

                 

                I was hoping that I could do it without needing a field - I figured I was missing something.

                • 5. Re: Question about filtering using self-referencing table
                  erolst

                  Well, it's not really “sloppy”, because being a headorder is an attribute that expresses a business rule of yours; it is of course unreliable if you do it manually.

                   

                  If you want to do this without an extra field, create a self-relationship where

                   

                  Order::headorder = Order__headOrders::orderID

                   

                  show all records, go to related record Order__headOrders, related only & matching found set, using the current layout.

                  • 6. Re: Question about filtering using self-referencing table
                    nicolasd

                    Hi,

                     

                    What if you created a calculation field like this:

                    If (isempty (Order::headorder) ; order:orderID ; order::headorder)

                    And used a self join with that one then assign a go to related record to your button?

                     

                    Nicolas

                    • 7. Re: Question about filtering using self-referencing table
                      dtcgnet

                      Your button fires a script called "Find Head Order".

                       

                      The script does something like this:

                      Set Variable $$HeadOrder = Order::headorder

                      Enter Find Mode

                      Set Field Order::orderID = $$HeadOrder

                      Perform Find

                       

                      That would show you the head order for any order you're looking at. If you want to show all of the associated orders, then your search uses much the same steps, but the Set Field step in the Find looks like:

                      Set Field Order::headorder = $$HeadOrder

                       

                      Give that a shot.

                       

                      Dan