Forums

Forum Home

Support

Foxfire! 8+ Support

Relationship ed...

Relationship editor / Linking tables

1/19/2010 10:18:18 AM

manhaveh

23 posts

www.jackson-lloyd.com

Relationship editor / Linking tables

If I have table C linked to table B which links to table A and this has been working fine for what I have been doing so far, how do I link table A to table C?

The system tells me that I cannot have a table as both a parent and a child.

I guess it would be possible to just work with table B and find in there the pieces out of A & C, but I don't really need any data out of B.

Your thoughts will be most appreciated.

Hans

1/20/2010 2:38:32 AM

AndrewM

145 posts

www.aksel.com

Re: Relationship editor / Linking tables

What system is telling you you cannot have a table as both a parent and a child?

Is there a direct link between A to C or is it always through B?

When you build a report that uses data items from A and C, Foxfire! will create the join using the relationship from A -> B -> C, even though no data is being used from B.

However, if by adding B, you are going to get multiple records, then you might want to consider re-aliasing "C".

All this A, B , and C stuff might be a little confusing. So if you can tell me a little about your data, we can likely get to the bottom of it - but let me try and use a sample here.

Let's say that A are Customers, B are Orders and C are Salespeople.
Customers have a dedicated sales person (C), however, a single Order to a customer also has a Sales person who may or may not be the same as the dedicated sales person.

Some real data:
Customer: ACME has a dedicated sales person of John Doe.
Customer ACME has Order 123 through John Doe.
Customer ACME has Order 124 through Jane Doe, who is another sales person.

If you imagine your joins, they would very much like the A,B,C scenario above. Customers are linked to Orders. Customers are linked to Sales People. Orders are also linked to Sales People.

If I wanted to see Customers and their dedicated sales people, I would only expect to see:
ACME : John Doe

But if Foxfire! included the Orders join, I would see:
ACME: John Doe
ACME: Jane Doe

So how do you deal with this?

Approach 1: Views
A View is essentially a particular set of relationships and data items that exclude others. So the "Customers" view would only include the join between Customers and Sales People and the "Orders" view would NOT include that join but only the one between Customer -> Orders -> Sales. When the user builds a report, they would choose the view most appropriate to the system.

To create a view, you need to use the "Exclude Relationship From these Views" - found under the Advanced Join options.

Another nice benefit of Views is that you can exclude data items from specific views as well. In the above Customers view, you might hide all of the Orders data items so users can't even try to include them in reports when they choose the Customers view.

This approach is described in the Foxfire! Demo Use of Views Data set.

Approach 2: Multiple Aliases

An Alias is simply a name used to describe a table. You can use multiple aliases for the same table if they serve two different purposes. In our example above, the SalesPerson table serves two purposes: one to identify the dedicated Customer Sales person and another to identify the sales person for a given order. If you first imported your SalesPerson table and linked it to the Orders table, import it again and name it CustSales. When adding relationships, you can create a relationship between Customers and CustSales.

Using this approach, users don't have to know which view to choose. However, you will increase the number of data items in your system. My recommendation would be to name the data items for the CustSales so they appear as part of the Customer table so it's clearer for users which data item to choose. Example:

Customer Name -> (comes from) Customers (table)
Customer SalesPerson Name -> CustSales
Order No -> Orders
Order SalesPerson Name -> SalesPerson

This approach is used in the Foxfire! Sample Data Set with the aliases of PURCH_BY and SOLD_BY, both of which point to the sales people table.

Hope this makes sense. Let me know if not.

1/20/2010 7:28:09 AM

manhaveh

23 posts

www.jackson-lloyd.com

Re: Relationship editor / Linking tables

Foxfire told me the parent/child<->child/parent problem when I was wanting to create the "new" relationship.

I used A, B & C to make it a general statement, a lot shorter than what I had written before I posted my inquiry :-) I also was hoping to gain understanding from re-expressing it myself. Like the quadratic formula where I would just plug in the numbers.

I will study your reply and see what to do next. One thing I was afraid of is that I didn't want Foxfire to replace my prior relationship definitions with my new (possibly faulty) one. Everything is working just fine, except for this new reporting request, didn't want to mess up a good thing.

Page 1 of 1

Support

Foxfire! 8+ Support

Relationship ed...