4 Replies Latest reply on Dec 11, 2013 2:09 PM by SteveKeiser

    Self relationship table occurrence



      Self relationship table occurrence


           I have three separate tables which share some information. I have all employees in a contacts table. All sales reps are in the employees table. To simplify making changes, I added fields for Sales Rep and Employee to the Contacts table, and global fields with values to equal the value for Sales Rep and Employee thinking I could use table occurrences with self relationships to separate out a list of sales reps and employees that can be pulled into various other tables when needed and to use in value lists. The problem is I can't figure out how to do this. 

        • 1. Re: Self relationship table occurrence

                    I have all employees in a contacts table. All sales reps are in the employees table.

               How's that again? wink Do you mean that employees and Sales Reps are both stored in the same table, contacts?


                    The problem is I can't figure out how to do this.

               Hmm and I can't really figure out exactly what you want to do. I can read your description a number of different ways and come up with a number of very different things.

               A conditional value list for selecting contacts can limit the list to just employees, just sales reps or it can be selectable where a user selects Sales Reps and the value list lists Sales Reps and if they select Employees, they get a list of just employees.

               A layout can be set up with some scripted support to only list employees or to only list Sales reps. (Or you can give the user something to click to switch between them.)

               A portal could display only  one group or the other or the group shown can be selectable.


          • 2. Re: Self relationship table occurrence

                 Sorry I wasn't clear--I'll try again. My database has tables to keep track of rentals, sales, purchases, contacts, documents, etc. The rental, sales, and purchase tables each have related tables which contain either sales reps or employees. The employees (and therefore reps) are also in the contacts table. If a new sales rep is added, there are three tables into which the change needs to be made. I wanted to make all of this information flow out of the contacts table, so changes in employees and/or sales reps only need to be made in one place. The main problem is that I cannot figure out how to make a value list that includes only the sales reps from the contact table. That is the reason I set up the redundant tables.

                 I thought I could make up a contacts table occurrence and relate it to the main contacts table occurrence via this type of relationship: _[ContactsID_pk = ContactsID_pk AND Rep = gRep] where Rep is tagged with "1" when the contact is a sales rep for our company, and gRep is a calculation that equals "1" and is global.  However, when I use this table from which to pull values for the value list, all of the contacts show up in the value list, rather than being limited to just the sales reps.

                 I guess I still don't completely understand how to do table occurrences for the purpose of queries.

            • 3. Re: Self relationship table occurrence

                   You'd need a relationship to an occurrence of Contacts that is set up like this:

                   MainTable::constSalesRep = Contacts::ContactType

                   If "SalesRep" is the text in ContactType for every Contacts record of a SalesRep, then constSalesRep can be defined as a calculation field with:


                   as its expression and with Text selected as the Result type.

                   You could also use Option 1 of the following tutorial to get a value list of SalesReps:

                   Forum Tutorial: Custom Value List?

              • 4. Re: Self relationship table occurrence

                     It works! Thanks so much. I used option 2 of the tutorial. When you see how it works, it looks so simple--hard to see why I couldn't figure it out without help. I never thought of option 1 however. That is a simpler solution that I will be able to remember easier.