8 Replies Latest reply on Aug 28, 2015 8:36 PM by erolst

    Filter Layout Help

    leeputnam

      Hopefully I can explain this with out confusing too may people.

       

      I have 3 tables.

      Suppliers --> Manufactures --> Products

       

      • Manufacturers have one supplier.
      • Products have one manufacturer.

       

      I need to create a script that goes to a layout and show all the PRODUCTS the SUPPLIER has. For some reason I can not get my 'Preform Find' to only show related records, it just ends up showing all PRODUCTS.

        • 1. Re: Filter Layout Help
          simonpcooper

          Depending on your needs you may want to view the products in a portal from a supplier layout.

           

          If you are using a list layout make sure the source table is PRODUCTS and then filter to a given supplier.

           

          If this hasn't answered your question then I may have misunderstood - if so please send a screenshot of your relationship table.

          • 2. Re: Filter Layout Help
            erolst

            I need to create a script that goes to a layout and show all the PRODUCTS the SUPPLIER has

             

            Which supplier? The one on whose record you are? In that case, let your script do

             

            Go to Related Record [ from: Product ( aProductLayout ) ; matching records only ; match current only ]

             

            leeputnam wrote:

            For some reason I can not get my 'Preform Find' to only show related records, it just ends up showing all PRODUCTS.

            All products are related to one supplier; you need to specify which one you mean.

            • 3. Re: Filter Layout Help
              leeputnam

              Thanks guys, these are both good solutions!

               

              Sorry, there are multiple suppliers. The goal is to have a print out to email to the supplier when stock is low.

              • 4. Re: Filter Layout Help
                thurmes

                The best way to do this not through a find, but instead using Go To Related Record. In the GTRR dialog, specify that you wish to 'show only related records', presumably 'Match current record only'. That presupposes that you've set up your relationships so that they really do point Suppliers --> Manufacturers --> Products

                If you really want to do a find, you'd want to do it on manufacturers (which I presume is a field in Products); From the Supplier you would List the Manufacturers, then go to the products layout and step through the list, entering the manufacturer's name on a find request, then doing a find. But, as I said, that's the hard way.

                • 5. Re: Filter Layout Help
                  leeputnam

                  Once I go to the related record, is it possible to filter those records down based on a true or false field?

                   

                  I.E.

                  Go to Related Record [ from: Product ( aProductLayout ) ; matching records only ; match current only ]


                  Filter Records if 'Products::Re_Order' = "TRUE"



                  • 6. Re: Filter Layout Help
                    erolst

                    “Filtering a layout” means performing a Find.

                     

                    Since the GTRR already results in a found set, you need to Constrain, rather than Find:

                     

                    If [ isEmpty ( Product::primaryKey ) ]

                      Exit Script

                    # [ no related products for this supplier ]

                    End If

                    GtRR [ as above ]

                    Enter Find Mode [ no pause ]

                    Set Field [ Products::Re_Order ; "True" ]

                    Set Error Capture

                    Constrain Found Set [ no restore ]

                    …etc

                    • 7. Re: Filter Layout Help
                      leeputnam

                      You rock! I don't know how you do it, but you navigate through my lack of terminology and give me a solution!

                       

                      If you don't mind explaining this to me, I would greatly appreciate it.

                      "If [ isEmpty ( Product::primaryKey ) ]

                        Exit Script

                      # [ no related products for this supplier ]

                      End If"

                       

                      Why?

                      • 8. Re: Filter Layout Help
                        erolst

                        leeputnam wrote:

                        If you don't mind explaining this to me, I would greatly appreciate it.


                        If [ isEmpty ( Product::primaryKey ) ]

                          Exit Script

                          # [ no related products for this supplier ]

                        End If

                         

                        The expression

                         

                        RelatedTable::nonEmptyField

                         

                        returns a value from the first related record; if it doesn't return anything, there aren't any related records, ergo there isn't anything to go to, and you want to exit the script.

                         

                        (The best candidates for nonEmptyField are either the primary key that every table should have, or the foreign key for the table you're coming from, which by definition must have a value for a record to be related.)

                         

                        You could also perform the GtRR, then check for an error; either way, it's best practice (in any programming language) to have your code trap for errors and act accordingly.