FM - How to Create an "Unmatched Filter"

Is there a way or a standard way of creating an unmatched filter in FM?  It's easy in Access.  Tried creating a TO as seen in the screenprint, but it showed all records.  Now that we finally are able to import data from our DOS database into FM, I am trying to compare the manual entry of insurance Premium info with the Insurance Info that was imported from our DOS database.  Typing "unmatched" in FM Help came up empty and "matched" didn't apply.  Thanks.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

North2AlaskaSenior EngineerCommented:
The not equal should work, but you will have to think about the other conditions in the relationship.  Not equal can cover a lot of ground if done incorrectly.
Will LovingPresident, Dedication Technologies, Inc.Commented:
When you have this many conditions it's easy to get something wrong such as field type that can cause things to not work. For instance, if either InsPremium or Amount is a text field instead of a number field, it may mean that they never match. To trouble shoot a relationship like this, try removing one or two of the conditions and make sure that you are getting the expected results. Then add conditions back in. You can also test things by changes signs, using equals instead of not equals. Do you get the results your would expect?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rvfowler2Author Commented:
I'm doing something fundamentally wrong because I made two fields equal to each other that had not matching records at all and still came up with 1715 records (or all of them).
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

North2AlaskaSenior EngineerCommented:
I would suspect you got them all but one.  If you think about it and you are comparing two fields, the item on the left side of the relationship is what is being compared to the right side.  So any record that doesn't match on the right will be returned.  That is what I meant by you have to be a little more exact when you use not equal to...
Will LovingPresident, Dedication Technologies, Inc.Commented:
I assume you are using a portal or GTRR from InsPrem2 TO BldgEscs_Unmatched, meaning Parent to Child?
rvfowler2Author Commented:
Yes, see the relationship in the screenprint above, but I was viewing it in table view and just assumed that it would be limited based on the TO.  I have a long list of unmatched records.  Can I view it in something like a table where I can scroll through or have to use a portal?
Will LovingPresident, Dedication Technologies, Inc.Commented:
In order to have records filtered by a relationships, you either need to view them in a portal or you "Go to Related Record" which effectively does a search for those records that match. Looking at the records in list view will still show you all records in that table regardless of that tables relationship to another table.

So, just put a button on the starting layout - presumably a layout for the InsPrem2 table if I'm reading this correctly - and use the Go To Related Record (GTRR) script step, selecting the BldgEscs_Unmatched relationship and specifying a layout that uses that Table Occurrence name.

If you want it to sort at the same time, then edit your relationship to include a Sort. Using GTRR on a relationship that has a sort will cause the records to be sorted by the sort order.

Alternately you can use a portal on the InsPrem2 layout to BldgEscs_Unmatched
rvfowler2Author Commented:
Close, but no cigar.  When I used the RR and set the Relationships for Amount = InsPrem, it showed 344 records.  Since the total recs was 404, I assumed a not equals would give me 60 records, which sounds about right.  However, it only gave me 3.  Attached are the RR and Relationship screenprints.  Also, for what it's worth, FM really needs queries like MS Access.  This is a ridiculous way to get unmatched data; I could have did it in Access in 2 minutes.
Will LovingPresident, Dedication Technologies, Inc.Commented:
Randy - do all the fields have values in them? What's the likelihood that some of the fields - any of the fields on either side, but particularly the amount fields - are empty. Doing a match is easy because their has to be a value and has to match, but if you are doing not equal, it may treat records with values differently from records without values in the Amount field. I'm setting up a test file for myself to see if this is the case, but one way to test it is to not use amount but instead create a calculation field on the right that says:

Case( isempty( Amount) or Amount = 0 ; 1 )

an pair that with a Constant field (Just a calculation equal to 1) on the left. Try that and see what happens while I do my test here.
Will LovingPresident, Dedication Technologies, Inc.Commented:
I'm looking at your GTRR script step and it says to "Match all records in current found set". If any of your records in InsPrem2 have more than one related record in the other table - an some are equal and others are not - then the total number of records found using GTRR may not be what you expect.
Will LovingPresident, Dedication Technologies, Inc.Commented:
I've created a test file that I think replicates what you are doing and set it up with two relationships, Matched and Unmatched, as well as with GTRR buttons to show the related records. My guess is that there is something wrong with the other predicates involved beside the unequal amount.

Troubleshooting suggestion: Set the relationship to equals and find your 344 records. Then choose "Show Omitted" from the records menu to find the 60 non-matching records. Look closely at the values in the fields that you are using in your relationship and compare them with the records that did match. I think if you look at the data you'll figure out what the problem is, either in the data itself or in the relationship you've setup.

rvfowler2Author Commented:
Actually, I don't think it is finding too many recs, just not enough.  Its only showing 3 records.  I put "Match All Recs" because I thought that is what you meant when you said you can use the GTRR to find ALL recs that are related to the main table.  Again, my basic question is how do I create a list of all unmatched recs.  This is REALLY weak in FM.  We're going on 4 hours here and I could have done an unmatched query in Access in 2 minutes.
Will LovingPresident, Dedication Technologies, Inc.Commented:
"Match all records..." is correct, I wasn't thinking clearly. If you look at my example file you'll see that's what I used. To reiterate, my guess is that the issue is NOT a problem with FM's ability to do unmatched, but a problem with fields or field data you are matching on. If you look at my example file you'll see that it works perfectly when using just two predicates, a match on RecordSerial and no match on Amount. So, my deduction is that there must be a problem with the three other predicates which you are still requiring a match on. The fact that you are only finding 3 records means that the missing 57 records are NOT matching on one of the other three predicates.

Do my "Show Omitted" test above and take a look at the other 57 records that are not showing up. See if you can find a record that doesn't match on Amount but DOES match on all other criteria. My guess is that you'll only find those three records and that all the other 57 do not match on at least one of the other fields in the predicate.

An alternative method would be to create a script that has two steps:

Go to Related Records
Show Omitted

That will find all records that are unmatched, including those that not only don't match on Amount but also don't match on any of the other fields as well.
Will LovingPresident, Dedication Technologies, Inc.Commented:
Clarification on my last sentence:

"That will find all records that are unmatched, including those that not only don't match on Amount but also don't match on at least one of the other fields as well.
rvfowler2Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.