8 Replies Latest reply on Dec 5, 2009 2:33 PM by RCaauwe

    Can Join fields be calculated fields?

    RCaauwe

      Title

      Can Join fields be calculated fields?

      Post

      I have two tables joined (=) with a field called N_Number.  In the first table, This number is like this: 1234B.  The second table it looks like this: N1234B.  So...to make the number in the first table the same, I created a calculated field called Edited N_Number, with the calculation being "N"& N_Number. 

       

      My join fields then are: Table 1 -  Edited N_Number, Table 2 - N Number.  I use this join to get the owners name from Table 2.

       

      The thing that puzzles me is that in  most cases, it works.  However there are some cases where it does not obtain the owners name.

      I can't see any difference between a successful join and one that fails.  There are not duplicate N Numbers in either Table 1 or Table 2.

       

      Is using a calculated field causing the problem? Is there a better way to do this?

       

                                                

        • 1. Re: Can Join fields be calculated fields?
          comment_1
            

          If it works in most cases, then most likely the problem is with the data in those cases where it doesn't - a trailing space is a typical culprit.

           

          Note:

          The correct term for a field used in a relationship is matchfield. Matchfields can be calculation fields, as long as they are stored (it's a bit more complicated than that, but it should suffice for now).

          • 2. Re: Can Join fields be calculated fields?
            RCaauwe
               OK, I'll check on those things.  I did try to store the the calculated field but it wouldn't let me. -Not sure why.
            • 3. Re: Can Join fields be calculated fields?
              comment_1
                

              RCaauwe wrote:
              I did try to store the the calculated field but it wouldn't let me. -Not sure why.

              Now you got me worried: if the calculation references only the N_Number field in the same table, there's no reason why it cannot be stored. Is N_Number itself a calculation?

               

              BTW, make sure that all fields involved are of type Text (or return a result of type Text) - since neither "1234B" nor "N1234B" are numbers.


              • 4. Re: Can Join fields be calculated fields?
                RCaauwe
                  

                The whole object I am trying to accomplish is to identify owners in the FAA Registry who are not members of our association (Malibu/Mirage Owners & Pilots Association),

                 

                Let me clarify by identifying the actual table names and field names:  Table 1 = FAA Registry  Table 2 = MemberInfo Table.

                 

                In the FAA Registry table there is a field called N_Number which is a text field, not calculated.  Example: 1234B.

                There is also a field I added to this table called N Number Edited, which is a calculated field.  The calculation is "N"&N_Number.  (I did this to get the N in front of the 1234B, so it will then be N1234B as an example.)

                 

                The field N Number Edited is the matching field to match the N Number field in the MemberInfo Table.

                 

                In the FAA Registry Table layout I added a calculated field called Member Name.  The calculation is: Memberinfo Table::LFName.  I made sure the Result is Text.  N_Number is also Text.

                 

                Now, if I try to store Member Name, I get this error message: "The calculation "Member Name" cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage".

                • 5. Re: Can Join fields be calculated fields?
                  comment_1
                     OK, that makes sense. The Member Name field is irrelevant here, because it is not a matchfield. IOW, this field uses the relationship, the relationship does not use it. Your matchfields are MemberInfo Table::N Number and FAA Registry::N Number Edited, and hopefully the latter is a stored calculation.

                  BTW, you don't really need the Member Name field in the FAA Registry Table: you could simply place the LFName field on the layout of FAA Registry Table.
                  • 6. Re: Can Join fields be calculated fields?
                    RCaauwe
                      

                    Thanks to all your help, I seem to have it working now.  There are still a few records that are not finding a match when there should be one, but I did find 2 of them that had a trailing space.  Once I removed the space, they worked.

                     

                    I don't want to go through 1137 records one at a time looking for trailing spaces.  Can I trim these fields to automatically remove the trailing spaces?  They are variable lengths.

                    • 7. Re: Can Join fields be calculated fields?
                      comment_1
                         If it's only a few records, you could simply find them. Anyway, have a look at the Trim() function.
                      • 8. Re: Can Join fields be calculated fields?
                        RCaauwe
                          

                        Yeah, I already figured that one out, although I made a lot of extra work for myself...I forgot to check Result is: Text, so it converted all those records to 1.00.  I had to reload the database, but all is well now.

                         

                        Thanks again for all your help.