Data Transformation Add Columns - Can I Have A Complex Equation?

Hi there @Teodor

I want to use the new Add Column server action step to have a value like this:

{{(((validity_status=='d') && (valid_from <= local_time) && (valid_to >= local_time)) || (validity_status=='a'))?1:0}}

where validity_status, valid_from, valid_to are columns in the original database query, and local_time is a Set Value.

Is this possible to do right now?

If so, is this the right syntax to do it?

Best wishes,
Antony.

I don’t understand what you are trying to achieve.
Maybe explain your idea more detailed?

So I have a discount_codes table with columns and values like this:

validity_status   valid_from     valid_to      (meaning...)
====================================================================
        a                                      (valid all the time)
        d         2021-04-06     2021-04-08    (valid between dates)
        d         2021-04-08     2021-04-10    (valid between dates)

I want to add a new column called is_valid, which based on the user’s time in their time zone (local_time), will indicate which of these 3 codes are valid (so today, 7 April it should be the first and the second ones listed above only.)

I hope that makes sense!

Add columns currently do not support referring to other columns in the same record. Just external expressions or static values.

Maybe we can improve that @patrick

Thanks for the update @george!

It would be very useful if this were possible. As you mentioned @patrick in your reply, and possibly adding this option, is it necessary to add a feature request (unless one has already been made)?

(On a related matter: I was going to have a look at the documentation for transformations but all the screenshot images are broken.)

I was looking for this exact same behaviour. I too misunderstood “Data Transformation” to actually be able to do the transformation based on the data in the other columns. That would be extremely useful, and I have to assume, quite a common use-case.