@Transform - works in field but not in view

HCL Domino Domino Forum
1

Hi All,

I’ve just added a computed field to a form which concatenates the values from 15 fields (all of which may or may not contain a date value stored as a string - bad design, I know, but it’s what I’m working with) into a list, runs @Transform on all of the values to remove them if they are blank, or convert them to a datetime value if they are not, then it sorts the remaining values to return the most recent date from the list. Here is the formula:

var:=ActionDate_1:ActionDate_2:ActionDate_3:ActionDate_4:ActionDate_5:ActionDate_6:ActionDate_7:ActionDate_8:ActionDate_9:ActionDate_10:ActionDate_11:ActionDate_12:ActionDate_13:ActionDate_14:ActionDate_15;

var:=@Transform(var;“tempVar”;@If(tempVar=“”;@Nothing;@ToTime(tempVar)));

var:=@If(@Elements(var)=1;var;@Sort(var;[DESCENDING]));

@If(@Elements(var)=1;var;var[1]);

This formula works fine in the field, but I now I want to display the result in a view and I didn’t want to have to refresh all of the documents in the database because there are a lot of documents in there. I thought it would be easier to just use the same formula in my view column if the LastContactDate field doesn’t exist. However, it seems that something is different in how Domino deals with column formulas, because the EXACT SAME formula in the column returns ‘Incorrect data type for operator or @Function: Number expected’, before I’ve even bothered to check for a value in LastContactDate first. I’ve verified that the LastContactDate computed field gets populated correctly when I do refresh the document, but in the view, the formula returns the number expected error. I’ve also tested the formula in an action and it works fine there as well. It is fine in the view if all 15 date fields are populated, or if none of the fields are populated, but if some of them have values and some of them are blank, it falls over with the error.

Has anyone got any suggestions as to what might be going on here?

I’m developing in 6.5.5, and I’ve tested this on a 7.0.2 server and a 6.5.1 server with the same result.

Emily.

2

Subject: Re: @Transform - works in field but not in view

Well it seems as though Domino couldn’t quite figure out what data type all my ActionDate fields were, although when I looked in the document properties they were all listed as Text fields. When I changed my column formula to this:

var:=@Text(“ActionDate_1”):@Text(“ActionDate_2”):@Text(“ActionDate_3”):@Text(“ActionDate_4”):@Text(“ActionDate_5”):@Text(“ActionDate_6”):@Text(“ActionDate_7”):@Text(“ActionDate_8”):@Text(“ActionDate_9”):@Text(“ActionDate_10”):@Text(“ActionDate_11”):@Text(“ActionDate_12”):@Text(“ActionDate_13”):@Text(“ActionDate_14”):@Text(“ActionDate_15”);

var:=@Transform(var;“tempVar”;@If(tempVar=“”;@Nothing;@ToTime(tempVar)));

var:=@If(@Elements(var)=1;var;@Sort(var;[Descending]));

@If(@Elements(var)=1;var;var[1])

I stopped getting the error.

So problem solved - I’d still quite like to know why this wouldn’t work without the @Text though, if anyone’s got any ideas.

Emily.

3

Subject: RE: Re: @Transform - works in field but not in view

Did you look at the document properties from the view, or from inside the document? When you are inside the document, the form will adjust the fields to be what they are expected to be on the form, which is why the formula worked in the document context. If you look from the view, you should see the values as they are outside of the form, which is what you get in the view context. I have frequently see this cause the exact sort of “Why does the same formula not work in a column?” question.

4

Subject: RE: Re: @Transform - works in field but not in view

Interesting - I’d never noticed this before. I was looking at the document properties from the view level, and they were showing text values for all the fields. When I just looked at them from within the document, they were showing as Text List values. Maybe this had something to do with it.

Thanks for the info, Ben!