8 Replies Latest reply on Oct 8, 2014 11:12 AM by philmodjunk

    Relookup Not Working

    MichaelHart

      Title

      Relookup Not Working

      Post

      This should be simple but I'm having trouble getting Lookup fields to work.

      I have 2 tables (Contacts and Artists) each has it's unique ID-field (auto-enter serial-number, no-modification) and each has a corresponding Lookup ID field (number, modifiable) pointing to the other's ID field, in the Lookup dialogue. The tables are related as follows: Contacts::Contact_ID = Artists::Contact_ID and Artists::Artist_ID = Contacts::Artist_ID and Contacts::Lastname = Artists::Lastname and Contacts::Firstname = Artists::Firstname. 

      I want to use the Lookups in each table to lookup the ID values in the other. When I do a Records/Relookup, no data is exchanged. The blank fields remain blank and no records are updated.

      I've double-checked for spelling differences, extra spaces, etc., but found none. I read all the posts on Lookups and nothing there has helped.

        • 1. Re: Relookup Not Working
          philmodjunk

          Contacts::Contact_ID = Artists::Contact_ID and
           Contacts::Artist_ID = Artists::Artist_ID  and
          Contacts::Lastname = Artists::Lastname and
          Contacts::Firstname = Artists::Firstname.

          Please explain that relationship. Why do you need to match by 4 different pairs of fields in one relationship?

          All you need is for one of those fields to not match and you don't have data to look up.

          You should be able to link records just by one of the two pairs of ID fields used here.

          And you must have the cursor in one of the above fields before selecting ReLookUp if you are doing this manually.

          • 2. Re: Relookup Not Working
            MichaelHart

            Whenever I used only-just Contacts::Contact_ID = Artists::Contact_ID OR only-just Artists::Artist_ID = Contacts::Artist_ID, I got "There are no fields that look up values based on the field "Artist_ID" (or conversely "Contact_ID").

            So, I then added the Contacts::Artist_ID = Artists::Artist_ID match, thinking that would satisfy Relookup but still nothing updated. Whenever I did the Relookup (with cursor in appropriate field) I would get "In the 250 records that you are browsing copy new values for every field that has a lookup based on this field "Artists_ID?".

            At that point, I added the lastname-match with similar results and then I add the firstname-match in a vain attempt to get it to work but still, no go. That's the condition I came to this post with.

            I tried deleting the Relationship(s), entirely, and started from scratch with just the single-pairs of the above ID-match fields but, I still get "There are no fields that look up values based on the field "Artist_ID?".

            • 3. Re: Relookup Not Working
              MichaelHart

              Is it possible that this problem is due to the difference of leading zeros in the ID fields?

              • 4. Re: Relookup Not Working
                philmodjunk

                There are any number of possible reasons. To start, the relationship you currently have is incorrect. I would guess that no records in the one table match to any of the records in the other via the relationship you are describing here. In at least one field for each possible matchup, the data is not identical and you have no matching records. To repeat, you should only need to match records by one or the other ID fields, but that assumes that you actually have that relationship defined and the data in the ID fields truly matches.

                When you establish this relationship:

                Contacts::ArtistID = Artists::ArtistID

                If you put the cursor in Contacts::ARtistID, select Relookup and then get "There are no fields that look up values based on the field "Artist_ID" ", it means that there is no field in Contacts that has a looked up value field option that copies data from a table occurrence named Artists--the table occurrence name used in the above relationship.

                You need to describe your data and what you are trying to accomplish with this relookup operation.

                • 5. Re: Relookup Not Working
                  MichaelHart

                  Contacts::ArtistID = Artists::ArtistID - results in "There are no fields that look up values based on the field "Artist_ID".

                  The tables are: Contacts - Artists - Titles - Impressions - Packets - Drawers - Frames - Vendors - Invoices

                  I want a lookup in Contacts to lookup/update/sync with Artists:ArtistID so, I created Contacts::ArtistID as a lookup field so whenever I add a new artist record, a new Contact record is also created and vice-versa (Contacts has all address, phone info, etc.. Artists has only Names and Gallery Affiliations and is related to Titles::ArtistID). I have a Group field, in Contacts, which define each contact as Artist or Gallery or Collector or Patron or Family or Friend, etc..

                  Contacts::ArtistID(lookup) = Artists:ArtistID     (I've also tried Contacts::ContacID = Artists::ContactID)

                  Artists::ArtistID = Titles::ArtistID

                  Titles::TitleID = Impressions::TitleID

                  Impressions::ImpID = Packet::ImpID

                  Packets::PktID = Drawers::PktID  (Frames and Invoices are related to Impressions with ImpID, Vendors to Frames and Invoices)

                  I've included a screenshot.

                  • 6. Re: Relookup Not Working
                    philmodjunk

                    That doesn't really make sense.

                    If your relationship matches records by ArtistID, you can't use this same relationship to look up a value into artistID as you have to have matching values in the two artist ID fields before anything can be looked up using that relationship and if you have the matching values, Artist ID does not have to look up a value as it already has the matching value. (This is a bit like using email from your computer to tell the internet service provider that your internet service is out of commission. wink)

                    I created Contacts::ArtistID as a lookup field so whenever I add a new artist record, a new Contact record is also created

                    Even if the data look up worked, this would not create any new records. It only copies data from an existing record on the other side of the relationship into the already existing record on this side of the relationship.

                    It looks like you need to rethink your basic design here.

                    • 7. Re: Relookup Not Working
                      MichaelHart

                      Hmmm... I guess that puts me back to square-one using the First and Last names as the match-fields but... "You should be able to link records just by one of the two pairs of ID fields used here", you said.

                      "Even if the data look up worked, this would not create any new records. It only copies data from an existing record on the other side of the relationship into the already existing record on this side of the relationship." - I see it as a means to an end. A way to populate the Contacts:ContactID into the Artists table (or vice-versa). It is my understanding that once a Lookup has been performed, the looked-up values become static so, could I not then change the Lookup field(s) into a non-lookup auto-serial-number-field which could then be used as a proper ID match-field and utilize the "create new records via this relationship" option?

                      • 8. Re: Relookup Not Working
                        philmodjunk

                        I'm not sure where to start with a response. I don't really know exactly how your tables are set up, how they are currently related and what changes you are trying to make. I do realize your project sounds familiar, but am not about to assume that I can remember the details correctly.

                        From here, I see no need for both an artistID and a contactID field. One or the other should suffice to link your records. First Name and Last Name fields should only exist in your contacts table and not be used as match fields in relationships.