Script to omit blank records

[Leather Knight]

I need to create a script that will look at the field "Date" and if it is empty, then it will omit all those records and only show the records that have data. I am using this script in a report so if the data is not in the specified field, that record will not show up in the report. Then upon hitting the continue button it will return to the original layout showing all records. Any advice??

Thanks

[Ugo DI LUCA]

Hi,

This report could be done in a new window. Then by closing that window, you'd get back to the original foundset.

You know how to omit these blank records, don't you ?

Marvellous illusion indeed :

[Lee Smith]

Look at the Topic just below yours Link, with almost the identical question.

Once you have run the Find, and Printed your report, just add the Show All Records Script Step.

If you want to preserve a Found Set, then Ugo's New window option, offers one way to do this.

HTH

Lee

[LaRetta]

If your new found set should become your main window data-set (if you want to then forget about your original set) then close your new main window, rename your NEW window to the original window and you are in business. And if you've switched to a Find layout in the new window, you can switch to your original layout (all under a Freeze Window) and your Users will see YOUR brand a magic! If no records are returned (in the new window) you can simply close it and maintain your original set.

If you need assistance in envisioning and walking through this just let us know. But it will help for you to be specific on what your rules will be, ie, what to do if records found or not and whether you wish to maintain the original record set while working on a new found set in a new window.

UPDATE: Kevin, I don't think it is right to leave you without a bit of vision to go on so picture this pseudo-script:

Set Variable [ $origWindowName ; Get ( WindowName ) ]

Commit Records/Requests

New Window [] ... duplicates your current window and record set

Set Error Capture [ ON ]

Perform Find [ thatDateField ; * ]

If [ not Get ( FoundCount ) ]

[color:red]Show Custom Dialog [ OK ; "No records found" ]

Close Window

Else

... you have a new record set without blank dates

... if you want this to become your new record-set

Close Window [ $origWindowName ]

Set Window Title [ current window ; $origWindowName ]

End If

BTW ... a script to omit blank dates is the same as a script finding records with something in the dates. Is one faster than the other? I speed tested it back when 7 first came out and found no difference when using global *. There WAS a difference if you were searching for more specific criteria. I don't know if (in more current versions) there is speed difference). You can change it to Perform Find [ find request = then click Omit ] and it works the same. A find request is used either way.

2nd UPDATE: I was sloppy and didn't notify the User. Notice insertion of message line in red.

Edited May 19, 2008 by Guest
Added script then sentence then red line in script. I'm a talkative bugger. :o/

[Leather Knight]

Thanks everyone. LaRetta, This example is the one closest to what I need. I tested it with a little tweeking, but for some reason "all" records are still showing up in the report including those with the date field empty. I tried show omitted only and added it, but same result. Those dang blank dated records still show up. The records do have data, but the date sold is blank...and those need to be not shown in report.

[Leather Knight]

Working on an "IF" addition, "If date = blank, omit record" hoping this will only show the records with data...I will return after work to try again.

[Leather Knight]

Ok, this is working:

Go to layout [layout name]

Allow User Abort [Off]

Sort Records

Perform Find [Date >01/01/1900]

[color:red]"this is so if any date is inserted, it will be in the report, if it is blank then it is omitted"

Enter Preview Mode [Pause]

Enter Browse Mode[]

Go to Layout...

Show All Records

Go To Record/Request/Page [First]

...

And boom...it works.

[comment]

1. Sort Records[] should come AFTER Perform Find[].

2. If using "> 01/01/1900" as the search criterion produces a different result than using "*", you need to check your data for invalid dates.

[Leather Knight]

1: my goof, fixed..lol..

2: produces a search for "*/*/2008" when I use >* as the criteria, that is why I used the 01/01/1900. Am I missing something else that will replace that date?

[comment]

Not ">*", just plain "*".

[LaRetta]

Kevin, if you end up with records with blank dates, when you search for ONLY *, then those date fields are NOT blank. Might they have a space in them? Is it for sure a date field? Put your cursor in a record with a blank date. Try using your arrow key to move the cursor forward or backward.

Also, was it a text date at one time or imported?

Hi Michael :king:

Edited May 19, 2008 by Guest
Fixed type and added sentence

[comment]

It's a date field for sure.

[LaRetta]

Indeed. And I'm wrong about it possibly having a space as well. Because a text field with a space or carriage return, that is turned into a date field (or imported into a date field) LOSES those characters anyway. Okay, I'll let you handle it, Michael. If you need my help again just holler. :content:

[David Jondreau]

Are the fields you're searching on related fields? I've found "=" doesn't work well on related finds, maybe that's true of ">" too (though quick testing belies that idea).

Anyone know why the "=" doesn't work when searching for blank records on related fields? Or is it just me?

Edited May 19, 2008 by Guest
oops, wrong in response to...

[comment]

I believe it does work, but perhaps we need to establish what "works" mean?

[LaRetta]

I considered that as well (at the last) but I decided to cut my chances and run. The reason is simple:

You are searching for any PARENT record which has related records with a date in it. It may display the result of a a blank date because it can be one of many records for that parent; some blank and some with date. You are simply seeing the first record of the relationship.

Remember you're finding the parents not the children. I scratched my heard on this one for quite some time the first time it produced blank records. So maybe you've hit it here as well, DJ.

UPDATE: I mean in Kevin's example - with it still displaying blank dates.

Edited May 19, 2008 by Guest

[David Jondreau]

Okay, revisiting it with a clear head, I see I'm mixing up "blank" records with non-existent ones. Searching for a "=" in a related field will not return records that have no children, but only those records with children that have a null value in that field. That's what's been confusing me.

Back to the thread, I think the OP's main issue, as Michael and LaRetta have already addressed, is learning how to properly construct a find, but confirming whether the field is native to the table may be worthwhile if things still aren't working right.

[Leather Knight]

LaRetta, no the field is an empty date field. It is used if an item is sold. My goal is a report of items in stock, and since it is a unique item in every case, the date field for sold would be empty til sold. By using just an "*" it still shows all records if there is any data at all in any field. It looks like using a date parameter of 01/01/1900 will suffice since that is old enough to cover any sales.

[LaRetta]

I would think it would be important to figure out WHY it breaks. It goes against all common sense and FM behavior. Something is greatly amiss. If you do not figure out why, then it will bite you in behind someday. As for me, I simply couldn't let the mystery (and chance to learn a great deal) slide by so easily.

We say again ... something is wrong with this picture. Can you provide the db, even if only back channel?

[Leather Knight]

I am looking into it, believe me, this is bugging me. Just like the newest update to v9.03 will make changing my scripts all screwy so I stay with 9.02. But it has to be found...

[LaRetta]

9.0v3 should make no differences in your scripts.

[Leather Knight]

Found the bug, the ">" was staying in place because I was forgetting it at the beginning of the scripting. I figured out the issue and not "*" works fine. I updated to 9.03 and when scripts were changed, close the app, then reopen and scripts were not modified as I did before, I returned to 9.02 and it works flawlessly. Again, maybe me, but it didn't seem right. In 02 changes stick, in 03 it goes nutz.

[Lee Smith]

Have you tried using the Script Debugger and watching what the script is doing?

It sounds like you have checked the Restore button.

HTH

Lee

[daddyplayzbass]

I've been following this thread with baited breath, hoping to solve my own 'omit blank field' problem. I'm unabashedly new at this, but am climbing up a steep learning curve.

I've been trying to write a script to produce a list of contacts with email addresses, omitting records that don't contain data. Using the stock Email script as a template, I stumbled across this:

Using the 'Perform Find [Restore]' script, under 'Specify', I set the Action to 'Omit', the Field to 'Email' and the Criteria to '=='. This produced a list of all contacts whose email address was not blank. Victory. I think.

My question is this: Is this the right solution? In 'Find' view, the 'Symbols' button defines '==' as producing a 'field content match'. To me, that suggests it produces a list of duplicate entries. Therefore, my solution should eliminate all blanks because they're identical to the other blanks. However, I fear it will also eliminate any contacts who share an email address, ie. [email protected].

Are my suspicions correct? If so, is there another way? I've been wracking my brains.

BD

[comment]

the 'Symbols' button defines '==' as producing a 'field content match'. To me, that suggests it produces a list of duplicate entries.

No, it actually means that field contents must match exactly the specified search string. Since the string is empty, it finds all records where the field is empty.

You could achieve the same result by searching for * without omitting.

[daddyplayzbass]

thanks.

I tried the * search, but with no success. I'll play around with it some more. I'm used to MS Access which has a handy filter function, but is tedious for building tables and queries. Also, it's not available for Mac.

BD

[comment]

What do you mean "with no success"? Does it find empty records? Does it omit records with anything in the field?

[daddyplayzbass]

My mistake. I don't know why it wasn't working before, but when I went and changed the Action to 'Find' and the Criteria to '*' it worked just great. As they say, there's always more than one way to skin a turtle. Thanks for the lead.

BD

[mr_vodka]

Isnt it usually cat? :confused:

[daddyplayzbass]

Thanks for the tip. Any idea why Safari keeps crashing every time I try to open the White Paper? Is it available anywhere else?

BD

[mr_vodka]

I dunno... Have you just tried to save it locall isntead of trying to open it?

http://www.foundationdbs.com/Downloads/WhitePaperForFMPNovices.pdf

[daddyplayzbass]

got it. thanks

BD