2 Replies Latest reply on Apr 3, 2013 7:24 PM by flybynight

    Multiple relationships / subgroups

    flybynight

      Having one of those "banging my head against the desk" moments…

       

      Our main table "JobTickets" is a child to our "Contacts" table, which is a child to "Companies".

      In JobTickets, I have a field _fk_Contact_ID that is linked to Contacts::__pk_Contact_ID. On the layout, the users pulls down a pop-up menu of _fk_Contact_ID which is linked a value list of Contacts::__pk_Contact_ID also/only displaying records from Contacts::Company_Contact which is a calc field concatenating the Company and Contact name.

      This part works just fine.

       

      All of my ID fields are hidden from the user, showing only human-readable values.

       

      There is a Contacts::Groups field with checkboxes for Designer, Vendor, and Sales. Right now, I am trying to create another field for the user to select the Designer (we may bill to the main client - the primary relationship - but it is valuable to know where the files came from, and we have a couple dozen contacts flagged as Designers that we work with often).

      So I have set up another TO from JobTickets::_fk_Designer_ID to Contacts::__pk_Contact_ID and also matching the Contacts::Groups field to JobTickets::zg_Designer (a Global field with the contents "Designer").

      I have a value list set up to use values from Contacts::__pk_Contact_ID also/only showing values from Contacts::ContactName (a calc field concatenating First and Last) and set to Include only related values from my new JobTickets_CONTACTS__Designer TO. I also want to display the primary phone and email for the designer so we can easily contact them with questions.

      On my pop-up menu, I'm getting "<no values defined>" showing up.

       

      I have also tried setting the value list to use fields from JobTickets_CONTACTS__Designer and include only related values starting from "JobTickets" - but I still get the same results.

       

      I'm sure I'm missing something easy. If someone could shine some light, I'd be most appreciative.

       

      Thanks!

      -Shawn

        • 1. Re: Multiple relationships / subgroups
          oldal41

          Too tired to follow your problem but the frustration came through clear. Without more to go on why not a self join that link the child back to it's parent.

           

          Line Items table to invoice table (invoice has A Line Items Portal ) a self join of the Line Items Table lets me view all Invoice Line Items, and not just the active Line Item.

           

          Just a thought from someone that should be in bed asleep.

           

          Anothe idea:

           

          You said: "JobTickets" is a child to our "Contacts" table, which is a child to "Companies"."

           

          Contacts

           

          TO - Contacts_JobTickets

           

          TO - Contacts_Companies

           

          TO - sellfJoin_Contacts ( ICompany = JobTicket )

          • 2. Re: Multiple relationships / subgroups
            flybynight

            An update…

             

            My relationship is working fine.

            I think the problem is with my value list.

            I put of a duplicate of the _fk_Designer_ID field on my layout and just made it an edit box. If I put an ID # into that field of a contact whose Group field has "Designer" checked, their phone number and email pop into the corresponding fields, and their name even shows up in the original instance of the _fk_Designer_ID field that is set to be a pop-up menu with the value list assigned. But if I click on that pop-up menu, only the current name appears. If I clear the field or put in an ID # that is not a contact with "Designer" checked, then the phone and email fields go blank, and the pop-up menu just displays a number. Clicking on it now, shows the "<no values defined>" message.

             

            Here is a screen shot of my Value List setup:

            ValueList.png

            If anyone can see what I'm doing wrong, please point it out. Thanks a heap!

            -Shawn