How to search for "non existant" on a referenced Table

[Doug183]

End Result:

With two related tables, I want to search one ( Searching for 5 digit codes) and then find in a second related table all the records that don't exist (empty name).  

 

CURRENT FMP DATABASE/TABLES

Table 1 = NEO LIST.    Field:  NeoID. (7500 records)

Table 2 = CONTENTS.    Field:  FilmNameID.  ViewerName.   (6000 records)

NeoID and FilmNameID are related.

 

LAYOUT

Layout = NEO and references NEO LIST.  Its also displays CONTENTS::FilmNameID and  CONTENTS::ViewerName.

Search for a 5 digit NeoID, and the layout correctly shows 4000 results of NEO ID, CONTENTS::FilmNameID and  CONTENTS::ViewerName. 

 

FAILED SEARCH

I tried 2 different searches:

1)  Perform a find for all NeoID that are 5 digits  AND have empty CONTENTS::ViewerName.  So I  type ##### in NeoID  AND =  in  CONTENTS::ViewerName  (equal sign for empty).  However I get back no results.  

2)  Perform a find and type ##### in NeoID.  I get back 4000 results.  Then I type = in  CONTENTS::ViewerName  (equal sign for empty) and Requests >> Constrain Found Set.

3) Show all records in NEO.  Type = in  CONTENTS::ViewerName and I get no results.  

 

I think the issue is the that CONTENTS::ViewerName doesn't exist as opposed to empty.  Any suggestions?

 

[Ocean West]

in table 2 do a find for * in a field it will find all occurrences with something present.

Choose Show Omitted (click the pie chart icon next to the record navigation) then in your other field in table 1 search for your code choose constrain found set from the records menu.