3 Replies Latest reply on Jul 31, 2013 1:20 PM by philmodjunk

    Lookup if no value...



      Lookup if no value...



           I have a table called Products and a table called Tests.  What I need to do is create a portal that shows only the products that have not been tested, that have no test number assigned.  Thus, when an employee looks at this portal they can quickly tell if a product has been tested in the history of the database.  Do I use a Lookup to determine if the productcode exists in the tests table?  If so how, what relationships etc...  Right now the relationship is  TESTS:Mixcode >--------< Products:Mixcode as I have a dropdown menu on Tests that allows the user to select a product to bring up all corresponding test. 

           Thank you!

        • 1. Re: Lookup if no value...

               Does Mixcode uniquely identify each record in Products? Tthis would then indicate this relationship:


               IF MixCode identifies a record in tests, you would have this relationship:


               You might be able to get what you need with these relationships:


               Products::anyfield X Products|All::anyfield
               Products|All::MixCode = Tests::MixCode

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               Products|All is a new Tutorial: What are Table Occurrences? of Products. The X operator matches any record in Products to All records in Products|All.

               You'd then put a portal to Products|All on your Products layout and add a portal filter to omit all records that are linked to a record in tests:

               IsEmpty ( Tests::MixCode )

          • 2. Re: Lookup if no value...


                 A similar method, which I have often used for such. I create an "opposite" calculation field; name it: _cMixCode_NOT, result: Number

                 Case ( IsEmpty ( Test::MixCode ); 1 )

                 I uncheck: [  ] "Do not evaluate if all reference fields are empty" (option at the bottom), since I want it when its empty.

                 Then I create a calculation field, in whatever table wants to "at" the above. The answer to the calculation is: 1, result: Number

                 I name it: _c1

                 Then I make a relationship from: _c1 = _cMixCode_NOT

                 That will show all that don't have MixCode. The relationship could be used for a portal. It will be pretty fast, as it's just 1=1..

                 You would need another relationship added to the above, to see the names of the Products, etc.

                 Also, the _c1 can be used for many other things. If you have a "constant" field for "1" already, you could use that.

                 P.S. The "_cMixCode_NOT" field can be shown as an obvious [x] (red) on a list layout in Tests (if you ever look at them)


            • 3. Re: Lookup if no value...

                   Glad to see that you are still around Fenton!

                   I like your idea as it can result in faster screen update when you have a lot of records involved.