0

Formula not working - number doesn't increase?

I have this formula working for my orders and quotes table, but when I try to adjust it for BLA's each records BLA Number is the same

 

let myyear := format(today(), "YY");
let myBLANr := max((select '10. BLA' where substr('BLA Number', 3, 2) = myyear).number(substr('BLA Number', 6)));
if cnt(myBLANr) < 1 then
'BLA Number' := text("BLA_" + number(format(today(), "YY")) + "_001")
else
'BLA Number' := text("BLA_" + number(format(today(), "YY")) + "_" + format(myBLANr + 1, "000"))
end

4 replies

null
    • Sean
    • 5 yrs ago
    • Reported - view

    Have you tested your select statement in a formula field to make sure it’s returning a result? Also, I’m not sure why you are using the combination of text() and number() functions in your if-then-else when it would all be text and should work without them.

    • SECOS Group
    • quartz_cap
    • 5 yrs ago
    • Reported - view

    RE the combination of text and number - couldn't tell you. The formula was provided by someone on the forum (can't recall off the top of my head who) and I'm still trying to understand the formula myself.

     

    I'm unable to get a formula field to allow the select statement unless I shorten it to let myBLANr := max((select '10. BLA' where substr('BLA Number', 3, 2) = myyear) which doesn't return a result

    • SECOS Group
    • quartz_cap
    • 5 yrs ago
    • Reported - view

    Okay so I figured out the issue was that I had 3 letters to start (BLA) instead of 2 (like OR and QU in orders/quotes)

     

    So I'm assuming the numbers have something to do with it in the select statement... just trying to figure out how to adjust them haha

    • Sean
    • 5 yrs ago
    • Reported - view

    Per the documentation... https://ninoxdb.de/en/manual/calculations/reference-of-functions-and-language

     

    substring(string, start, end) – Extracts a part of the string. Start and end are zero-based.
    |  substring("Hello World!", 0, 5) => "Hello"
    |  substring("Hello World!", 6, 10) => "World"

    substr(string, start, length) – Extracts a part of the string. Start is zero-based.
    |  substr("Hello World!", 0, 5) => "Hello"
    |  substr("Hello World!", 6, 5) => "World"

    substr(string, start) – Extracts a part of the string. Start is zero-based.
    |  substr("Hello World!", 4) => "o World!"

     

    if you put substr('BLA Number', 3, 2) in a formula field you would get "_1" as the result.

    if you put substr('BLA Number', 4, 2) in a formula field you would get "19" as the result.

Login to reply

Content aside

  • 5 yrs agoLast active
  • 4Replies
  • 1619Views