Formula not displaying at the last record

[jbhsuman]

Version CR9. I am using a formula to count the number of unique detail records. The table contains multiple detail records of invoices so I want to only count the invoice once the invoice value has changed and only if the date value of the record = January. The formula is as follows:

 numbervar JanCnt;

if {DATA12.INVOICE} <> Next ({DATA12.INVOICE})and Month ({DATA12.DATE})= 1 then 
    JanCnt := JanCnt +1 else JanCnt := JanCnt;

The formula seems to work fine until the last record in the report. At that point the formula returns a blank where I believe the formula should display the last calculated value.

Any suggestions would be appreciated.

Thanks

Joe

 

[bdreed35]

Why don't you just a Running Total?

Set up a Running Total that uses these options:
Field to Summarize: {DATA12.INVOICE}
Type of Summary: Distinct Count
Evaluate: Use a formula. Enter "Month ({DATA12.DATE})= 1" in the editor
Reset: Never (assuming that based upon the info you provided)


~Brian

 

[Ngolem]

our problem is that when on the last record, Next() is null

so modify yur formula to the following

if onLastRecord or ({DATA12.INVOICE} <> Next ({DATA12.INVOICE})and Month ({DATA12.DATE})= 1) then
JanCnt := JanCnt +1
else
JanCnt := JanCnt;



Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[jbhsuman]

dbreed and Ngolem, thank you both for responding to my post. db, originally I did not use a running total because I make an evaluation based on the month returned from the month() function. After reading your post, I modified the formula to include similar logic to evaluate on the change of invoice i.e.:

({DATA12.INVOICE} <> Next ({DATA12.INVOICE})and Month ({DATA12.DATE})= 1)

This appears to work so I will probably scrap the forumlas.

Ng, regarding your post please correct me if I am wrong, if I use the &quot;If onlastrecord or&quot; statement, the formula will always add 1 at the last record regardless of the value returned by the month() function. Is that correct?

Shouldn't this be modified to read:

if not onlastrecord and {table.field} <> Next ({table.field}) and Month (table.field}) = 1

Thanks - Joe

 

[Ngolem]

yes...you are correct...sorry about that.

What you do depends on how you treat a last record of a group.

Actually you probably treat it the same as you do for a change in group.

then the formula should be

if (OnLastRecord or {DATA12.INVOICE} <> Next ({DATA12.INVOICE}))and Month ({DATA12.DATE})= 1 then
JanCnt := JanCnt +1
else
JanCnt := JanCnt;

I think that works better.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!