4 Replies Latest reply on Dec 13, 2013 9:18 AM by JosephSchueller_1

    Lookup not working, can't figure out why.



      Lookup not working, can't figure out why.


           Hello, thanks for checking out my post.


           Here's the setup:

           Table "Awards"                  Table "Roles 2" 




           AwardID is the serial/key, of course, and TagParentAward is simply a calculation that results in "X" in either table based on certain criteria. It is calculated in each table, respectively. 


           The relationship should look up 3 fields in the Roles 2 table: PI_Email, DeptAdminEmail, and ContactEmail. It's only working for the PI_Email field, and I cannot, for the life of me, see why.

           I've double and triple checked that the record match criteria are valid and present in all appropriate records. Basically, the look ups are identical, but only one of them is functioning. Any ideas?



        • 1. Re: Lookup not working, can't figure out why.

               Just to clarify... Are the fields that you are doing the lookups on in the Awards table?

               If so have you tried only using the pk --->fk fields..  does this work?

               Also could you post your lookup calc so we can help..

          • 2. Re: Lookup not working, can't figure out why.

                 Hi Jason,


                 Thanks for the reply. I realized that my problem is simple-  the lookup is not by calc, but by field option. Thus, it's going to the first matching record, which does not necessarily have anything in the specified field. For example, I have a record in Roles 2 with data in the"PI_Email" field, but because there are 2 other records in the table that match based on the relationship criteria, the data isn't being pulled to the appropriate record in Awards as it's not the "first matching record".

                 That being said, I have not experimented with doing lookups by calculation. Any tips on how to started?


            • 3. Re: Lookup not working, can't figure out why.

                   You'll encounter the same issue whether you use the "looked up value" or "calculation" auto-enter options. It would appear that you need a different relationship that matches to the correct record.

                   We might be able to suggest something if you describe your data and how you need this to work in more detail. The key detail being, what data in the looked up data identifies the specific record as the correct one from which to look up this information?

              • 4. Re: Lookup not working, can't figure out why.

                     Basically I'm trying to lookup (for insertion into a single record in Awards) three separate instances from the Roles 2 table without using any more table occurrences. It's probably a bad idea. A portal won't work, I need all the email addresses to be part of the same record.


                     I'm just going to add more occurrences of Roles 2. Thanks anyway!