concatenating a list of related dates.

[oilcan]

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.

[Søren Dyhr]

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

[comment]

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.

[oilcan]

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, 2007 by Guest