Avatar of rvfowler2
rvfowler2
Flag for United States of America asked on

FM - How to do an Unmatched query in Filemaker Pro?

We are importing data from a DOS db that has no timestamp field and my boss wants to see what new tenants/records we have for the past week.  I have already imported data one week old in one table and another has current data, but how do you create an Unmatched Query in FM?  I entered "Unmatched Query" into FM Help as well as in EE Knowledgebase and did not come up with anything.  Thank you.
FileMaker Pro

Avatar of undefined
Last Comment
rvfowler2

8/22/2022 - Mon
Will Loving

I'm not sure exactly what you mean by "unmatched query". If you are looking for records that have no value in a field, you can simply enter an equals sign "=" in the field. Alternately, you can enter an asterisk "*" and then use the "Omit" Find Request option.

If you are dealing with setting up relationships, you can also use the "not equal to" relationship matching operator.
rvfowler2

ASKER
I need to know what records exist in one file that do not in another file, so I think your idea of "not equal to" does it.  I just need to see if it works both ways and shows ones missing as well as ones that exist but are not in the primary table.
Will Loving

Several thoughts:

- When importing the new data, include an auto-enter date or timestamp field called DateImported.

- When doing repeated imports (or batch emails or postings to a General Ledger) I will sometimes include setting a BatchNumber field in the import script for easy reference in the future.

- You may need a multi-predicate relationship, one with a cartesian "X" join, so every record is related to every other, and a second with the "not equals"
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
rvfowler2

ASKER
This began to sound familiar so I went to another db where I once set this up, but the relationship seems to be the same as the one in the attached screenprint.  However, I'm still showing all the records in my layout.  Should I just create a calc field that combines the four fields and then make them not equal to each other?

Again what I want to show is all records from the current table to the Oneweekago table that shows unmatched records.
--Unmatched.JPG
rvfowler2

ASKER
Not sure what you mean by a multi-predicate relationship.  Do you mean an X relationship added to the same Edit Relationship box or do you mean create another TO?  Also, this is a real weak spot for FM; I can create an unmatched query in MS Access in less than 60 seconds.
Will Loving

The relationship in your screen shot is a "multi-predicate" relationship, meaning that there is more than one condition, or predicate, that must be met.

The "X" or Cartesian relationship simply relates all records in one table to all records in another table. Prior to having this option, we used to simply make a "Constant" calculation field equal to "1" in both tables and relate them. Either way, I'm guessing you don't need to use it here because you have more specific predicates that are required.

I'm having trouble understanding exactly what you want to do that you can't already do with either existing relationships, a search or a GTRR combined with a Constrain. If you can upload an example file or give more details about what you are trying to do that would be helpful. I really can't parse what you mean by: "I want to show is all records from the current table to the Oneweekago table that shows unmatched records."
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
rvfowler2

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rvfowler2

ASKER
Though I figured out the calc field solution, I also like your solution of GTRR and omitting the found set, so giving credit.