1 Reply Latest reply on May 29, 2013 8:26 AM by philmodjunk

    Related fields

    AlexiHall

      Title

      Related fields & Unique Ids including data from another field

      Post

           I have not been using FMP long, and have relitively complex requirements

           I work in an art gallery, we have a database of works and a seperate database of artists.

           A lot of time is spent in the gallery manually entering data, copying data etc. There are a couple of processes I would like to automate using FMP, and would greatly appreciate any help out there.

           The first is to generate a Unique ID for each artist in the artist database, for artists Shane Feeney, Michael Richardson and Sarah Fang their IDs will be SF01, MR01 and SF02 respectively. So you can see with each set of initials the numerical secuence that folows starts at 01 and is never repeated.

           The second process that I want to be automated is the auto-completion of artist related fields in the work database, this database contains information including Artist ID, Telpehone number, Image ID, Image name, size and so on.

           Therefore when a user enters the Artist ID such as SF01, the other fields for artis address, phone number etc are automatically completed.. is this possible?

           I would greatly appreciate any help, attachments can be sent if necessary

           Thanks

           Alexi

        • 1. Re: Related fields & Unique Ids including data from another field
          philmodjunk
               

                    The first is to generate a Unique ID for each artist in the artist database, for artists Shane Feeney, Michael Richardson and Sarah Fang their IDs will be SF01, MR01 and SF02 respectively.

               Why do you need that specific format for an ID value? An auto-entered serial number is much simpler to set up and much safer to use in setting up relationships between tables. I'd never use such a field as a match field in a "main" relationship (Might use it in special purpose relationships only for data search purposes). If I needed such an identifier to meet the needs of the users who may already be using that method, I'd set up a field for it in the Artists table, but use an auto-entered serial number ot link artists to works instead of this field.

               

                    Therefore when a user enters the Artist ID

               Wouldn't it be simpler and less vulnerable to error if you simply selected the artist's name from a drop down instead of entering such an ID? It's possible to set up a text field where you can use an auto-complete enabled drop down list of artist names with a script that uses the name to find the previously mentioned serial number for that artist and enter it in the match field of the works table. Then any fields from the Artists table that contain the data you want to see on your works layout can be added to that layout to show data from the Artists table.

               Here's a demo file of this method:

               FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

               Ps. Are all your works by single artists 100% of the time? If you have any collaborative works or have the slightest chance that you could have such, a join table between artists and works would be a good idea as you would then have a many to many relationship between artists and works.