2 Replies Latest reply on Sep 23, 2014 4:35 PM by SteveKeiser

    Table relationship question



      Table relationship question


      I want to make a list of all companies where they are not found in a contacts table. So I created a TO of CompanyTable and one for ContactsTable and related them by companyID_fk not equal to companyID_pk. I then created a portal from the ContactsTable context using this relationship. For those of you who intuitively understand table relationships, you know this did not work. I struggle with doing these query relationships and usually figure them out through trial and error, or outside help. So my questions are three: 1) Why doesn't this work? 2) How can I do what I want to do? 3) Where can I go to learn more about query type table relationships so I don't have to do everything by trial and error? I need to understand the underlying principles.

        • 1. Re: Table relationship question

          A match field cannot be empty for one thing. But with the above relationship, if you are on the record for a contact where the company ID_Fk is 3, you would see in your portal all existing company records except the company with an ID of 3--which is not what you want here.

          Instead, set up this relationship--which you likely already have:

          Companies::CompanyID_pk = Contacts::CompanyID_fk

          Then go to a list or table view layout based on Companies, enter find mode, put an Asterisk into the Contacts::CompanyID_fk field, click the option to make this an omit find and then perform the find to get a list of all companies that do not have at least one related contact.

          • 2. Re: Table relationship question

            I didn't think your suggestion worked until I noticed there was a found set. I only had fields from the queried table on the layout. When I put fields from the layout's table context on the layout, I had the information I needed. Thanks so much!