6 Replies Latest reply on Jul 10, 2013 3:41 PM by philmodjunk

    Test for related record

    Kaps_1

      Title

      Test for related record

      Post

           I have done this several times and gotten this to work but I cant this time.

           I have two tables called "Companies" and "Holding Area Active Members".

           In my "Companies" table, I have two fields Companies Id, and Company.

           In my "Holding Area Active Members" table, I have a field called Company name.

           The relationship is:-

           Companies::Company = Holding Area Active Members Table:Company Name

           I am trying to identify which company names in the holding table are already in the Company Table. I have posted my script in the next post below.

            

      BNI_CRM.png

        • 1. Re: Test for related record
          philmodjunk

               Not IsEmpty ( Holding Area Active Members Table:Company Name )

               if evaluated from the context of Companies, will be true only if a related record exists.

          • 2. Re: Test for related record
            Kaps_1

                 My code is below.

                 So if the table "Company" had values "Northwood" and "My company" for the field "Company", then the field "Does the company exist in the main database ?" should equal "YES" whenever the field "Company Name" on the table "Active area Holding Area" equals either "Northwood" or "My company". However, I always seem to get "NO".

                 What am I doing wrong ? thanks

                 Kaps

                  

                  

            • 3. Re: Test for related record
              Kaps_1

                   Thanks Phil. I have changed my field to calculation and have :-

                   Does Company exist in main database = If(not IsEmpty(Companies::Company Name);"YES";"NO")

                   but always seem to get "YES" now ??

                    

              • 4. Re: Test for related record
                philmodjunk

                     What table occurrence did you select as the "context" for your calculation field? It should be the occurrence of Holding Area Active Members used in the relationship that links it to Companies.

                     If you have defined this field in companies, then you have defined it in the wrong table if you want to check for records in companies from the context of the "Holding area..." table.

                • 5. Re: Test for related record
                  Kaps_1

                       Thanks Phil - I believe I have done this - seen attached screenshot. However still getting wrong answer.

                  • 6. Re: Test for related record
                    philmodjunk

                         You should clear the "do not evaluate if all referenced fields are empty" check box. Otherwise, you'll get an empty field instead of "no".

                         Other than that, your calculation and relationships look correct. If you are still getting "yes" when you expect to see "no", better check the data in your fields to see why you have matching records when you don't expect them. One way to check that is to go your Holding Area Active Members layout and add the comapny Name field from Companies to the layout to see if you see a name in the field or an empty field.