6 Replies Latest reply on Jul 2, 2012 3:04 PM by baztown

    preventing duplicate thru portal

    baztown

      Title

      preventing duplicate thru portal

      Post

      i'm new to Filemaker and to databases.

      Im working with a realtionship database for a small museum.

      Im working on a sample database, one table is 'names' the other 'artifacts'

      I created a portal under the artifacs table that pulls the name table information . I would like to create a way to input names etc thru the artifact table, so that when new items are aquired we shouldnt have to input the reated names thru the name databse, we should be able to input that information  thur the artifact table via the portal.

      1) is that wise?

      2) i created a drop down (from the name table) so that we should pick any current name rather than creating a duplicate name. But every time we click on a current name thru the dropdown it creates a duplicate and a new ID under  the name table  , how can i avoid that?

       

      Thanks!

        • 1. Re: preventing duplicate thru portal
          philmodjunk

          It would help to know how you have related these two tables and the purpose for each table. Why do you need the names table? What does it do for you?

          Ideally, your artifacts table should have a primary key field. In FileMaker, we often begin the name of such a field with __fk or something similar to make it easy to spot which field serves as the primary key. In FileMaker, this field is almost always an auto-entered serial number, though Filemaker 12 offers an alternative using Get ( UID ).

          This field would then be used to link a record in artifacts to other related tables such as:

          Artifacts::__pkArtifactID = Names::_fkArtifactID

          But this assumes a one to many relationship, one Artifact to one or more records in Names. That may or may not be what you need here. It may actually be a many to many relationship where more than one artifact can be linked to the same name and can link to more than one Name. But that's something you will need to determine and let us know.

          • 2. Re: preventing duplicate thru portal
            baztown

            currently setup    Names:: pk_nameID =Artifcats::_fkNameID

            Let me explain why i went this way.

            The museum has many objects e.g wedding certificates, artifacts, letter,post cards etc. I noticed that just about every table  has names, location and date fields. That's why i thought i should setup a name, location and date table to relate every object to.

            For example a postcard will have a 'from' and 'to/recipient' field as well as the location/landmark.

            This way if the same name is in another table such as in a letter table the name will  show up on the dropdown which will:

            1)ensure that the name won't be misspelled, the user will notice it is already there (he will figure out if its the same peroson or another) 

            2)if we would like to search any information on a specifiic person we will find any letter and post cards that he has sent.

            Thank you!

             

            • 3. Re: preventing duplicate thru portal
              philmodjunk

              So each record in names represents a specific person?

              Might a single artifact record be linked to more than one such record in Names?

              Using the postcard as an example, you might record the name of the person who donated the item, the name of the artist who created the image on it, the name of the person who sent it and the name of the person who received it.

              This suggests a many to many relationship between names and artifacts.

              If so, a join table is in order so that you can link many individuals to many artifacts and a given artifact to many individuals:

              Names----<artifact_name>----Artifacts

              Names::pk_NameID = artifact_name::_fkNameID
              Artifacts::pk_ArtivactID = artifact_name::_fkArtifactID

              • 4. Re: preventing duplicate thru portal
                baztown

                Yes. each record i the names represents a new person.

                Yes, it would be a many to many realtionship.

                Thank you for your help i will use a join table .

                Would any of this explain why i when a user chooses a name form the drop down under e.g. postcard table that it creates a new instance of the name with a new id, eventhough it's already under the names table?

                For example . if multiple people lived in Chicago, the first time  Chicago is added thru the post card table ( which is related to the Location table) then Chicago gets added under the Location table and get  ID. When the next post card info is added Chicago can be picked thru the drop down and seems to work fine. However , once i open the Location table i see 2 instances of Chicago with 2 different ID's which i'm assuming will cause problems down the road since  any given person / city can have multiple ID's which i dont supose is a good thing.

                Forgive me for being a newbie...

                • 5. Re: preventing duplicate thru portal
                  philmodjunk

                  Would any of this explain why...

                  It's hard to say without knowing more about how you designed the layout. Adding a new name record is best added on a layout based on the name table and validation rules can catch instances where the new name is already used in another record in the names table. You'd select the "unique values" validation rule to do that.

                  Here's a demo file on many to many relationships that you may find helpful:  https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                  • 6. Re: preventing duplicate thru portal
                    baztown

                     Great! will check it out.

                    Thanks for your help!