1 Reply Latest reply on Mar 6, 2012 8:57 AM by sicoa

    Clairvoyance portal filtering across two separate relationships

    alas

      I have a situation where I am using a portal to find and select (via a scripted button) an item out of a list (the script copies the item's ID to a field on another portal on the display.

       

      I have the following tables:

       

      Screen

      Screen_Flag

      Flag

       

      Flag is where the selection portal is pulled.

      Screen_Flag is the table connected to the portal being filled in

      Screen is the main table/display

       

      I have included a global field on Screen where the user can input words that use the Clairvoyance method to filter the portal via the relationships between Flag and Screen. I actually have gInput being used to populate up to four fields being used in the relationship with the following calcualtion:

       

      If ( IsEmpty(gInput) or IsEmpty(MiddleWords(gInput;1;1)) ;"_ALL_";MiddleWords(gInput;1;1) ) [with the 1;1 being adjusted for the second, third and fourth words]

       

      So I have Flag = cKeyNew1 and Flag = cKeyNew2, etc. as the relationship.

       

      I have a desire to "first" filter the selection portal (Flag) by a field in another table (Primary). There is a field in that table called Color, and I would like the selection portal to be filtered where Flag::Color = Primary::Color.

       

      I can make it work by creating a calculated field on Screen where Screen::Color = Primary::Color and then add Color = Color in the relationship between Flag and Screen.

       

      But is this the best way? Does having that calculation run the risk of slowing down my system (right now there are not many items so it is not running slow, but I am concerned). Should the calculation be stored or unstored?

       

      Or is there a way to get the selection portal to filter by Color from the Primary table too?

       

      I will note that Primary is already related to Screen for purposes of displaying some of the data from Primary on the Screen layout.

        • 1. Re: Clairvoyance portal filtering across two separate relationships
          sicoa

          A few quick tips if you are thinking of having a few hundred thousand records:

           

          1. Avoid bringing data to the portal from a relatiosnhip to a table with many Calculation fields
          2. Try to use stored calulated values...rather than unstored
          3. Avoid conditional formatting in the portal
          4. Test your solution by adding a few hundred thousand dummy records...you'll soon find out if it will perform as expected!