3 Replies Latest reply on Feb 29, 2012 2:40 PM by philmodjunk

    Limiting Records in Related Table



      Limiting Records in Related Table




      I'm wondering if it's possible to show only records related by a checkbox in a related layout.

      For instance, i have a products database and an order database. I'm wanting to put a products layout in the orders database but am only wanting to show items that we want to sell currently. Those items are marked with a checkbox in the products database that our marketing team keeps up to date. I really dont want a portal for hte layout in the orders system.

      Right now, all of hte products pull over and i have a constrain script to limit the records to only current items. This just isn't working for us since it always resets itself everytime you go into the layout and it's making it tough to work between more than one layout.


      If there a way that i can reltate them so that only the current items pull over to the orders?

        • 1. Re: Limiting Records in Related Table

          Not without using a portal or importing the records into a separate table.....

          I think you are using a script trigger to constrain the found set. That would seem a reasonable option. What do you mean by "reset"? There are simple ways to disable the constrain so that you can leave the layout and return without the record set being modfied by the trigger's script. Here's how I set up my layout based script trigger controlled scripts so that I can turn them off when necessary:

          If [ Not $$TriggersOff ]
              #Put script steps here
          End IF

          That way, If I need another script to execute without tripping such script triggers, is use this code:

          Set Variable [$$TriggersOff ; value: True ]

          #Put stepts here that would trip the script trigger

          Set variable [$$TriggersOff ; value: False ]


          Hmm, just thought of something a bit less drastic than importing the data into its own table. I'm not convinced that you need to, but...

          Define a table with just two fields: ProductID, Current

          Make Current a calculation field that always returns the same value entered in your check box field in Products to mark a product as currently sold.

          Define this relationship:

          CurrentProducts::ProductID = Products::ProductID AND
          CurrentProducts::Current = Products::CheckBoxField  

          If you base your layout on CurrentProducts, you can add fields from Products and you'll only see records for products currently offered for sale. You can set up a script trigger on the check box field to create or delete the matching CurrentProducts record any time a user clicks that check box field to change the status for a Products record.

          • 2. Re: Limiting Records in Related Table

            Hey Phil,


            I took the 2nd route but it's not working.


            I created a new table in orders that shows product id : Text and current status : Calculation to show "open"

            Then i created a new table relationship between the new table and my products table relating product id and hte current status on both tables.

            All of my products are still pulling over.


            I can't seem to get it to limit the records shown to only open items.

            • 3. Re: Limiting Records in Related Table

              In layout setup, does your layout refer to the new table or your original products table? "Show Records From" should specify the new table.

              How did you populate the new table? (to get things started, I'd perform a find for all "open" products on the products table then import from it into the new table.)