How to do outer joins?

[PattyRod]

I am new to Filemaker Pro. I have some experience with Access. I just started a new job that requires me to develop a database with FM. I have the database created, but need to do an outer join for reporting purposes. Can I do an outer join in FM?

Thanks!

Patty R

[mr_vodka]

FileMaker does not have outer joins per se.

You will have to use work arounds but with limitations...

See if you can use some of this.

http://fmforums.com/forum/showtopic.php?tid/184230

http://fmforums.com/forum/showpost.php?post/204343

[PattyRod]

Thanks John!

I read through your links. The custom function is close, but unfortunately does the opposite of what I need. Maybe I should just post my problem and not ask about an outer join. Perhaps a solution can be found.

Table A is a list of contracts that specifies data files we are entitled to receive. Table B lists all the data files we have in house. Table A and B are related by contract number. I would like for a report to show all contracts in table A and all data files in table B, but to also show any contracts in table A that do not have data files in table B.

Ex:

Contract 1 Data File A

Contract 1 Data File B

Contract 2 Data File C

Contract 3 Data File D

Contract 4 (no data files)

Contract 5 (no data files)

Any thoughts?

Appreciate your help..

Patty

[The Shadow]

FileMaker is always providing *only* left-outer joins. The table the layout is based on always being the left table.

You perform a find on that table for the subset of records you want, all contacts found will be shown in your report regardless of whether they have a related data file or not.

Hope this helps.

[comment]

If the number of data files per contract is relatively small, you could produce the report from a list layout of Contracts by placing a portal to Data Files in the body. Let the portal have enough rows to accommodate the greatest possible number of files, and set it to slide up when printing.

A better solution would be to collect the list of related files into a calculation field in Contacts. In version 8.5 and higher, you could use the List(0 function for this - otherwise you need to either define a value list of related files and use ValueListItems() to get it, or use a custom function.

Either way, your report will look more like this than your example:

Contract 1

• Data File A

• Data File B

Contract 2

• Data File C

Contract 3

• Data File D

Contract 4

Contract 5