2 Replies Latest reply on Dec 2, 2014 1:38 PM by philmodjunk

    Artist's relational database



      Artist's relational database



      Years ago, I was hired by an artist to create a digital database of her contact information, and her records of all the artworks she has ever made.  At first I thought it would be basic data entry, but when I realized that Filemaker had discontinued support for Bento, I looked into Filemaker and recommended to her that we purchase a license key.  I thought the intrinsic nature of being a relational database would be perfect for her needs, and I thought I was up to the challenge to learn how to use Filemaker and design a solution for her.

      Now, after much grappling with the basic training manual and some of the advanced training articles, I am stumped.  In my case, the artworks may have both a seller (let's say, the artist herself, or a gallery owner) and a purchaser (a friend or collector).  I would like to be able to store all her contact information in one Contacts database, and be able to view associated artworks when she pulls up a record for a given contact.  It's a many-to-many relationship, since each artwork may have multiple people associated with it (at the very least a buyer and a seller, and possibly also additional galleries or museums that have displayed the artwork for a period of time), and each contact may have multiple artworks associated with it (as a somewhat complex example, there may be a gallery owner who sells the artist's work as part of an exhibition, and ends up purchasing an artwork now and then for his own personal collection.  Regardless of who's buying and who's selling, I'd love to find a way to make this information readily accessible and related between the database of contacts and the database of artworks).

      I currently have two tables, Contacts and Artworks.  I have only created one basic layout for each table.  I thought that if I assigned "auto-enter can't-modify" serial numbers to each contact, giving a unique seller ID and a unique purchaser ID, that I would be able to easily relate the artworks to the contacts.  I had visualized building a tab-control with two tabs, one for artworks purchased and one for artworks sold, for each contact, with a portal displaying information about that artwork.  But I can only get it to populate data for one of the tabs; not for both.

      Any assistance would be sooooo greatly appreciated.  I hope it has not been a grave mistake to encourage her to purchase Filemaker, when I simpler program would have been less of a headache.

      Thank you! 





        • 1. Re: Artist's relational database

          FileMaker can easily handle this.  The basic training manual is just...well too basic.  You would be better off with either the VTC training series or The Missing Manual.  It may also help to look at the Assets Starter Solution.

          I would think you need a join table between the two existing tables to track the artworks 'movements'.  Call it Transactions.  It could even end up as a portal on the Contact table.  I think also, the Category checkbox should be a related table

          • 2. Re: Artist's relational database

            Working from yours and Steve's posts:

            Start with these relationships:


            Artworks::__pkArtworkID = Transactions::_fkArtworkID
            Contacts::__pkContactID = Transactions::_fkContactID

            You can place a portal to Transactions on the Artworks layout to list and select Contacts records for each given Artworks record. Fields from Contacts can be included in the Portal to show additional info about each selected Contacts record and the _fkContactID field can be set up with a value list for selecting Contacts records by their ID field. And a Field or fields in Transactions can document the type of transaction with values such as "Purchase", "Gallery Showing", etc.

            For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

            And stay tuned. I have recently resumed work on "Adventures in FileMaking #3 - Many to Many Relationships"

            Caulkins Consulting, Home of Adventures In FileMaking