Jump to content

Outer join in FIlemaker Pro ??


jiehuang001

This topic is 7198 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7198 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.