0

Hi, How do I check if the current Year has changed?( I want to reset a counter on the first record of the new year)

let i := last((select Projects).'P#');

let lDate := last((select Projects).'Created on');

if i = null or year(today()) > year(lDate) then

  i := 1000;

  'P#' := i + 1

else

  'P#' := i + 1

end

4 replies

null
    • Fred
    • 3 yrs ago
    • Reported - view

    Maybe this is something that will work for you. I have this basic formula in a button:

     

    let LDate := last(((select Projects) order by 'Created on').'Created on'); <-- finds the most recent record with the order by, just in case
    if year(today()) > year(LDate) then <--if the year of today is greater than the year of the last record then it sets the P# to 1000
    'P#' := 1000
    else
    let LNum := max((select Projects where year('Created on') = year(today())).'P#'); <--If you are in the current year, it finds the largest P# value
    'P#' := LNum + 1 <--then sets the current P# to 1 greater than the max of P#
    end

     

    You could then put it in the trigger on create so it figures out your P#. I originally had the else part check for a null P#, but that should never happen, so I took it out. 

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi Fred and Rocco

     

    The problem here is that if you put this in a Trigger on create then LDate will always equal Today(). Trigger on create includes the record that has just been created.

     

    Regards John

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Count the number of projects created this year, and add that to a base number

    let a := cnt(select Projects where year('Created on') = year(today()));
    'P#' := 999 + a

     

    Regards John

    • Rocco.1
    • 3 yrs ago
    • Reported - view

    Thanks Guys

    this is what I came up with:

    if not 'Project #' then

                    let y := year(today());

                    let n := count(select Projects where year('Created on') = y) + 1000;

                    let yy := format('Created on', "YY");

                    'Project #' := yy + "-" + n

    end

Login to reply

Content aside

Categories

View all topics
  • 3 yrs agoLast active
  • 4Replies
  • 442Views