2 Replies Latest reply on Apr 13, 2009 5:23 PM by RickWhitelaw

# Calculation Snag

### Title

Calculation Snag

### Post

Hi,

I've been using a calculation meant to insert a number into a field after I "close off" a date in a schedule. The field has 10 repetitions. The formula works fine except for repetition 10, which is returning "0". BTW the "rep-1" is there because Choices are numbered 0,1,2 etc. and repetitions 1,2,3 etc. Ideas?

Let ( rep=Get ( CalculationRepetitionNumber );Case(GetRepetition ( CLOSED ; rep)="Closed";"-"&Count ( Choose ( rep-1 ; SN_SERVICE1::SUB_SH;SN_SERVICE2::SUB_SH;SN_SERVICE3::SUB_SH;SN_SERVICE4::SUB_SH;SN_SERVICE5::SUB_SH;SN_SERVICE6::SUB_SH;SN_SERVICE7::SUB_SH;SN_SERVICE8::SUB_SH;SN_SERVICE9::SUB_SH;SN_SERVICE10::SUB_SH) )&" -";"" ))

RW

• ###### 1. Re: Calculation Snag

RickWhitelaw wrote:

Hi,

I've been using a calculation meant to insert a number into a field after I "close off" a date in a schedule. The field has 10 repetitions. The formula works fine except for repetition 10, which is returning "0". BTW the "rep-1" is there because Choices are numbered 0,1,2 etc. and repetitions 1,2,3 etc. Ideas?

Let ( rep=Get ( CalculationRepetitionNumber );

IF(GetRepetition ( CLOSED ; rep)="Closed";

"-" & Count ( Choose ( rep-1 ; SN_SERVICE1::SUB_SH; SN_SERVICE2::SUB_SH; SN_SERVICE3::SUB_SH; SN_SERVICE4::SUB_SH; SN_SERVICE5::SUB_SH;

SN_SERVICE6::SUB_SH; SN_SERVICE7::SUB_SH; SN_SERVICE8::SUB_SH; SN_SERVICE9::SUB_SH; SN_SERVICE10::SUB_SH) ) & "-"; "" ))

RW

This is definitely not your typical calculation! I've taken the liberty of rewriting and reformatting it a bit in my efforts to decode it. Since you have only one Boolean clause in your case function, "GetRepetition ( CLOSED ; rep)="Closed"; ", an If function will produce the same results as Case.

It would help to know what relationships you've set up. I'm guessing here that the Count function is counting the number of nonempty records related to the current record while the choose function is selecting which relationship to use in performing the count. That suggests that count(SN_service10::SUB_CH) = 0 from what you report. If so, I'd take a look at the relationship definition for the Table Occurrence SN_Service10 and double check to see what data is present in the key fields for those records.

I could be way off base, since I may not be correctly interpreting the structure of your database.

The complexity of this calculation suggests that you might be better off replacing your repeating field with a table of related values.

• ###### 2. Re: Calculation Snag

Phil,

Thank you for pointing out the obvious! No sarcasm intended. I mean it. There was indeed a difference in the relevant relationship  for field "SN_services10" and I had overlooked it. Although the main field in question is a repeating field, the others, SN_services1,2,3, etc. are not. In the process of (necessarily) setting up 10 fields I had made a simple mistake in the relationship. I guess I had never needed to close off Service 10 before so I hadn't noticed it. I tend to use repeating fields for display purposes only as I don't find them "worth it" for other purposes. This one needed a relatively complex calculation to display the data properly. Thanks for your help.

RW