Dynamic Formula to change QRP form header based on +ve/-ve

[NBVC]

Hi,

I have a QRP form (using Gupta Report Builder V. 1.5.1). This QRP is an Invoice form for our ERP system. When an Invoice total is negative, we call this a Credit Memo, and we dynamically change the form title between "INVOICE" and "CREDIT MEMO" with formula:

Code: Select all

StrIFF(INVOICE_TOTAL ,'CREDIT MEMO' , 'INVOICE', 'INVOICE' )

Where INVOICE_TOTAL is an Input Total variable I created, that is a running total of the Invoice line values.

This worked well up until now, when we discovered that it doesn't work well with multi-page "credit memos". It shows title of "INVOICE" on all the pages where the total cumulative Invoice amount is positive. Once the cumulative amount is negative, then it shows "CREDIT MEMO" as the header of that page only.

I have the INVOICE_TOTAL set to Sum an Input Variable called "N_CUR_GRANDTOTAL" at every Page Break.

I tried other breaks, like at every CO_ID (customer ID), etc... and tried checking and unchecking the "pre-process" checkbox.... but none of these work.

I know it might difficult for you guys to diagnose since you are not running the ERP to see what variables are there.... but wondering if you had any suggestions for me to play with as far as setting and/or properties that might work.

In case you are wondering, I am running VISUAL MANUFACTURING (Version 6.3.8) ERP system.

Thanks for any advice....

[Charlie]

Hello,

By "multi-page", do you mean to say the print job has multiple invoices and credit memos in some random order/mix?

Or do you mean the print job has one credit memo that spans multiple pages?

[NBVC]

Hi Charlie,

Actually, it could be both... but, in my immediate case it is one credit memo that spans multiple pages. So if the Credit memo was 2 pages of line items, then the first page shows "INVOICE" at the top, while the second page shows "CREDIT MEMO".


Note: We do often print multiple invoices at once that can include some Credit Memos, and if any of those Credit Memo's are multi-paged then the same rules needs to apply.

[Charlie]

Report Header or Page Header?

Do you mind attaching the QRP file?

[NBVC]

It is in the page Header.

Here is the QRP. I hope this is what you want.... Remember that this is an old version 1.5.1.

[Charlie]

Let's keep in mind I'm working with TD2.1SP1 ...

I'm tempted to say that when the page header goes to print, the "INVOICE_TOTAL" function hasn't been processed at all yet when the first page starts "printing." I'm also tempted to say that when page X is "printing", the value of INVOICE_TOTAL is the value calculated on Page X - 1.

A quick check would be to copy that "flaINVOICE_TYPE" field on your "Header: CO_INVOICE_ID" and on your detail block (any line in both blocks). Check to see if on the first page flaINVOICE_TYPE shows up correctly on either or both the header and detail blocks.

Let us know what happens.

I just noticed that the line on Header: CO_INVOICE_ID forces a page break before printing. What happens if you turn that off (i.e. no page breaking)?

[NBVC]

Hi Charlie,

Thanks for your reply.... I really wish the posts wouldn't take so long to be viewable so that we can get this working without so much delay between

I tried what you asked and put the "flaINVOICE_TOTAL" in the header and detail blocks. What happened is that it showed INVOICE everywhere, and this time even in all places of the second page, including the Page Header....

When I took them out, then it reverted back to showing CREDIT MEMO on the second page.

Also, I am not able to "turn off" page break at the header block for some reason. Everytime I select None in the Behaviour tab, it just reverts back to Page Break Before...

This is frustrating...

Someone mentioned that I may have to do an extended query and assign an Input Variable to the final Invoice Total from our database and then refer to that variable in my formula... but I am hesitant, because I already have an extended query to get data from another table, and the 2 tables are not related... and as far as I know, Report Builder doesn't allow more than one extended query... or am I wrong?

[Charlie]

YES, THE MODERATION IS BRUTAL! CAN'T WE GET RID OF MODERATION AND JUST BLOCK THE BAD APPLES ???

Anyway, back to the problem.

Might not fix anything, but I have to mention: why are you restarting calculation of INVOICE_TOTAL on page break? Wouldn't it make more sense to restart calculation when you get a new CO_INVOICE_ID ??? I'd try thant, and then setup the INVOICE_TOTAL to "pre-process".

Aside: in your header: CO_INVOICE_ID, the line can't be told "None" for page break. I'd add a new line Header: CO_INVOICE_ID, move the field to that new line, and get rid of the old one.

Touch base with you again tomorrow, I guess.

[NBVC]

I agree.. that they should ban the bad apples instead of punishing the good apples! That is the way we do it in a forum I am a moderator at.

As I mentioned initially, i did try playing around with the INVOICE_TOTAL definition and tried setting the Restart Event at each CO_INVOICE_ID and checked Pre-process... but that made both pages show INVOICE at the top....

I am not sure what you mean by your aside.... what do you mean by: "move the field to that new line, and get rid of the old one"?

I tried inserting a line in the Header block with CO_INVOICE_ID and setting Total definition as you mentioned, but to no avail....

This thing is killing me!

I appreciate your assistance on this. Thanks.

[NBVC]

Hi Charlie,

Just wanted to let you know that I think I finally got it... I played around with my extended query to incorporate the table from our ERP that has the total amounts.. and created a new input variable to contain that amount, then pointed to that variable in my Invoice/credit memo formula.

It would have been nice not to have to do it that long way around, so if you have any further ideas, I'd be willing to try them.... but if not, no worries.. I think I got something I can use.

Regards
NBVC

[Charlie]

G'day,

Just back from a little vacation, and I'm a little swamped right now.

I'll try to get back to you in a bit.

(I've been working with SQLWindows and Team Developer since '93. Report Windows and Report Builder have always been pretty decent. Unless TD1.5 specifically has some quirky bugs, you should be able to do just about anything with it except make toast.)