AnsweredAssumed Answered

List contents of one field from multiple records, as determined by a common field.

Question asked by MattMinde on Jan 21, 2018
Latest reply on Feb 1, 2018 by MattMinde

HI all! Try as I might, I haven't found the answer to this question, but it can't possibly be unique!

 

First thing first: I'm using FMPro 9 on a Mac. (I know it's old but it works, so far...)

 

Basically, I would like to concatenate into a new, single field in ONE record the contents of a different field across MULTIPLE records; the contents of that single field need to be determined by a data condition in a separate field in said multiple records.

 

Specifically, I have exported an iTunes library playlist (not the XML file, just the text); I've imported this into a table that has ONE record for each song on an album — i.e., each record displays the album and one song on that album.

 

In another layout (another related table?) I want to be able to display:

1) The album title ONCE in one field;

2) ALL the track numbers, song names and times on that album in another field;

 

so that I can format this information (along with other relevant info, like genre, etc.) into a printable CD booklet. (*see footnote…)

 

Ideally, this would all happen automatically (like a calculation result), as in when I open the CD layout, the data is arranged so that there appears the ARTIST, ALBUM TITLE, and the TRACK NUMBERS, SONGS and TIMES from that album. So basically, if I were to add a song to a compilation, it would show up automatically in the song title listings on the CD layout.

 

I've tried messing with portals, related tables, calculations and get list, all to no avail, although I have a feeling this will require use of a portal. And if so, would I need to use two separate DBs or can this be done with two related tables in one DB? (I probably don't quite understand related tables all that well...)

 

Any help, any leads would be greatly appreciated! —MM

 

* I know iTunes has the capability to print CD booklets, but the quality is poor, there's no way to manipulate the typeface, copy size, or handle overflow text (it just ends with an ellipses), and I need more info than is available on the CD insert option. I suppose I could try and hack into whatever format Apple uses to generate the CD insert, but I thought it'd be easier to manipulate data in FMPro and get a useful and aesthetically pleasing result.

Outcomes