Portal Navigation and Concatenation

[Ryan P]

Hello everyone,

I recently started working with FM database and have been asked to implement a new feature to our current database.

I made a portal with flight information, with different fields such as date, time, flight number, etc. For each record there may be many portal records, for example any person may go on many flights. (This is a student information database). The flight records are sorted by date.

What I need to do is concatenate, all the flight information into a new field which represents flight summary. This is easily accomplished when I only have one flight, however when I have more than one, each flight must be sorted by date and put into the summary field.

So what I need to be able to do is navigate through the portal while it remains sorted and concatenate this information onto a field.

Can anyone help?

Thanks in advance.

[Vaughan]

Read up on the List() function. It will do what you want.

You'll probably need to make a calculated field in the flight table that concatenates the summary info for each flight, then use List() to concatenate these together at the student level.

Why does this info need to be concatenated into one field?

[Ryan P]

Thanks that worked great! It all had to be in one field because this information will be included in many letters and layouts.

[HV McCue]

Ryan,

You could use a custom function to recursively build a list of the concatenated details from your portal, but there's a much simpler option. Not as slick, but practical and easy to implement.

1) In your flights table (portal), add a calculation that concatenates the various flight details you want included (text result).

e.g., _flight_details = date & " " & airport & " " & flightTime

2) In your student table, add the following calculation:

List(flights::_flight_details) [text result, unstored]

If your sort order has been defined in the relationship (rather than the layout portal), your result will also be sorted.