Relationships and conditional value lists across several tables
I am creating a database for an artist. The goal today is to correlate particular works with the collectors they belong to.
I have tables for each kind of artwork: flatworks, film, sculpture, etc. I wanted to have all these different kinds of art works in one table, however the kind of information required for each is extremely disparate and so this was not a possibility. Also, because of their nature, some work is unique (ie a painting) and other works are editioned (ie a photograph printed in an edition of 25). For the editioned works I have a table with the edition information, and another that lists each edition number and information specific to it (such as condition, location, etc). I also have a table with a list of collectors: names, addresses, etc.
I have created yet another table whose records represent a single "purchase", that is they require the date of purchase and the cost. I have a portal on this layout with a value list of the collectors listed in the collectors table. I am trying to put another portal on the layout for the actual art, right now I am working out the editioned pieces because those seem to be more difficult.
On the editions list table I have created a calculation field that creates a text string from the edition number (current table) and the edition title and year (from the editions table). I want to create a value list from this field, however I am getting an error when I try to commit the "use values from field" function ("This value list will not work because the field “TitlewithNumber_c” cannot be indexed"). I assume this is a problem because the calculation requires values from a relationship... is there a work-around, or better way to do this?
I am not sure how to approach creating a value list of the other artworks, across different tables (I would like to list all films, all sculptures, etc). I am used to creating related records in portals, however I do not want to "create" a related collector in a portal on a piece of art's record, I want to match them up as I have tried to do with the editions above.
Ideally I would like to have a drop down list, where in one field you select collector and in another you select artwork. I do not mind the artwork list being long, this is the nature of our work here.
Any logistical or organizational help would be gladly appreciated, thank you!
PS: Using Filemaker 12 Pro