jim.weeda Posted February 8, 2011 Share Posted February 8, 2011 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! Link to comment Share on other sites More sharing options...
Reid Posted February 8, 2011 Share Posted February 8, 2011 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. Link to comment Share on other sites More sharing options...
jim.weeda Posted February 8, 2011 Author Share Posted February 8, 2011 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. Link to comment Share on other sites More sharing options...
jim.weeda Posted February 8, 2011 Author Share Posted February 8, 2011 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. Link to comment Share on other sites More sharing options...
bcooney Posted February 8, 2011 Share Posted February 8, 2011 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. Link to comment Share on other sites More sharing options...
jim.weeda Posted February 8, 2011 Author Share Posted February 8, 2011 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! Link to comment Share on other sites More sharing options...
bcooney Posted February 8, 2011 Share Posted February 8, 2011 Very good catch on the Find * with Omit. Of course that saves a step. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 4825 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now