Forum Discussion

NathanBeach's avatar
NathanBeach
Qrew Member
4 years ago

ToDate function

I was just given the job of modifying a Quickbase database to prepare for a merger next week. I come from the SQL world.

There is one field they want changed that I need help with. The current code snip-it is:

ToDate([Date Created])<Date(2021,1,1),("E" & Right(ToText(Year(ToDate([Date Created]))),2)
&("-" &([Envoy PN 2019]))))


They want the E changed to a P. That part is easy. But they also want the E to stay attached to anything from 1/1/2020 to 6/20/2020 and the P for anything starting 7/1/2020. If I just change the E to a P, it does it for everything for the year.

I looked at the ToDays, but I'm getting myself confused.

Can I just do:

ToDate([Date Created])<Date(7/1/2020,1,1),("E" & Right(ToText(Year(ToDate([Date Created]))),2)
&("-" &([Envoy PN 2019]))))

ToDate([Date Created])>Date(7/1/2020,1,1),("P" & Right(ToText(Year(ToDate([Date Created]))),2)
&("-" &([Envoy PN 2019]))))

Or is there a better solution. There is a lot of weird code which I get to muddle through now.



------------------------------
Nate
------------------------------

1 Reply

  • When you get into complex formulas, the syntax gets quirky really quick.

    Your option in an If Statement structure should work they way you wish:
    If(

    ToDate([Date Created])<Date(7/1/2020,1,1),("E" & Right(ToText(Year(ToDate([Date Created]))),2)
    &("-" &([Envoy PN 2019])))),

    If(
    ToDate([Date Created])>Date(7/1/2020,1,1),("P" & Right(ToText(Year(ToDate([Date Created]))),2)
    &("-" &([Envoy PN 2019])))),
    null))

    the null part might be "", depending on field type, but you get the idea.



    ------------------------------
    Ryan Stanford
    ------------------------------