Report using a partial join

[jansenw]

I hope this is under the correct topic.

I have a company and contact table, not all companies have contacts.

CompanyA Contact1

CompanyA Contact2

CompanyB

CompanyC Contact1

CompanyC Contact3

CompanyC Contact4

CompanyD

etc... (This is essentially a partial join of Company to Contact).

What's the best way to print a report on all companies with their contacts (if any)? Using sliding portals is awkward and limited.

[bcooney]

Not to avoid the question...but have you considered storing Companies and Contacts in the SAME table, "Parties" or "People." Have a look at Productive Computing's CRM product for how this would play out: Core

Also, check out this thread. Subtype/Supertype

[Fenton]

Another method, which is alluded to in the Subtype/Supertype discussion, is one discovered by David Graham (an extension of earlier methods of record creation), which is to have 3 tables, Companies, People and Contacts. Every company or person has a 1-to-1 record match with Contacts. This allows you to see and use them separately when appropriate, List views, etc., but the same when needed, for things like reports (or invoices).

Only the fields which are unique to the type are in the separate tables. It requires a few more table occurrences, and most of the data would be in a related table. But often the flexibility is worth that small tradeoff in reduced speed.

BTW, David wrote much of that part of the Core while he was at Productive Computing. He moved on to his own independent company (which you can see in this modified version of his example file of the technique. Some additions are mine (extra join tables for navigation). I don't know what the latest version of the Core uses internally; but I imagine they have them both in the same table, and use a join table.

Either way works, it mostly depends on whether you really want them to have their own tables and layouts most of the time, and only occasionally want to use them as one thing. In which case David's method is useful.

[P.S. If you do a search over the last year, for me as the poster, with "David Graham" in the post, you will find some longish discussions of the method.]

Contact_Type_fej.fp7.zip

Edited February 22, 2009 by Guest
P.S.

[jansenw]

This is very helpful.

Thanks!

[jansenw]

I have worked out a solution for my Company Directory report by loading a Directory table to simulate a partial join:

TABLE: Company

FIELDS: CompanyID, cCountPersons(calc field = count of persons in Company_Person portal),…

C-1, 2

C-2, 0

C-3, 1

C-4, 1

C-5, 0

TABLE: Person

FIELDS: PersonID,…

P-1

P-2

P-3

P-4

TABLE: Company_Person (Portal of Company, data entered by users)

FIELDS: CompanyID, PersonID,…

C-1, P-1

C-1, P-2

C-3, P-3

C-4, P-4

NEW TABLE: Directory (with relationships to Company, Person & Company_Person table occurrences)

Fields: Serial#, Company, Person…

PROCESS: When a report button is pressed…

1. Delete all records in Directory

2. Load all Company_Person rows into Directory

3. Load all Company rows with cCountPersons where cCountPersons = 0

LOADED TABLE: Directory

FIELD: Serial#, CompanyID, PersonID,…

1, C-1, P-1

2, C-1, P-2

3, C-3, P-3

4, C-4, P-4

5, C-2

6, C-5

I can now use the Directory table on a report to simulate partially joined data.

SELECT *

FROM Company C, Company_Person CP

WHERE C.CompanyID *= CP.CompanyID

Edited February 25, 2009 by Guest