6 Replies Latest reply on Jul 13, 2016 8:34 AM by HB

    show related data of same table

    HB

      Hi

       

      How can I show related data that exists inside same table.

       

      Ie

      ProductTable

       

      MainNum1=A1 SecNum1=zzz  Loc1=There

      MainNum2=zzz SecNum2=67  Loc2=Here

      MainNum3=A1 SecNum3=yy  Loc3=Here

      MainNum4=yy SecNum4=88  Loc4=Here

      How can I find all the MainNum's whose SecNum's location is not There

       

      ie If table were only the 4 records above

      I would like to perform find that shows just the first record because zzz's location is not "There"

       

      Thanks in advance

        • 1. Re: show related data of same table
          keywords

          Do you want to relate records to one another, or just see a particular set? One is a relationship task, the other simply a Find.

          • 2. Re: show related data of same table
            mikebeargie

            You can do it in a simple script:

             

            Enter Find Mode [ no pause ]

            Set Field [ ProductTable::Loc1 ; "==Here" ]

            New Record/Request/Page

            Set Field [ ProductTable::Loc2 ; "==Here" ]

            New Record/Request/Page

            Set Field [ ProductTable::Loc3 ; "==Here" ]

            New Record/Request/Page

            Set Field [ ProductTable::Loc4 ; "==Here" ]

            etc...

            Perform Find

             

            Note the "==" search variable refers to an exact match of your field.

             

            Tie that script to a button or menu action and you can run it whenever you need.

            • 3. Re: show related data of same table
              mikebeargie

              Looks like just a find to me.

              • 4. Re: show related data of same table
                HB

                I am sorry - I guess I wasn't clear enough...I was trying to show 4 records with 3 different fields (not 12 as Mike read), one named MainNum, one named SecNum and one named Loc...my apologies for being unclear.

                 

                Re "keywords" - I want to show all the records of the table whose SecNum is elsewhere in the same table listed as a MainNum having its Loc entry not equal "There".

                 

                Hope that is clear!

                • 5. Re: show related data of same table
                  mikebeargie

                  This is a little clearer, you can make a self join to the ProductTable based on:

                  ProductTable::SecNum = ProductTableSELF::MainNum

                   

                  Once you establish that relationship, then the find is simply:

                  Enter Find Mode [ no pause ]

                  Set Field [ ProductTableSELF::MainNum ; "*" ]

                  New Record/Request/Page

                  Set Field [ ProductTableSELF::Loc ; "There" ]

                  Omit Record

                  Perform Find

                   

                  That would find for all records (*) with a related MainNum value, and OMIT any records where the related MainNum Loc = "There"

                   

                  You might have to adjust a bit (I forget if finding via related data would work exactly like this, but it's close).

                  • 6. Re: show related data of same table
                    HB

                    Thanks Mike - I read your post & got it to work