HOw to do a left outer join in Filemaker Pro

Posted on 2009-02-13
Last Modified: 2012-05-06
I have 2 tables, servers & backupjobs.  They are related together by serverid.  I want to find all servers that don't have a backupjob defined yet.  As easy as this is to find out in SQL Server, I can't seem to find it in FMP.  I tried adding the backupjobid to a server layout and doing a find there but FMP doesn't return anything.  What am I missing?
Question by:Soccerman55
    LVL 28

    Assisted Solution

    all you can find is servers with a backup job, and they omit the found set to work out the ones without backupjobs. a left outerjoin is not exactly that, and fm has no real equiv, but you can normally achieve similar results a different way.
    LVL 19

    Accepted Solution

    It's important to remember that FileMaker is not a SQL-based system. FileMaker doesn't have SQL nulls, for example. FileMaker relies heavily on omitted record sets and the "go to related records" script step to do things that would be done in a very different way in a SQL system.

    To find servers that do not have a backup job, go to a layout based on the servers table that also includes the ID of the backups table on the layout.

    Do a find for any value in the backup field (this can be done by searching for a range that includes all possible values, or even simply by searching for "*" which indicates any values. Click the OMIT option when performing the find, or do the find, and then use the Show Omitted Only option. The result will be all servers that do not currently have a related backup record.

    LVL 1

    Author Closing Comment

    Thanks guys, that was what I was missing!

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
    Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now