How to find "Null" value?

[emannering]

I have recently switched from MS Access to Filemaker Pro 8.5. Our office is now using both PCs and Macs. In Acceess I could search for "null" values in a relational database. That is searching for those entries that were in one table, but NOT in the other table.

I have created a relational database that tracks membership for a certain organization. There are different levels of membership kept in a field called "Status" in the primary table called "Main Table". This table contains basic name, address, status, etc. There is another related table called "Dues" that stores information on membership dues, date, check no. etc. I have no problem extracting information for those who pay, but how do I find those who did not give anything, and thus have no entry in the "Dues" table during a specific time period.

Any help would be appreciated!

[Genx]

Search "==" in the field to find empty values.

FYI it's not a Null search -- null is a field that never had a value and this doesn't REALLY exist in FM.

[emannering]

Actually, it would be a field that never had a value. In MS Access it is called a "null search."

For instance, suppose I had 500 members and 350 paid dues one time. There would be 350 entries in the "dues" field of the "Dues" table. However, the other 150 members would never have given anything and therefore would not show up in the dues table. I want to be able to identify those other 150 members.

Table "Main"

• Field "Name"
  
• Field "ID"

Table "Dues"

• Field "Date"
  
• Field "Amount"
  
• Field "DuesID"

These tables are related throught the "ID" and "DuesID" fields. The 150 who never paid dues would not have their "DuesID" show up in the "Dues" Table. This is a bit simplified, because I would actually be querying for a specific date range. They may or may not have ever given, but they haven't given within the date range specified.

[Genx]

I don't really like Null Searches.. what if you mistakenly enter a value?

Anyway, scriptually, to be executed from a layout based on the Main Table:

Set Field[ Dues::Date ; StartDate & "..." & EndDate ]

Set Field[ Field::Amount ; "*" ]

Omit Multiple Records[ Get( FoundCount ) ; Perform Without Dialog]

Or manually...

Enter

3/3/07...4/4/07 into the Date Field

* into the Amount Field

--> The records returned here are all those people who made a payment between your two dates, but we want all the people who didn't match the criteria... so

Records > Omit Multiple and just enter 9999999999999 or the number of records currently being viewed... whatever.

[emannering]

Thanks, but I should have been more detailed. There are classes of members denoted by a field "Status" in the Main Table. There may actually be 3000 unique entries in the Main Table but only 500 are "Members". There may be 300 "associates', 700 "visitors", 200 "gizmos" etc. So I query on two fields: "Date" and "Status" - Which makes it more complicated. If I find 350 "members" who have paid, and then ask for those omitted I get all 1650 other names. Can I query (or whatever the FM term is!) these remaining 1650 to identify those who are "members"?

[Genx]

Thats fine:

Set Error Capture[On]



Enter Find Mode[]

Set Field[ Dues::Date ; StartDate & "..." & EndDate ]

Set Field[ Field::Amount ; "*" ]

Perform Find[]

Omit Multiple Records[ Get( FoundCount ) ; Perform Without Dialog]



Enter Find Mode[]

Set Field[ Main::Status ; "Member" ]

Constrain Found Set[]

# Might Want to Catch for error here using If[ Get(LastError) <> 0 --> I.e. no members found that didn't pay in that period]

[emannering]

Thanks - looks like that will work!

[comment]

Omit Multiple Records[ Get( FoundCount ) ; Perform Without Dialog]

Woudn't this result in an empty found set?

I would start by finding any payments made during the required period in the Dues table. Then Go to Related Record [ Match found set ] in the Members table. Show Omitted Only, and constrain by member status.

[Genx]

OOPS.... It should be Show Omitted Only not Omit Multiple.

Set Error Capture[On]



Enter Find Mode[]

Set Field[ Dues::Date ; StartDate & "..." & EndDate ]

Set Field[ Field::Amount ; "*" ]

Perform Find[]

Show Omitted Only



Enter Find Mode[]

Set Field[ Main::Status ; "Member" ]

Constrain Found Set[]



If[ Get(LastError) <> 0 ]

Show Custom Dialog[ OK ; "No results returned" ]

End If

Your method did cross my mind though, but this script would logically be executed from the members layout, given that we want to find members that haven't paid... not that it really matters.

Set Error Capture[On]



Go To Layout[Dues]

Enter Find Mode[]

Set Field[ Dues::Date ; StartDate & "..." & EndDate ]

Perform Find[]

If[ Get(FoundCount) <> 0 ]

Go To Related Record[ Members ; Members ]

Else

Set Variable[ $fail ; True ]

End If

Show Omitted Only



Enter Find Mode[]

Set Field[ Main::Status ; "Member" ]

Constrain Found Set[]



If[ $fail or Get(LastError) <> 0 ]

Go To Layout[ Member ]

Show Custom Dialog[ OK ; "No Results Returned" ]

End If

[comment]

That doesn't look right. You need to GTRR in Members right after the find in Dues, and do the switch to Omitted Only there.

[Genx]

Hi Michael,

Does it look right now?

BTW, is there any reason you would do it in your particular way over the way i chose -- Is there a speed factor in there or something?

[comment]

Yes, it should be faster, since it is searching indexed fields in their native table - instead of searching for related values.

Regarding your 'new and improved':

1. I don't think you need to include Amount = * in the search; if there's no Amount, there should not be a record.

2. I don't understand the Else part. If no Dues were found, then I think the search failed. It should go back to the Members layout and say 'No matching records were found'.

[Genx]

Regarding indexing... cool, this really in general has never crossed my mind before, so thanks.

Regarding the amount --> didn't cross my mind.

Neither script really traps for error's so i just wanted it to return to the original layout which is all the first one does.

But i've updated both the above with error capture for future reference.