Finding Empty Fields

[jim.weeda]

I am creating a database that uploads our automated meter readings and then I want to display on a layout all of the accounts that the reading did not come through on. On my attached image, I show a field labeled KWH. This is the meter reading. I'm trying to build a script that searches for all records that are in the book BB1 and where the KWH field is blank. The KWH field is a record from a related table. Anytime I put the find in for the KWH field and run the script, I get 0 out of 101 records found. I know I'm missing something, but I'm a FileMaker newbie and I'm stuck at this point. I've tried using the = sign and even creating a match record field and finding all those and i still get blanks.

Any help?

Thank you very much!

[Reid]

I am creating a database that uploads our automated meter readings and then I want to display on a layout all of the accounts that the reading did not come through on. On my attached image, I show a field labeled KWH. This is the meter reading. I'm trying to build a script that searches for all records that are in the book BB1 and where the KWH field is blank. The KWH field is a record from a related table. Anytime I put the find in for the KWH field and run the script, I get 0 out of 101 records found. I know I'm missing something, but I'm a FileMaker newbie and I'm stuck at this point. I've tried using the = sign and even creating a match record field and finding all those and i still get blanks.

Any help?

Thank you very much!

First try doing a basic search to see if it works. In the same view that you show in your attached pic, click on "Find". In the KWH field type "=" (no quotes) and then click "Perform Find"

If you get back the records with nothing in the KWH field then you know you are able to find then using a script. It doesn't matter if the fields are related or not. FM finds them regardless.

Now, to make a script go to "Scripts" and add a new script. Depending on how you want to get there you could do...

Open New Window

Go to layout ... (Choose the one from your pic)

Perform Find ... (add a criteria. Click on KWH. Type = in the box to the right. Click add)

Close and save.

Let me know if that works.

[jim.weeda]

First try doing a basic search to see if it works. In the same view that you show in your attached pic, click on "Find". In the KWH field type "=" (no quotes) and then click "Perform Find"

If you get back the records with nothing in the KWH field then you know you are able to find then using a script. It doesn't matter if the fields are related or not. FM finds them regardless.

Now, to make a script go to "Scripts" and add a new script. Depending on how you want to get there you could do...

Open New Window

Go to layout ... (Choose the one from your pic)

Perform Find ... (add a criteria. Click on KWH. Type = in the box to the right. Click add)

Close and save.

Let me know if that works.

What you have detailed is exactly what I tried. I try the basic search and I get the little box that says, No records match this criteria. Something I just thought of...what if those fields show nothing, but had a space(s) in them? Would that disqualify them as blank fields? They are numbers stored in a text field because when I import them as numbers, they are all messed up.

Also, I just tried this and i'm editing this reply. In the view that I posted in my image, I can click on any field with something in it and edit that field, but when I try to click on a blank field, I can't add anything or even get a cursor. It's like its not even there.

Thanks.

[jim.weeda]

Ok, I guess when I start typing this stuff, my mind frees up a little and I start thinking clearly again. I figured out my problem. The ones that don't have a reading don't have a record in the other table, therefore there is no way for it to search. I think what I need to do is load all possible meters into that table, and then just do an update script to load the readings in and then I can do a search for the blank fields because the record will be there. Thanks for helping me get the wheels turning.

[bcooney]

How about Finding in the child table for KWH = * (not empty, any value). Go to related parent (match found set). Now you have all parents with a KWH reading. Then, Find Omitted. Now you have all parents without a KWH reading.

[jim.weeda]

How about Finding in the child table for KWH = * (not empty, any value). Go to related parent (match found set). Now you have all parents with a KWH reading. Then, Find Omitted. Now you have all parents without a KWH reading.

Excellent! I was making this way to complicated! I did an Omit Records with KWH criteria set to * and it gave me all the records that didn't have my KWH readings.

I added a constrain found set and set it to search only that book and voila, I have my list specific to the book and no reads!

Thanks for your help!

[bcooney]

Very good catch on the Find * with Omit. Of course that saves a step.