2 Replies Latest reply on Jul 11, 2012 1:39 AM by mario.furer

    Automated "Valid to" date calculated from "Valid from"

    mario.furer

      Title

      Automated "Valid to" date calculated from "Valid from"

      Post

      Hi

      I need help building a calculation in a table. The table contains conditions related to Products. Over time, conditions (such as retail price etc) change and in order to filter the conditions for further use, I set up date fields "Valid from" and "Valid to".

      Instead of having the user type in a "Valid to" date, I would like to automate the entry by an IF calculation. If a record is the most recent one, the "Valid to" date should be 31.12.2999. If a new record for the same item is created, the "Valid to" date of the older record should change to the same date as entered in "Valid from" of the new record.

      Important is, that the "Valid to" date is calculated from the next higher record, not the newest one.

      Who can tell me how to build this calculation?

      Thanks!

        • 1. Re: Automated "Valid to" date calculated from "Valid from"
          philmodjunk

          Put a button on your layout for creating a new condition record for the same product:

          Set Field [Conditions::ValidTo ; Get ( CurrentDate ) ]
          Set Variable [$ValidFrom ; value: Conditions::ValidTo ]
          Duplicate Record
          Set Field[ Conditions::ValidFrom ; $ValidFrom]
          Set FIeld [Conditions::ValidTo ; Date ( 12 ; 31 ; 2999 ) ]

          Or use:

          Show Custom Dialog ["On what date will this new record go into effect?" ] // Use Conditions::ValidTo the input field
          If [ Get ( LastMessageChoice = 1 // "OK" was clicked ]
            Set Variable [$ValidFrom ; value: Conditions::ValidTo ]
             Duplicate Record
             Set Field[ Conditions::ValidFrom ; $ValidFrom]
             Set FIeld [Conditions::ValidTo ; Date ( 12 ; 31 ; 2999 ) ]
          End IF

          Note: even when display and/or location settings specify DD/MM/YYYY date formats, the parameters for the date function must be in the order shown in this script: Date ( MM ; DD ; YYYY ).

          • 2. Re: Automated "Valid to" date calculated from "Valid from"
            mario.furer

            Thanks. I haven't thought of a button Embarassed