Thursday, November 09, 2006

Why do my 3.0 relationships not work in 6.0 or 8.0?

We recently had a Foxfire! customer who was working on upgrading their 3.0 installs to version 8.0.

Unfortunately, many of the relationships that were defined in 3.0 wouldn't return the same results in 8.0 or they would report an error. It was a real mystery until I looked at how the relationships were defined.

In 3.0 (and earlier), all relationships were handled as an inner join so it never really mattered which table was listed as being the primary table or how many possible joins you had. Customers could have Invoices and then Line Items could have Invoices and Foxfire! 3.0 would make it all work.

In 6.0 and 8.0, with Outer Join support, it wouldn't be too difficult with only three tables but relationships were also defined by their "level", a term which really meant where they appeared in the tree. For example, if you had Customers with Invoices and Line Items with Invoices, your Join Tree might actually list the Invoices table twice. When Foxfire! 6.0 comes to build a relationship, it would focus on the level to identify which join to build the main relationship. In worst case scenarios, it would never pull in the relationship between Line Items and Invoices properly.

How do you fix this? By going into the Relationship Editor and reversing the relationship in question.

A good rule of thumb is that a table should only ever be listed ONCE in the Relationship tree. If it's listed more than once, you might find yourself with a problem. It's fairly easy to change - but if you are upgrading from 3.0, keep this in mind.

Note: We'll be updating the way relationships are handled in 8.1 to allow you to easily define "lookup" tables so Foxfire! won't consider them as part of key relationships.

0 Comments:

Post a Comment

<< Home