Outer join in FIlemaker Pro ??

[jiehuang001]

I have two files. The first one is SITE.fp5, which has fileds "site_id,

site_name", and the second one is PATIENT.fp5, which has fields

"patient_id, patient_name, site_id". Basially, each PATIENT belongs to a

SITE and each SITE may have zero to many PATIENT.

Now I want to create a report on PATIENT.fp5 grouped by SITE. I first

define a relationship to make the two "site_id" in the two files related.

Everything works well except this: some site does NOT have any patient.

How can I list those sites on the patient report?? My current report only show those sites with patients. Is there a way to do outer join in Filemaker pro?

Your help would be bestly appreciated!

Jie

[RalphL]

I am guessing at this, but here goes: Create a report file. Import data from Site then import data from Patient. Sort by site. You may want to use a calculation for the line of text that appears in the report.

[jiehuang001]

Hi, Ralph:

What do you mean "create a report file", you mean another "REPORT.fp5" database file?

Right now, I am trying to create a new report/layout from the PATIENT.fp5 file.

Please let me know.

Jie

[RalphL]

Yes I meant a new file that is used to make your report. You make a script that deleats all records, then imports from Site, then imports from Patient, then sorts by site. This should give you something like:

Site 1

Site 1 Patient 1

Site 1 Patient 2

Site 1 Patient 3

Site 2

Site 3

Site 3 Patient 4

Is this what you want?

[jiehuang001]

I don't test how that might work, but i expect there should be an easier way such as an outer join...

[RalphL]

I am not familiar with the term "outer join". Where does it come from? I can't find it in any of my database books.

What I proposed was a method to get you the result I thought you wanted.

Maybe you can do it in FM7.

[-Queue-]

Ralph's suggestion would probably be the easiest way to accomplish a pseudo-outer join.

An outer join (left outer join in this case) is a way of retrieving fields from a parent and child table where the key fields match and where no child records exist for a particular parent (a common function in SQL). Your idea of a report table/file is about as close as one could get in FM, since you need all data from the related tables in one place.