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

    Calculation Snag

    RickWhitelaw

      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
          philmodjunk
            

          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
            RickWhitelaw
              

            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