Can anyone shed any light on this peculiar behavior of the @Transform formula?
I have some documents for events that have a date field allowing multiple values. Some events go for one day others span a few days and others might be on one day a month for 12 months. So the multi-value date field seemed the best approach to take.
On the home page of my site I only want to show the events that occur in the next 7 days.
I’ve used this selection formula to get that:
SELECT Form = “f-event” & Archive!=“Y” & ((RepeatDates < @Adjust(@Today;0;0;7;0;0;0) & RepeatDates >= @Today) )
Now in my display of the events I want to show the current date only for the event. For example today is 29/10/2008 and I have an event which has these dates 1/9/2008, 1/10/2008, 1/11/2008, 1/12/2008. So I only want to show 1/11/2008 on the home page for this current event.
I had tried this in my column formula:
@Transform(RepeatDates; “myDate”; @If(myDate < @Adjust(@Today;0;0;7;0;0;0) & myDate >= @Today; myDate; @Nothing));
But it was only working for documents where all dates were within the current range (ie the next 7 days). For any documents that had a date out of this range it was randomly giving me a “ERROR: Incorrect data type for operator or @Function: Text expected” OR “ERROR: Incorrect data type for operator or @Function: Number expected”. All fields in the docs were of type date.
For a test I then tried putting a computed for display field on the document with exactly the same formula, and it worked perfectly.
Through trial and error I was able to get the column formula to work by converting it to text so that I could trim it and then back to date again:
dateList := @Transform(RepeatDates; “myDate”; @If(myDate < @Adjust(@Today;0;0;7;0;0;0) & myDate >= @Today; @Text(myDate); @Nothing));
@TextToTime(@Trim(dateList))
It appears that the @Nothing was not causing the formula to ignore that element when building the new list. This is contrary to what is stated in the designer help for @Transform: “If an iteration of the formula returns @Nothing, no element is added to the return list.”
This appears to be a bug when using @Transform in a column formula.