Display different text in column depending on match with other value

Hello, I want to create a column view where a tag value is compared to %_file_create_datetime_raw% and %_file_mod_datetime_raw% and it displays different text in the column depending on the results.

If the value matches %_file_create_datetime_raw% then display the text: Created
If the value matches %_file_mod_datetime_raw% then display the text: Modified
If the value doesn't match either of the above, but it is a number greater than 946684800 and less than 4102444800 then display the text: Other
For any other numeric value (or if the value contains any non-numeric characters) then display no text at all and leave the column entry empty.

I hope someone can help because I can't figure out how to do this myself.

You can add scripting functions to the column definition in "Value".
Like
$if($eql(%datetag%,%_file_create_datetime_raw%),Created,)$if($eql(%datetag%,%_file_mod_datetime_raw%),Modified,)$ifgreater(%datetag%,946684800,Other,)$if($less(%datetag%,4102444800),Other,)
Leave "Field" for that column empty

1 Like

Thank you very much for your kind reply.

It does not seem to be working quite right at the moment.

Firstly, it is showing "Other" for every entry, instead of showing an empty field when the value does not meet the necessary criteria.

It shows "Other" when the values are invalid (containing letters or symbols instead of only numbers) and it also shows "Other" when there is no value at all. In both of these cases I would like the field to be left blank / empty.

View screenshot

If the %datetag% value does not match %_file_create_datetime_raw% or %_file_mod_datetime_raw%, then the word "Other" should only be displayed if the field contains only numbers (no letters or symbols) and if the number is between 946684800 and 4102444800. If the value contains any symbols, or if the number is outside of that number range, then the column should not display anything for that entry.

Secondly, in my test case (see screenshot above) it displays both "Created" and "Other" at the same time. It should only be one or the other (or nothing at all if the value does not meet the criteria).

Is it possible to adjust the scripting function to get this outcome?

Many thanks

It probably is - I think it would be nice if you could expand the script a little further yourself to get closer to your rather special requirements and see which part of the functions has which effect.
And if all this becomes too complicated, I wonder, how often in the lifetime of a file you really need this display, or whether a filter would serve the purpose much better than a column definition.

Or to cut this short: I am currently not really in the mood to dig any deeper into your conditions. The foundation is there, build on it

So in no case does this formula driven column show anything other than "Other" for any files? Are the specific files that you know should display "Created" or "Modified" in your current file set? If none of these other conditions that have been met also do not display the desired result, I would have a look at the field you are using as a comparison first. Does it contain a true numeric value to match? Is there any spaces or other hidden values that may be influencing the match? And where does this match field value come from in the first place?

Lot's of questions from me here, but the original post and subsequent replies aren't helping to clarify this and work towards getting you a solution.

And unless you have an ongoing reason to go back to this value, I agree with @ohrenkino that perhaps the better solution is to use a set of filter conditions that can do this rather than have a formula field that will need to be updated regularly to have any effective use. Otherwise this is adding unnecessary complexity to your tag fields in every file in your library.

Yes, I know.
I did not include the case for "contains non-numeric strings" as it is not clear whether this deviation appears in the middle of the string or the string itself is a word - otherwise the function $num() could be used to return "0".
I am missing a little the overall hiearchie of conditions - as that hierarchie would determine how many of the statements have to be nested.
And what I am missing also: what is the purpose of all this?
OK, you could sort by the column - and a sorted list could be ok for a couple of dozen entries. But as soon as it goes into the thousands, a filter would be superior.
That is why I was not really in the mood to dig into it a lot further.

@Quisquose can you confirm what the original source of this %datatag% is? It would be interesting to understand this in the scope of the request.