View column format for months using formula

HCL Domino Domino Forum
1

Hi All,

I have a column in a view that I would like to have categorized using month and year (ie Oct 2006), and then all of the documents that have dates in that month for a status to be grouped in there. I have tried several of the examples on this forum to get this to work but I still cant get it.

It is the first sorted column in the view, and the properties are custom to display a date/time format the way I want it.

This is what I have in the column so far:

p0:=@If(Status=“Aporoved”;@Date(@Adjust(AppDate;0;0;1-@Day(AppDate);0;0;0)) ;“”);

p1:=@If(Status=“In Process”;@Date(@Adjust(IPDate;0;0;1-@Day(IPDate);0;0;0));“”);

p2:=@If(Status=“Completed”;@Date(@Adjust(ComDate;0;0;1-@Day(ComDate);0;0;0));“”);

p3:=@If(Status=“Closed”;@Date(@Adjust(CloseDate;0;0;1-@Day(CloseDate);0;0;0)) ;“”);

p4:=p0+p1+p2+p3;

p4

This should group all of the dates regardless what day they fall on (turns them all to MM/01/YYYY) and then the categorization of the column should be correct. The fields I am extracting are computed and date/time fields.

All of the data is being placed under Not Categorized??

Any help is appreciated…

Mick

2

Subject: View column format for months using formula

I don’t think you need to @Adjust the date. Why not first set a temp variable to whichever date is applicable:

statusdate := @If (Status = “Approved”; AppDate; Status = “In Process”; IPDate; …)

Then extract the year and the month from that date:

year := @Text(@Year(statusdate))

@Select (@Month(statusdate); "D. January " + year; “E. February” + year; “F. March” + year; “G. April” + year; “H. May” + year; “I. June” + year; "J. July " + year; "K. August " + year ; "M. September " + year ; "A. October " + year ; "B. November " + year ; "C. December " + year)

The letter designations are just if you want the view categorized in FY order, with the FY beginning in October. If you want it in plain old calendar order, change those designations so January starts with A instead of October. (This assumes the view will only be for one year, whether calendar or FY.)

I will not be in tomorrow to follow-up, so hope this helps…

Mark

3

Subject: View column format for months using formula

Why not just…

statusDate := @If(Status=“Aporoved”;AppDate; Status=“In Progress”;IPDate; Status=“Completed”;ComDate; Status=“Closed”;CloseDate; “”);

@If (statusDate = “”; “”;

@Date(@Adjust(statusDate;0;0;1-@Day(statusDate);0;0;0)))

4

Subject: RE: View column format for months using formula

James,

I used this one also, or at least something like it. What you have presented here gets me pretty close. Strange issue is that some of the data in the column works and some of it doesn’t!!! I get some of my data like the way I want (Jan 2005…etc) but in the same column I get the Not categorized stuff. Very strange. I think what it has to do with is how the fields were being stored in the old form. From what I can see, some of the older forms (even after a refresh) indicate the date fields being saved as text!? So what I have attempted to do was to convert all of the date fields to text, and then re-convert all of them back to date/time fields… no joy. Anyway here is what I have in the column right now:

App_Date:=@ToTime(@Text(AppDate;“S0”));

IP_Date:=@ToTime(@Text(IPDate;“S0”));

Com_Date:=@ToTime(@Text(ComDate;“S0”));

Clo_Date:=@ToTime(@Text(CloseDate;“S0”));

statusDate := @If(Status=“Approved”;App_Date; Status=“In Process”;IP_Date; Status=“Completed”;Com_Date; Status=“Closed”;Clo_Date; “”);

@If (statusDate = “”; “”;

@Date(@Adjust(statusDate;0;0;1-@Day(statusDate);0;0;0)))

But this still gives me some of the values as Not Categorized. I have tried the other posts as well. Anyway, thanks to all who gave responses here, gave me some ideas to work with but just not quite there yet.

The help is well appreciated.

Mick

5

Subject: View column format for months using formula

I think you need to convert your temporary variables (p0 - p3) to text using @Text.

6

Subject: RE: View column format for months using formula

Thanks Brent for the response, but I tried that already. If I do that the column data becomes an error because it needs to be a date format (with the column properties being date/time and custom).

That does give me an idea though, do you think that my variable definitions (p0, p1, p2, p3, and p4) are inheritly strings when setting them with “:=”?

Any other thoughts??

7

Subject: RE: View column format for months using formula

I don’t think they are inherently strings, but I do think the + operator requires strings. Otherwise, I think you need to use the concatenation operator :

Let me know what you come up with!

8

Subject: RE: View column format for months using formula

Brent,

Thanks for the response. Yea I had that incorrect when I posted it and I did change it to use the : but it still came out wrong. Anyway, my last post under James is what I currently have and most of the time it works. Just not for all of the documents, and it seems as though the ones it fails on (does not group properly) are the documents that were created with the old form.

Thanks…

Mick