0

resetting the counter

hello, thanks for helping me the other day. i am making a table where i enter my trips, and count them monthly. i am using this code

let myID := number(Id);
let myMonth := yearmonth(Start);
cnt(select Orders where yearmonth(Start) = myMonth and number(Id) <= myID)

that i've got from the support team. however, i want to seperate my trips within the month. i have a choice field indicating the type of my trips. i want to count type a trips and type b trips seperately. is there a reset/hold command? i've tried this:

let myID := number(Id);
let myMonth := yearmonth(Start);

if text('choicefield')="type a" then
cnt(select Orders where yearmonth(Start) = myMonth and number(Id) <= myID)

end

and a second formula

let myID := number(Id);
let myMonth := yearmonth(Start);

if text('choicefield')="type b" then
cnt(select Orders where yearmonth(Start) = myMonth and number(Id) <= myID)

end

but it didn't work, it showed the same numbers with the first code, just in seperate columns

is resetting the counter possible? 

Thanks,

shn

4 replies

null
    • Leonid_Semik
    • 6 yrs ago
    • Reported - view

    Hi,

    first formula:

    ---

    let myID := number(Id);

    let myMonth := yearmonth(Start);

    cnt(select Orders where text('choicefield')="type a" and yearmonth(Start) = myMonth and number(Id) <= myID)

    ---

    and second Formula:

    ---

    let myID := number(Id);

    let myMonth := yearmonth(Start);

    cnt(select Orders where text('choicefield')="type b" and yearmonth(Start) = myMonth and number(Id) <= myID)

    ---

     

    Regards

    Leo

    • oemersahin
    • 6 yrs ago
    • Reported - view

    Hello Leo,

    You are fast as always. Sadly, i already tried this(and some other combinations  where i changed order of "or" inputs) and it didn't work, it indeed seperates the trips but counting stays the same. I had to give up on this, changed my format a little. 

    Thank you,

    shn

    • Leonid_Semik
    • 6 yrs ago
    • Reported - view

    Try replacing "where" with squered bracket:

    first formula:

    ---

    let myID := number(Id);

    let myMonth := yearmonth(Start);

    cnt(select Orders [ text('choicefield')="type a" and yearmonth(Start) = myMonth and number(Id) <= myID])

    ---

    and second Formula:

    ---

    let myID := number(Id);

    let myMonth := yearmonth(Start);

    cnt(select Orders [ text('choicefield')="type b" and yearmonth(Start) = myMonth and number(Id) <= myID])

    ---

    I do not know why, but sometimes it works better

    regards

    Leo

    • oemersahin
    • 6 yrs ago
    • Reported - view

    Hello Leo,

    Noted this, just in case. I've realised that the counting system would be very complicated since we have 2 kinds of trips and we are 3 people. i needed it for creating travel expenses(seen below) but i simply used "if" and made the trip number manual. now you choose the name, trip type and type the trip number and it calculates the travel expense name automatically. 

    Thank you for your consideration.

    shn

    Captur1e1 

Login to reply

Content aside

  • 6 yrs agoLast active
  • 4Replies
  • 2574Views