Multiple related records in a merge field

[Seanjuan]

I have a FM system for reserving multiple calendar days for a single workshop. I would like to generate one field that displays the title of the class as well as all the associated dates, this way I can insert that total value into an email body field and send workshop reminders. It should look like this:

Workshop 1

Jan 2, 2002

Jan 8, 2002

Jan 12, 2002

I can generate portals, I can do complicated copy and paste scripts to get it all, but I there just has to be a simpler way. Any thoughts?

Thanks so much!

[bruceR]

If you had 8.5 or greater you could use the list function. Assuming the class name field is ClassName:

List( ClassName; list(ClassDates::Date))

[eos]

And if you're unwilling to update to FMP 9, there should be some custom functions floating around on this site which make use of GetNthRecord to emulate the List() function... hmm, why not try this one: http://www.briandunning.com/cf/439.

Concatenate the class title with the output of this function and use the result as body of your e-mail, either in a new calculation field or by using a script and a variable. Good luck!

Cheers,

eos

Edited December 5, 2007 by Guest

[comment]

But since you have 8 Advanced, you can a use a custom function to achieve the same thing - see here:

http://www.fmforums.com/forum/showpost.php?post/267809/

Although that thread deals with a found set, the function will work just as well with a related set, if a reference to a related field is placed in the fieldName parameter.

[Seanjuan]

I upgraded to FM9 and need a little syntax help with this list function. I'm generating email bodies that include a list of dates, times and channels that programs play on.

In a wierd kind of shorthand, I need to display three fields per row, like this:

Sunday, 3PM on Channel 9

Monday, 2PM on Channel 10

Tuesday, 6PM on Channel 22

etc

However when I try to List(""&date&""&time&""&channel&"") then I get a message that basically says I should not use an expression when a field would suffice. Well la-di-da, thats fancy Filemaker language for "you don't know what you're doing".

And so I am here. Anyone have thoughts?

[comment]

You need a calculation field in the related table =

date & " " & time & " " & channel

Then use the List() function with this calculation field as the parameter.

[Seanjuan]

Ok, got it. My list is appearing as intended, almost.

I made field called schedule_summary:

""&Timestamp ( date ; time)&" on Channel "&channel&""

and got:

2/22/2008 9:00 AM on Channel 10

And this is perfect, but the List(schedule_summary) does not sort the records/rows in a logical order. How can I force the resulting list of records to go in chrono order and not random?

[Seanjuan]

Nevermind, I finally found the answer after distinguishing between Value List and List(Function) in my searches.

I had to use the relationship sort order to get the List(schedule_summary) to display in chrono order - and either make a separate relationship for my needs or display any future portal with a different portal sort order than the relationship I used for the list.

The answer was here:

http://www.fmforums.com/forum/showpost.php?post/272183/