4 Replies Latest reply on Feb 21, 2011 9:33 AM by philmodjunk

    How do I set up the key field so it will not incorrectly fill records?

    marco96

      Title

      How do I set up the key field so it will not incorrectly fill records?

      Post

      I have a business solution that has different files which access the same table through a portal. The table is called "Doc. /Web viewer". I use it to bring up documents or web pages that I use for that file. in one file the Id key field was 22  but it brought in some records that another file was using which had the id key field of 0022.

      How do I  have different files access the doc web viewer with out bringing in files I didn't put there?

      Right now, each file that has the portal in it has the id key set as a serial number and some of the id keys from the different files are matching. Can I place two letters in the auto enter "next value" and then have the number fill. So in my project layout the ID key might have "pr" then the next number value. Then in my task pricing table I might have "tp" then the number value. If I can do something like that the different id key would never match from one file to another.

      Thank you

      Marco

        • 1. Re: How do I set up the key field so it will not incorrectly fill records?
          Sorbsbuster

          A Fundamental Rule for good DB design would be to never rely on a user creating a Key Field value.  If you have values '22' and '0022' it sounds like you are letting users enter those values somewhere.  Some designers go as far as saying the users should never even be allowed to see the Key Fields, never mind interact with them.

          The cause of that particular example is possibly that the fields are defines as type 'Number', but someone has typed '0022' into the field, thinking it was different from '22'.

          I would also resist using special characters in my table or file names (such as '.' or '/')

          Numbers are cheap, and Filemaker can keep track of them perfectly.  There is often no need for the supposedly human-friendly way of prefixing numbers with 'PR' or 'TP'.  Let Filemaker handle the serialisation of the numbers.

          • 2. Re: How do I set up the key field so it will not incorrectly fill records?
            marco96

            There are several files that access the doc/web viewer. They all auto fill the key field of the doc / web viewer. But with several files accessing the same file through a portal and auto counting, there are going to be some number repeats. The 00 was suggested by a developer to have it auto fill "00" before the number.

            I have a project layout with the doc web viewer portal. I also have a file called "task pricing" with a portal from the doc/web viewer.The doc web viewer key field is set to match the other files key field. I am not sure how to work the numbers so they will not repeat. This is why I though in the project layout I would set up the auto enter with a "Pr" before each number. And in the task pricing data base I would set up the key field to start the number with "TP" before each number. None of the numbers are manually set. I was just thinking there would not ever be any number matches in the doc / web viewer data base with these letters auto entered.

            Maybe I should have the doc. /web viewer making the key field and the other files matching the web viewer. As it is now, it is the other way around. I just thought that since the other files all already have a number for every record I would use that as my key field for the doc /web viewer.

            Thanks for helping a rookie

            Marco

            • 3. Re: How do I set up the key field so it will not incorrectly fill records?
              Sorbsbuster

              "with several files accessing the same file through a portal and auto  counting, there are going to be some number repeats" - I don't understand this - in that I don't understand why you would have designed it to let this happen.  Let's say you have two tables:

              Each file should have it's own series of unique ID's.

              The Parent File has a field ProjectID, and it is unique, and automatically generated by the Project File.

              Each Child record has a unique DocumentID field, automatically generated by the Document File.  It also has a non-unique field, ProjectID.

              When you generate a new Document record it auto-generates it's own unique ID, and will have inserted the ProjectID that you want it related to.  If you set the relationship for the Document portal in the Project Table to be able to create new child records it will even do this for you automatically.

              • 4. Re: How do I set up the key field so it will not incorrectly fill records?
                philmodjunk

                22 will = 0022 when both values are treated as numbers. They will not be when both are treated as text. Check your data types in your relationships if you are seeing records in a portal for 0022 when you are entering/selecting 22.

                What's a bit confusing here is that you have a web viewer and a portal here and I'm not perfectly clear as to whether it is in the portal or the webviewer that you are seeing this mismatch.