5 Replies Latest reply on Aug 25, 2011 8:32 AM by FMNewbie

# Calculation using preceding records returns a "?"

### Title

Calculation using preceding records returns a "?"

### Post

Hi all...

I've searched the forum and I couldn't find anything on this subject so I apologize if I'm repeating something that's been asked before.

Basically, I have a calcluation field that uses data from the preceding two records.  How do I prevent it from returning a value of "?" in the first couple of records (given that there are no preceding records for it to calculate with)?

The calculation is supopsed to total meal allowances from the previous two pay periods (records) if the current pay period ends on the 15th.  Here's what I have:

If ( Day(Pay Period End) = 15 ;GetNthRecord ( Earnings Meal Allowance CURRENT ; Get(RecordNumber)-1 )+ GetNthRecord ( Earnings Meal Allowance CURRENT ; Get(RecordNumber)-2 ); 0 )

Obviously for the first and second pay period of the year, this calculation returns a "?"  Any thoughts?

• ###### 1. Re: Calculation using preceding records returns a "?"

There are two reasons for getting the "?"  One is that the data in the field is too long to be displayed as you have the field sized on the layout - in this case if you enter that field you will see the data instead of the "?".  The other reason is that the calculation is not valid due to something like a divide by zero.

• ###### 2. Re: Calculation using preceding records returns a "?"

Well... it's definitely not the field size on the layout.  It works fine for all the other records, just not the first two because the calculation is trying to total data from two records that don't exist (current record -1 and current record -2).  So I guess what I'm asking is, is there a way to put a "clause" in my IF statement such that it will perform the calucation as stated UNLESS the current record number is 1 or 2?

I'm not a pro at this and still learning so forgive me if I'm asking an obvious question.  Shoud I be using CASE instead of IF maybe??

• ###### 3. Re: Calculation using preceding records returns a "?"

You can evaluate whether you have the previous one/two records with an If:

If(

Day(date)=15 and IsValid(GetNthRecord(field; Get(RecordNumber)-1)) and IsValid(GetNthRecord(field; Get(RecordNumber)-2));

GetNthRecord(field; Get(RecordNumber)-1) + GetNthRecord(field; Get(RecordNumber)-2)

0)

or you can put different conditions with Case if you want something different from 0 in first two (but generally remember that case will evaluate sequentially, doesn't make a difference here, but may in some situations):

Case(

Day(date)=15 and IsValid(GetNthRecord(field; Get(RecordNumber)-1)) and IsValid(GetNthRecord(field; Get(RecordNumber)-2));

GetNthRecord(field; Get(RecordNumber)-1) + GetNthRecord(field; Get(RecordNumber)-2);

Day(date)≠15 and IsValid(GetNthRecord(field; Get(RecordNumber)-1)) and IsValid(GetNthRecord(field; Get(RecordNumber)-2));

0;

"")

• ###### 4. Re: Calculation using preceding records returns a "?"

In this case, your first record generates an error that is reported as a question mark because it is referencing a record numbers -1 and - 2. This error is then propogated from record to record by the getnthrecord calls throughout your found set.

change the expression to:

Case ( day (Pay Period End ) = 15 and Get ( RecordNumber ) = 1 0 ;
day ( pay period end ) = 15 and get ( RecordNumber ) = 2 ; GetNthRecord ( Earnings Meal Allowance CURRENT ; Get(RecordNumber)-1 ) ;
day ( pay period end ) = 15 ; GetNthRecord ( Earnings Meal Allowance CURRENT ; Get(RecordNumber)-1 )+
GetNthRecord ( Earnings Meal Allowance CURRENT ; Get(RecordNumber)-2 ) ;
/* else */ 0
) // case

Edit note: Deleted original If function that I had pasted in for reference while building the case function with which to replace it.

• ###### 5. Re: Calculation using preceding records returns a "?"

AHHHHHHHHH!!!!   Ha!  That works perfectly both of you.  Thank you both.