script to omit records where field value is zero

[yankeer0se]

Im trying to create a report that shows all unpaid invoices. I was
trying to find all records that have a balance that is not equal to zero
and have tried few different approaches, but i am not getting the
desired result. Any help would be greatly appreciated.

[MartieH]

 Enter Find Mode (do not pause)

Set Field - your field - "== 0"

Omit Record

Perform Find

 

hth,

Martie

[LaRetta]

You need to be careful with number field because empty does not equal 0. If any record is null, it will not be found. Three ways to protect: 1) use two requests looking for < 0 and then > 0, 2) use two requests searching for = and 0 and then omitting or 3) force a result in your total so it is never empty.

If never empty, search for single 0 and then Show Omitted. ==0 isn't needed on number field. :-)

[yankeer0se]

It is a locked calculation field, so it will never be empty.  Could you show me how the script would read?  I guess Im just a little stuck at the syntax.  I am a long time access user, queries were my crutch. 

[LaRetta]

What do you mean the calc is locked? Is the calculation ever empty?

Yes we will help once we know. Can you paste the calc? I usually make number return a number or zero ... that might be easier here. Also see here for recent example how to make multiple find requests

http://fmforums.com/forum/topic/87685-script-for-omitting-more-than-one-parameter/

:^)

[yankeer0se]

Thanks for the link.

 

I added:

 

New Record/Request

Set Field [PO Table::Commission Due; "0"]

Omit Record

 

Worked like a charm.

[LaRetta]

Very good!  If Commission Due is truly a number field, you do not need to wrap in quotes which wastes an evaluation.

 

Also again I caution that if that calculation can ever NOT produce a number or a 0 then it will not be included in what you omit (your find will fail to produce accurate results).  Does every field referenced in the calculation always have a value?  If they are all empty, your calculation will usually be empty as well unless you've modified the calculation to account for it.  Just something to watch for

[yankeer0se]

Yes it is a number field.  The field calculates amount due minus any payments or discounts.  The field is locked and guarded against any data entry, so there can only be a zero or a number (amount due)

 

I will remove the quotes though.   Thanks so much for your help.