find results include records from 2 tables - want distinct (unique) results

[Thomas2syllables]

In SQL, I would select records from two different tables based on a similar field in each table, but SQL has the "distinct" limiter, so that you only get one record when there are multiple records with the same identifying info.

First of all, is there any online resource that explains how to do in FileMaker all the powerful things that SQL can do? If I find such a resource, I think I will be able to do just about anything in FileMaker.

Second, let me give you the brief description of why I want to put such a find together. I have 2 types of records stored in those 2 tables, and the similar field I want to use to find records is "Record requires editing". I want to create one long list of records that need editing and print it off for the person doing the editing (it's on their database, not mine--mine is a copy of theirs).

Thanks in advance--I know that "find results include records from 2 tables - want distinct (unique) results" is a long subject line, but I thought it might help for others searching the forums to be as descriptive as possible--I've been searching the forums for an answer to this question, and while someone may have already asked and answered it, I can't find it.

[Paul de Halle]

Hi Thomas

Unfortunately that sort of functionality is not directly possible within the FileMaker engine. FileMaker can only display data in a standard list from one table, it can't merge data from multiple tables like SQL can (oh i wish it could).

The way around it would be to create a third table (like a temp table - only they don't exist either) and merge your data using scripts to then display for your report.

Alternatively if you have Server Advanced you could query it through SQL using a SQL reporting tool to produce this report externally.

Hope that helps.

Cheers

Paul

[Fenton]

In FileMaker you are required to do more messing about with the IDs themselves. Here is how I would do more or less what you want to do. First, I would have a copy of their database; it could just be a "clone" (no records), so that I could set up the relationships needed, and target their tables and layouts.

I would Find the records in your file, one table at a time, then either export or import them into a little file, just the IDs. Then I'd create a relationship from this little file to their file, based on ID. Then create a button, with Go To Related Record, using the (•) Match all records in current found set, using the appropriate layout of their file.

Send them the file. They click the button. It finds the records for them. Clunky perhaps, but it works. If you wanted to narrow the found set down to some criteria for "unique" you would need to do that before importing the IDs.

I sometimes use this if I've located some problem records, to make it easy for a client to locate them on his copy of the files.

[Thomas2syllables]

Thank you Paul de Halle and Fenton,

I think what I may do is use a mySQL backend and an OpenOffice front end.

~Thomas