Concatenating Multiple Related Records

[mattc]

Hi

I'm not sure if this can be done this way..

I'm wanting to use a calculated field in one database to display the contents of multiple related record fields from a second database. Is this possible?

I can structure the data from the 1st related record, but don't know how to display the subsequent records.

I could construct and format the data using a script or display via a portal, but I'd rather a field calculate this 'on the fly'

Any help appreciated!

Matt

[capisco]

I think this is what you're trying to do:

Create a Portal in the db in which you want to view.

Create a Field, make it the Related Record you want to view, and place it in the Portal.

Select the Portal, make it large enough to view multiple rows, and choose Format > Portal; then type the number of rows you'd like to see, and view multiple records as if they were repeating fields in a list.

Hope this helps. /J.

[mattc]

Thanks for the reply

I know this is an option, but I didn't want to do it this way. I was hoping that I could use a calculated Text field to reference the related records i.e.

Related_File::Time & " " & Related_FileDate) & "

[capisco]

Not that I know of. What about creating a new field that is data from several other records squished together (like Name = Name, Last + Name, First). There may be a way to capture everything you want to see with a calculation (next record, copy, paste, etc.) in one field on the fly, then view it via the related field. Sounds quite complicated. Good luck. /J.

[Ender]

You might try the ValueListItems() function to pull values from the related field into a calc field in the parent file. This requires a conditional value list based on the parent's ID. There's also a couple limitations to this: 1. Duplicate values are omitted. 2. The source can only be one field, so if you had more than one field that you wanted to see, you'd need to concatonate them in the related file with a calc, and use that for the values.

[BobWeaver]

It's possible to set up the valuelistitems method in a way that includes duplicates if necessary.

[mattc]

Thanks for the input guys - can you give me an example please, I'm struggling to display multiple records!

I have a parent file (Call Log) & a related file (Log Detail). Each Call Log has multiple Log Detail.

The fields I have in Log Detail are:

Call Serial Number (Key)

Log Time

Log Date

Log Notes

I need to display multiple records from Log Detail (all above fields), formatted in a text field (calculated or other method) in Call Log.

I wanted to avoid using scripts or portals if possible.

I hope this is clear?

Thanks

Matt

[aaa]

Building your text_field by script - you dont want?

[mattc]

I would rather a calculated text field do it on the fly, rather than it having to be a triggered event.

Thanks

Matt

[comment]

You could add a calculation field in the Log Detail table (result is text) =

Call Serial Number & " " & Log Time & " " & Log Date & " " & Log Notes

Instead of the space in " ", you can use option-tab character: in Browse mode, type option-tab into a field, select it and copy. When defining the calculation, paste the character between quotes.

Next create a value list based on the calc field.

Finally, in Call Log, create a calculation field (result is text) =

ValueListItems ( Status (CurrentFileName) , "YourValuelistName")

[TDebb8594]

I have been trying this calculation

ValueListItems ( Status (CurrentFileName) , "YourValuelistName")

And I can't complete it. I am told that Satus is not a function and my currentfilename is not found.

[QuinTech]

Use Get(FileName) instead of Status(CurrentFileName) for version 7.

J

[mattc]

Thanks Guys, but i'm afraid 'im still struggling.

Not sure whether this works in FMP6. If the theory is correct, would the value list return multiple records with the same Call Serial Number?

[comment]

The value list will return all unique combinations of (Call Serial Number & " " & Log Time & " " & Log Date & " " & Log Notes).

One thing I should have mentioned - make the calculation field that displays the value items unstored.

[TDebb8594]

Use Get(FileName) instead of Status(CurrentFileName) for version 7.

J

I managed to make it work. However, The value list pulls up only the first sorted record. This is not the only time I've run into "only the first record". Any ideas?

[comment]

I don't have version 6, so I wouldn't be able to look at your file. Here's a version 4 working demo.

valueListItems.zip

[mattc]

Just wanted to thank you - your example db works perfectly & I have now got my FMP6 solution working.

I was getting the 'ValueListItems' Status function wrong.

Thanks Again

Matt

[mattc]

Here comes the next challenge!

I know this sounds like I want the moon on a stick, but I'm interested if this is possible, now I've gotten over the hardest part.

With my concatenated data being delivered using a value list, is there any way of formatting the data in the master file (Call Log), i.e seperating each returned record with additional carriage returns?

This doesn't seeem to be possible, as the value list ignores additional carriage returns (within the calculated field - Call Detail) & delivers related items 'line by line' - I suppose the other way would be to parse the data as required into another field using commands such as Leftwords?

Matt

[comment]

Try:

Substitute (

ValueListItems(Status(CurrentFileName), "relatedValues") ,

"

[mattc]

The substitute function has worked perfectly - All sorted.

Thanks