Value List from join table

[sflynn]

Any help on this would be great, I'm stuck. I have a project table that connects to a location table with a join table. Locations can belong to multi projects. I then have a media table that connects to the project table with a join table. A single media file can belong to many projects. I have portals viewing and creating all these records...works fine. Now I want to be able to assign the media to locations based on the list of locations that correspond to the projects assigned to the media. I can use a global field to give me a drop down showing only the active project locations. But if I assign two or three projects to a single media entry (via Portals), I would like the drop down list of locations to be driven by the corresponding records created in the location/project join table. Meaning if media record "45" has projects 1,4,5 assigned, I only want to see locations assoc. with those projects. whew... help!

[mr_vodka]

Am I misunderstanding something?

it seems as if it works for me. Try this sample and let me know if this is what you were looking for.

media.zip

[sflynn]

Thanks for that. here is a stripped down version of what I've got so far. Go in as a guest and select any project. Then click the button to the media asset page and I've written my issues out there. navigation and many things are messed up but this part is working. I may have a structural problem with the tables... please give it a look. I know enough to be dangerous but I've probably missed an easier way.

mediajoinsample.zip

[mr_vodka]

For the problem on hand, make your value list look like this image.

Also, you may want to clean up this database. It seem as though it may become messy. Also there are data mismatches in some locations. In one calc under the MediaAsset Main table, you have it resulting as a number when if it fails your conditional statement it will result with the text "null". In addition, I would suggest using the Case statement instead of this nested If.

Try this

// Cleaned up formula

Let ( ma = MAType ; 

                    Case ( ma = "Video Tape"; 1;

                           ma = "Digital Video File"; 2;

                           ma = "Graphic"; 3;

                           ma = "Audio File"; 4;

                           ma = "Still Image"; 5;

                           ma = "Other"; 6;

                           0      // Either change the result data type to be text if you want the word "null" or use 0

                         )

    )

.

[sflynn]

Thank you so much for your input. I'll dig into this and see where I get. And yeah, I have lots of cleanup to do. Cheers.