5 Replies Latest reply on Oct 18, 2013 2:21 PM by philmodjunk

    Relationships and conditional value lists across several tables



      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

        • 1. Re: Relationships and conditional value lists across several tables

               Companies routinely manage inventories of very disparate types of items from a common table. The secret is in linking up special "detail" tables that only contain related data for a specific type of item.

               Example 1: Your Inventory table record, __pkInventoryID = 1, has a description field text: "Painting". The _fkPaintingID field has a value that links it to one record in the Paintings "detail" table. The _fkCopyID field, on the other hand, would be empty in this table because only prints would link to a record in the Copies table; this table can, in turn, link to an "original prints" table and so forth...

               When setting up an invoice, your invoice record can link to a lineitems table for each item listed on that invoice. (If these items are sufficiently valuable that this is unheard of, you don't need the lineitems table.) Each line item record can link to Inventory for the ID, price and description.

               And yet you still have your additional details recorded in the related detail tables.

               And yes, there can be significant complications when it comes to reporting some of that very different detail info, but it can be done once you have the basic tables and relationships set up.

          • 2. Re: Relationships and conditional value lists across several tables


                 Thank you for the advice (again).

                 The advantage of your suggestions of an Inventory table, then detail tables for each type of art is that some of the artwork we are cataloging has the same name, for example "Fun" might be a painting, performance, collage, and film. All are related, so this way I can create one "Fun" record in the Inventory table, then add one painting detail, a performance detail, a collage detail, and so forth.

                 However, this still seems to complicate my problem, because I want to be able to create a list of ALL artwork. Let me give another example:

                 "Fun" is a performance, painting, collage, and film. 

                 "Tree" is a photograph, printed in an edition of 5.

                 There is a CollectorA and a CollectorB.

                 I would like to be able to (when a purchase is made) select the collector (this is easy); but then also which artwork, specifically which edition number if that is applicable. "Tree" number 1 of 5 is different than 2 of 5. And they are not always sold in order. So I need to pull the title from the Inventory, then pull the detail information from the detail table (this decides whether an edition will be applicable, and what kind of art they are buying), then pull the edition name from the edition table (if applicable), then pull the edition number from the edition number table (if applicable).

                 Maybe there is a way to do this within your answer that I am not understanding, but it seems like there are still many relationships and so the database can't index certain fields, so I won't be able to create a list of all available art... It would be easier if we were a company with a normal inventory!

            • 3. Re: Relationships and conditional value lists across several tables

                        because I want to be able to create a list of ALL artwork.

                   Seems like your list could be produced directly from the inventory table, but you'd need to log each edition as a separate record in that table instead of a related table. In other words, if you have 5 prints of that specific piece, you'd have 5 records in inventory.

              • 4. Re: Relationships and conditional value lists across several tables

                     So I guess what I want to do is just not possible, then? Since there is so much information in the tables, keeping track of which edition is being worked on would become excruciating, in cases of editions of 65 or more works (sometimes editions go into the hundreds, plus then there are Artist's Proofs, and Printer's Proofs, and Artists Print Proofs).... ? Plus, when I generate a master list of all art, I would not want to have each photograph appear as many times as there are editions.

                     Am I doing something very wrong, or have I just reached the limits of Filemaker Pro? Would upgrading to Advanced be a good idea?

                • 5. Re: Relationships and conditional value lists across several tables

                       It should be very possible. Just put one record in inventory for each item you have, then link each record to detail tables as needed.

                       There are two basic types of inventory tables that businesses set up.

                       One type, what I suggested originally, has one record for each type of item in inventory. If you have 4 forklifts, you have one record for that group of forklifts.

                       The other type of inventory, what I am now suggesting, has one record for each physical item in inventory. Those 4 forklifts become 4 different records in the table.

                       Either table could be linked to detail tables that document additional info specific to particular types of items in inventory such as a detail table for sculptures and a detail table for prints.