Left Outer Join or Union

[peptoulcer]

Left outer join (from Wikipedia): The result of a left outer join (or simply left join) for table A and B always contains all records

of the "left" table A, even if the join-condition does not find any matching record in the "right" table B.

Example: You want a report based upon the Payments (child right) table but want to include Invoices (parent left) without payments or you want a report of Sales (child right) but you want all Sales Persons (parent left) listed even if they have no sales.

This files shows dynamic way of using one temp table to merge table data and generically label the fields, all using global variables and merge variables. This is an intermediate technique but can easily be implemented by beginners. Global variables are file specific so you cannot attach the virtual table as external file without dealing with the issue of transferring the global variables into script parameters (which is possible but not described in this demo).

Post edited: I have replaced the original version with one which explains it in more detail and I have added enhancements to the process with additional examples. I am providing this file because FileMaker does not natively provide that ability and I was receiving frequent requests for this technique from the companies I support.

Replaced file with revised v3

LeftOuterV3.zip

[peptoulcer]

Well, I found another issue. GetNth not only needs to be wrapped if it is date or number, it also needs to be wrapped if it is text. I should have thought of that and checked it but I thought text was text and wouldn't break anything.

On the existing file, Invoice Payments report, setting the check number always grabbed the first related payment's check number (same break as the others would do) unless I changed it from this:

List($$text2 ; Case(IsEmpty(GetNthRecord(payments::check#;$kount) ) ; " " ; GetNthRecord(payments::check#;$kount)))

to this:

List($$text2 ; Case(IsEmpty(GetAsText(GetNthRecord(payments::check#;$kount) )) ; " " ; GetAsText (GetNthRecord(payments::check#;$kount))) )

All of the breaks appear to be from GetNth requiring a specific type cast to identify the correct related record, at least when used within List, Min or Max so far. I also added the invoice number because I had accidentally left it out. Field replaced with V3.