AnsweredAssumed Answered

Self Join Relationships

Question asked by SteveKeiser on Aug 25, 2017
Latest reply on Aug 26, 2017 by philmodjunk

Is there a list of rules that help to narrow down the options in building self-relationshps? It seems like I waste a lot of time with trial and error until I figure out how to do things. In the past as I discovered things, I kept a list of my own, but then found out some of the things I thought to be true, weren't always true. Such as:


You can create a relationship with a global value, as long as it is not a calculation field. I found out this wasn't always true, such as with self-joins.


The fields used for relationships need to be indexed, except if global.


In some cases you cannot create a relationship with a calculation, but you can if you use an auto-enter calculation.


The sum of the relationships are done in the order of the of the entered matches.


So, are there rules that typically apply, but don't apply with self-joins?


What prompted all of this was I set up a some conditional value lists that limits the choices for manufacturers to the choice made for category. Then the choices for description are limited by what was chosen for manufacturers. After setting this all up, I then wanted the lists to be dynamically altered by an inactive field in each table (categories, manufacturers, and descriptions). Setting up the self-joins to further limit the choices in each category to the active records is where I started having to trial and error until I got everything working right. Whether the fixed value to test against the inactive field should be global or not, stored or not, the result of a calculation or auto entry, when you have to include the primary key or not, took some time to get right.


Also, it took me a while to realize that the layout I was using to test the changes wasn't always responding to the changes made. Sometimes I had to quit the database and re-open before the changes were effective. So I set up a "refresh" button to use for the testing, which helped a lot, but found it didn't always provide the correct results. So that is another question.


Of course, rather than a set of rules, if anyone can help me to better understand the "why"s, that is the most helpful. I'm obviously learning as I go, so appreciate any efforts to educate.