Deciphering a formula to determine if value is positive/negative/zero

[NBVC]

Hi,

I have a Centura Report that has a field with this formula in it:

StrIFF( StrScan( CUR_GRANDTOTAL , '(') , 'INVOICE' , 'CREDIT MEMO', 'CREDIT MEMO' )

Can someone describe exactly what it does? I can somewhat understand that it is an immediate if statement, which looks at CUR_GRANDTOTAL input and scans it for something... what is that something? (ie. what does '(') mean?).

Background:

I have an Invoice form that sometimes prints with "INVOICE" at the top and sometimes it prints with "CREDIT MEMO" but we can't really determine what gets it to decide which to print. The Grand Total is always 0 or positive, but it seems that it is looking at the first invoiced item. If that item is negative or 0 it prints "Credit Memo", otherwise it seems to print "Invoice"....but Grand Total is at the bottom of the page...

[NBVC]

I think I figured it out, it looks for an opening parenthesis and returns the position....

but it doesn't seem to work properly... any possible reason?

[NBVC]

Ok, let's put it this way...

If I want to say If an input item number is greater than or equal to 0, return "Invoice" else return "Credit Memo"  How would I write that formula...

Here is one of my failed attempts.

StrIFF( StrToNumber(CUR_GRANDTOTAL ), '>=0' , 'INVOICE' , 'CREDIT MEMO', 'CREDIT MEMO' )

[Steve Leighton]

Try...

StrIFF(NumberIFF(CUR_GRANDTOTAL,-1,1,1),
'Credit Memo','Invoice','Invoice')

Being...

If CUR_GRANDTOTAL < 0 or NULL, NumberIFF() will return -1 to StrIFF()
If CUR_GRANDTOTAL = 0, NumberIFF will return 1 to StrIFF()
If CUR_GRANDTOTAL > 0, NumberIFF will return 1 to StrIFF()

If StrIFF receives -1, it returns 'Credit Memo'
If StrIFF receives 1, it returns 'Invoice'.

ReportWindows is juuuuuust magic.

[NBVC]

Hi Steve and thanks for the reply..

I had to convert the CUR_GRANDTOTAL (string) input item to a number using StrToNumber() to get it to accept the formula:

StrIFF(NumberIFF(StrToNumber(CUR_GRANDTOTAL),-1,1,1),'Credit Memo','Invoice','Invoice')

but still, for some odd reason, it is not giving expected results...

I have an invoice with a negative grand total, but it is outputting "INVOICE"

The CUR_GRANDTOTAL is outputting on the form as ($345.00) this is why the original formula was:
StrIFF( StrScan( CUR_GRANDTOTAL , '(') , 'INVOICE' , 'CREDIT MEMO', 'CREDIT MEMO' )

because it looked for an opening parenthesis in the text string to determine if it was negative or not.

Do you have any ideas why this is happening?

As you know (from your previous help), I have very limited knowledge in this software, and the forms were pre-engineered so I do not have access to the tables/queries used for input items.

Thanks.

[Steve Leighton]

Running in the dark without seeing the .qrp, but it could be that CUR_GRANDTOTAL holds '-$345.00' when it comes into the report, and it is the field itself that is applying the
 '( )' around it when the report is displayed - in which case the your StrIFF formula will be being applied BEFORE the field formatting - so wont work.  Check the properties of the CUR_GRANDTOTAL field itself by right clicking on it.

Happy to have a look at the qrp if you can mail it - go to to get my eMail address.

[NBVC]

Done.

Thanks.

[Steve Leighton]

I'm now thinking that the values held in your Input Variables N_CUR_GRANDTOTAL etc. are being incremented in real time - i.e. they only get (re)calculated by the calling application each time a row is fetched from the dB .
By the time the final N_CUR_GRANDTOTAL value of -345 is achieved at the end of all the SQLFetchNexts, the field holding 'Invoice' or 'Credit Memo' has already been formed on the report. In fact, this field is formed at the very start of the report when N_CUR_GRANDTOTAL is = 0.
I bet if you move this field to the bottom of the report - say into the report footer, so it is formed last, then it would come out correct.

Of course you don't want this field to be at the bottom - it needs to be at the top, so there is two ways to resolve.....

1) Modify the calling application to calculate the N_CUR_GRANDTOTAL in a separate SQL statement, before the report is called.

2) Make the report a 'Two Pass' report - so that in the first pass Report Builder calculates the totals, and in the second pass it places the calculations in appropriate fields, formats the report, processes other calculations, and creates the report.
A Two Pass Report is fairly common, but at the same time can be fairly complex - some courses dedicate a whole half day to 'Two Pass Reports' theory.

If I was to explain this theory by eMail ,we'd be here until doomsday - and so here's a link to the on-line tutorial that may help you re-structure your report.

http://support.unify.com/Docs/TDDoc/wwh ... wwhelp.htm

then navigate to 'Unify Report Builder - Business Reporting' --> 'Report Builder Tutorials' --> 'Two pass totals report tutorial'

[NBVC]

STEVE!

ThankYou very much! I think this has been solved... The Two-Pass method seems to have done the job! And on my first try All my sampling so far have shown expected results.

Just FYI, moving the field to the report footer actually seemed to have reversed the result.. it sometimes gave the opposite of what it said in the header???

Anyways, I think this is resolved.

Thanks so much for your concentrated time and effort on this. I really, really appreciate it. I love to learn new things and feel great when something I newly apply works!

Regards,
NBVC