7 Replies Latest reply on May 18, 2011 4:18 PM by philmodjunk

    Creating an "audit" table

    JantzSelk

      Title

      Creating an "audit" table

      Post

      Hey everyone. I am trying to create a relationship between a table and another "audit" table. The first table has fields containing various chemicals, amounts and procedures related to a certain chemical "recipe". The purpose of the second "audit" table is to ensure that the chemicals, their amounts and procedures found in the first table are followed correctly. This audit table will then have to be given a unique lot # and filed independently of original table. Any instruction in the right direction is appreciated!

        • 1. Re: Creating an "audit" table
          philmodjunk

          Can you post a more detailed example of what you want to do?

          On a side note:

          "The first table has fields containing various chemicals, amounts and procedures related to a certain chemical "recipe"."

          Sounds like you might be better off with two tables for that. One table where one record = 1 "recipe". ANd then a related table to list the materials used in that recipe.

          • 2. Re: Creating an "audit" table
            aammondd

            A little more description of what your audit file is supposed to record/display would be helpful.

            I have experience creating audit tables. I usually link my audit table to a Table of  Global fields where I store information  and use script triggers to store and eventually write out my audit record.

            • 3. Re: Creating an "audit" table
              JantzSelk

              OK. so the purpose of the audit form is to again list the same ingredients as found in the original chemical formula. Along with this the audit form lists more detailed info about the specific chemicals used. For example. if the original formula calls for the 20MG of ascorbic acid, the audit form would again display this along with the specific lot number, and expiry date of the ascorbic acid. To go back to the original "recipe" idea, the first table is the original recipe, and the audit form needs to be created everytime we actually make that recipe, to show that we in fact followed the recipe correctly. Hopefully this helps explain what i'm trying to do! 

              • 4. Re: Creating an "audit" table
                aammondd

                Ok in this case audit is a process of verifying the original record.

                In this case you will need to establish at which point in the original process you want data in  the audit record to be established.

                You could do this a number of ways.

                You can create the audit record at some point along the original data entry process or you could create the autid record when you decide to perform the audit I prefer the later.

                I cant swear that my process doesnt have some short cuts that can be taken but the general way I would do it is

                First I would define my audit record to have a number of _fk (foriegn key fields) the same ones the original record had for itself and displaying any referenced information.

                I would define a table of global fields to hold all these keys.

                I would define a relationship between the original record and the audit by the originals id to the _fk field in audit.

                I would also define any other key relationships to other tables

                Desgin my audit layout to display any orginal record fields as related fields I want to display along with any other referenced data

                From a layout where the orginal recepie record you wish to audit is execute a script to pass all that records relationship keys into a table of global fields

                Move to an audit record layout create a new blank record and set the relationship key fields in the new record.

                Some things to think about here is omitting all records before adding one so you only have the related one in the found set.

                This would allow you  to lookup the original values that were posted in the record and display them as related fields on your audit layout.

                You can apply this same principle for creating a set of recipie items by looping through the orginal recepie items and creating individual audit records  after the main audit header record is created and view them in portals in your audit record layout.

                 

                 

                 

                • 5. Re: Creating an "audit" table
                  JantzSelk

                  OK, that makes sense! i know this is a newb question, but how do i define my fields as primary or foreign key fields?

                  • 6. Re: Creating an "audit" table
                    aammondd

                    Filemaker doesnt care about them being keys but for your own sanity making the field names prefaced with _pK(primary Key) and _fk(foreign Key) really helps you know what fields to link to.

                    A primary key is a field that has a unique value within the table

                    A foriegn key is a field that has a unique value in another table (and can be repeated in this one) and is in this table to establish the relationship between them.

                     

                    • 7. Re: Creating an "audit" table
                      philmodjunk

                      And FileMaker primary keys are almost always defined as auto-entered serial numbers.