Third File Relations

[falkaholic]

I've been struggling with this one for awhile.

I have a music tour database where:

-the main database is a one record per day.

-every day has a promoter for the event

-every day has a one-to-many to musical Acts (with bandID, start & end times etc), and in shown in a portal in days.

there is also a promoter database which promotor info in stored (contacts etc), as well, I have a portal the shows all the days that promoter is working. I've been asked to have it show the bands which were playing on the days that promoter was working in the portal.

I almost got it working by making a self-relation the Acts DB and making a valuelist based on the relations then using the ValueListItems() is a calculation, but that only yeilds the bandIDs; and I can't use the BandName (Bands::Name) calc in the Acts because it can't be indexed, so I have no way of getting the name from there.

HELP.

[djgogi]

Instead of calc Band::Name define an field in Acts as lookup from Bands ( I know it's against rules of normalization, but than it's the only way you can index it) and than apply the same method as for bandID, ie valueList.

Dj

[falkaholic]

That was my first idea, but it all goes down the river if the promoter of that day is changed.

grr.

I think i'm going to explore scripts, because i use the old calcuation field over-top a popup to get rid of IDs, which has to call a script to open the menu, so i could do stuff in there....