Report covering several tables

[M1050]

Hi,

I hope I'm posting this in the right place...

I have 5 tables: CDs, Songs, Contacts, CDSongs, CDSent

CDs - list of CDs in library

Songs - Song names

Contacts - List of contacts

CDSongs - Which songs are on which CDs

CDSent - Which contacts got which CDs

I've been asked to provide one report that lists the Names of the people that received CDs and the dates they were sent (CDSent) along with the songs they received.

I'm guessing if I had a later edition of Filemaker this would be easier, but is there any way to do this with FM5? To me it seems like it would have to be 2 separate reports from 2 separate tables (CDSent and CDSong).

Any help would be appreciated.

Thanks

M

[Fenton]

Look at this recent post and example, using ValueListItems(). You would be getting the CDSongs.

http://www.fmforums.com/forum/showtopic.php?tid/187390/post/253367/#253367

Do the report from CDSent. But I don't remember whether FileMaker 5 had related value lists yet (5.5?).

[M1050]

I've never used that before. When I tried doing this, I just got all songs instead of only those songs for a particular CD.

I've created value lists containing values from a relationship, but that didn't work here. Maybe I'm doing it wrong??

[Fenton]

If you can create relational value lists in 5, but it's showing all the values, then probably the relationship is incorrect. It would be from CDSent to CDSongs, CD_id = CD_id.

And I think you're going to have to Lookup the name of the song into CDSongs. Because the field must be indexed to be the target of a value list, and it must be in CDSongs (I think).

In 8.5 you'd just use List(), in 7-8 you'd still use ValueListItems(); in either case you'd reach across CDSongs to Songs to get the names. I'm a little rusty on FileMaker 5 techniques -]

[M1050]

Thanks for your help.

I got it to work...but then it disappeared. I was reading some other topics and people mentioned that you'd have a hard time with refreshing. Do you know why this happens?

Also, how would I look up the name of the song? Like you said, I can not bring Song Title into CDSent because it is not indexed and it must be indexed...

M

[Fenton]

What exactly disappeared? One major point; calculations using ValueListItems() must have their Storage set to [x] Do not store; in the calculation dialog there's a Storage Options... button, lower right.

This calculation, result Text, unstored, would be in CDSent, using a value list name (type it correctly; it's in quotes). The Value List itself is using a relationship from CDSent to CDSongs, based on CD_id.

In CDSongs you need a text field, SongName. Its Auto-enter options are (•) Lookup, with the relationship to Songs, based on Song_id, looking up the Song Name field.

To poplulate into an existing CDSongs file, put your cursor in the Song_id field in CDSongs, then go to the Records menu and choose Relookup. Show All Records first. It could take a while.

[M1050]

The values appeared and then disappeared. But then I changed the setting to do not store etc. and it works.

I changed "Song Title" in CDSong from a calculation field to a text field and now I have the song title in CDSent. This raises a concern for me though. Won't this increase the file size of CDSong since before "Song Title" wasn't really stored in the file (just a calculation) and now it is...?

[Fenton]

Yes, it increases the size of the database. Yes, it is "redundant" data, in that if you edit the name in Songs, it ain't gonna change in CDSongs. Yes, upgrading to 8.5 would give you the tools to easily bypass these problems, in that it could reach "thru" CDSongs to Songs -]

[M1050]

}-)

Thanks for all the help.

I'm resistant to upgrade to 8.5 because I'd have to re-create all of my databases, value lists, scripts, etc. I kind of feel that if I have to go through all that work, I'd rather just upgrade to a SQL server database...

M