0

Using FileMaker Pro 8 I have a table whit a column for dates, like showing below.

dates

Mon, Sept 10, 2007
Wed, Sept 12, 2007
Wed, Sept 19, 2007
Mon, Oct 8, 2007
Mon, Oct 15, 2007

I want a field(s) to show all those records like this

Sept 10, 12, 19 Oct 8, 15

I will appreciate your help

2
Contributors
1
Reply
2
Views
10 Years
Discussion Span
Last Post by laurenkuhlman
0

First, ensure that the field containing your dates is of the DATE type.

File > Define > Database... Choose your table from the dropdown. Look at the field containing your dates. Under Type, it should say Date. If not, select the field hold down Ctrl + D or choose Date from the Type dropdown, press Enter and Choose OK. You may also want to change the name of this field to DATE to ease entering of the calculation below.

Next, create another field. Again, you can name it anything but I called it DATE_LIST.

Stil in the define database bialog, type DATE_LIST into the field name textbox, choose Calculation from the dropdown and click Create.

Copy this code and paste it into the field:

If(Month(GetNthRecord ( DATE ; 1 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 1 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 1 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 1 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 1 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 1 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 1 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 1 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 1 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 1 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 1 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 1 ))=12;"Dec";""

)))))))))))) & " " & Day(GetNthRecord ( DATE ; 1 ))

 & 

If(Get ( FoundCount )>1;

If(Month(GetNthRecord ( DATE ; 1 ))=Month(GetNthRecord ( DATE ; 2 ));", " & Day(GetNthRecord ( DATE ; 2 ));" " & 

If(Month(GetNthRecord ( DATE ; 2 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 2 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 2 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 2 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 2 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 2 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 2 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 2 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 2 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 2 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 2 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 2 ))=12;"Dec";""

)))))))))))) & " " & Day(GetNthRecord ( DATE ; 2 ))))

 & 

If(Get ( FoundCount )>2;

If(Month(GetNthRecord ( DATE ; 2 ))=Month(GetNthRecord ( DATE ; 3 ));", " & Day(GetNthRecord ( DATE ; 3 ));" "  & 

If(Month(GetNthRecord ( DATE ; 3 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 3 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 3 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 3 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 3 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 3 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 3 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 3 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 3 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 3 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 3 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 3 ))=12;"Dec";""

)))))))))))) & " " & Day(GetNthRecord ( DATE ; 3 ))))

 & 

If(Get ( FoundCount )>3;

If(Month(GetNthRecord ( DATE ; 3 ))=Month(GetNthRecord ( DATE ; 4 ));", " & Day(GetNthRecord ( DATE ; 4 ));" "  & 

If(Month(GetNthRecord ( DATE ; 4 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 4 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 4 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 4 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 4 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 4 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 4 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 4 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 4 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 4 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 4 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 4 ))=12;"Dec";""

)))))))))))) & " " & Day(GetNthRecord ( DATE ; 4 ))))

 & 

If(Get ( FoundCount )>4;

If(Month(GetNthRecord ( DATE ; 4 ))=Month(GetNthRecord ( DATE ; 5 ));", " & Day(GetNthRecord ( DATE ; 5 ));" " & 

If(Month(GetNthRecord ( DATE ; 5 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 5 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 5 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 5 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 5 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 5 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 5 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 5 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 5 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 5 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 5 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 5 ))=12;"Dec";""

)))))))))))) & " " & Day(GetNthRecord ( DATE ; 5 ))))

 & 

If(Get ( FoundCount )>5;

If(Month(GetNthRecord ( DATE ; 5 ))=Month(GetNthRecord ( DATE ; 6 ));", " & Day(GetNthRecord ( DATE ; 6 ));" "  & 

If(Month(GetNthRecord ( DATE ; 6 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 6 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 6 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 6 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 6 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 6 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 6 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 6 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 6 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 6 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 6 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 6 ))=12;"Dec";""

)))))))))))) & " " & Day(GetNthRecord ( DATE ; 6 ))))

 & 

If(Get ( FoundCount )>[B]6[/B];

If(Month(GetNthRecord ( DATE ; [B]6[/B] ))=Month(GetNthRecord ( DATE ; [B]7[/B] ));", " & Day(GetNthRecord ( DATE ; [B]7[/B] ));" "  & 

If(Month(GetNthRecord ( DATE ; [B]7[/B] ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; [B]7[/B] ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; [B]7[/B] ))=3;"March";
If(Month(GetNthRecord ( DATE ; [B]7[/B] ))=4;"April";
If(Month(GetNthRecord ( DATE ; [B]7[/B] ))=5;"May";
If(Month(GetNthRecord ( DATE ; [B]7[/B] ))=6;"June";
If(Month(GetNthRecord ( DATE ; [B]7[/B] ))=7;"July";
If(Month(GetNthRecord ( DATE ; [B]7[/B] ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; [B]7[/B] ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; [B]7[/B] ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; [B]7[/B] ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; [B]7[/B] ))=12;"Dec";""

)))))))))))) & " " & Day(GetNthRecord ( DATE ; [B]7[/B] ))))

Before closing the calculation screen, click Storage Options... and check Do not store calculation results -- recalculate when needed

This formula only shows seven dates. To add to this number, copy and paste the red text below the existing code. Increment each bold number within the red text by one. For more, repeat.

You must perform a find for your targeted dates then, sort by DATE (ascending) for this formula to appear properly.

The abbreviations I used for the dates are as follows:

Jan
Feb
March
April
May
June
July
Aug
Sept
Nov
Dec

For further assistance, please feel free to hop over to my FM blog at laurenkuhlman.wordpress.com I have lots of handy calulations there. May I also suggest fmforums.com.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.