I have a set of related fields from a separate table called Harvest Dates. Users first enter their harvest dates through a portal, creating a unique related record for each date entered. Now, for summary purposes, I wish to be able to display the dates from these records in a comma delimited, yearless format, for easy viewing in a list view. for example, let's say you have the following dates entered in the portal:

10/22/2007

10/25/2007

11/1/2007

I wish to construct a calculation to display this as a single text field like:

10/22, 10/25, 11/1

I really don't mind if the last date shows the requisite year. i.e., this is okay:

10/22, 10/25, 11/1/2007

so far I've been trying this:

Substitute ( List( Harvest_Dates::Harvest_Date); Right (¶; 6); ", " )

the idea i'm trying to achieve here is to use the list function to get a return delimited list of the dates, then substitute each carriage return and the 5 characters before it with a ", ". so, 10/22/2007¶ should return "10/22, "

unfortunately it isn't working. there is limited success in that I'm able to return a comma delimited list rather than a ¶ delimited list. but I can't quite seem to get rid of the year. i've tried a few permutations of this notion, but I've yet to get this field to display the way I want it to. I'm sure there's something simple to do this, but my lack of aptitude for designing elegant calculations is really holding me back any pointers would be appreciated.

Although the mathematical esthetics are right down the drains here, could it pay off to consider the context although it reminds us about the millennium bug.

Substitute ( List( Harvest_Dates::Harvest_Da te); ["/2009";", "];["/2008";", "];["/2007";", "];["/2006";", "];["/2005";", "] )

While if the esthetics are the concern, would I seek shelter under this:

http://www.briandunning.com/cf/771

...but it requires a Adv. version to design CF's!

--sd

You would need to either use a custom recursive function (requires Filemaker Advanced), or use the List() function to construct a fairly complex formula, then pass it to Evaluate(), somewhat similar to the method shown here:

http://www.fmforums.com/forum/showtopic.php?tid/149507/post/149633/#149633

A simpler solution would be to add a calculation field in the HarvestDates table, to return a single 'yearless' date (as Text).

Then use the List() function on the results, substituting ¶ with a comma.

Or, since it seems the year must be the same for all the related dates, why don't you just substitute it out along with the ¶? Something like:

Substitute ( List ( HarvestDates::HarvestDate ) ; "/" & Year ( HarvestDates::HarvestDate ) & ¶ ; ", " )

Note that this relies on the date format ("/" as the separator, year is last), so it won't work for someone using 'yyyy-mm-dd' for example, as their preferred date format.

Ideally, I will be acquiring FM Advanced some time in the near future, because having access to custom functions sure would be nice. In the meantime, I used your code, comment, and it works nicely! at least nice enough for now until I can make a more elegant custom function anyway.

thanks everyone!

Edited November 16, 200718 yr by Guest