AnsweredAssumed Answered

Relationships and conditional value lists across several tables

Question asked by HelenaKaminski on Oct 14, 2013
Latest reply on Oct 18, 2013 by philmodjunk


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