9 Replies Latest reply on Nov 22, 2011 11:03 AM by philmodjunk

    Generating an inspection report based on tables

    EdwardBatt

      Title

      Generating an inspection report based on tables

      Post

      I have two tables:

      One table contains step by step quality inspection plan (Table 1) for use by company inspectors. Each inspection plan is made up of multiple lines, but has a unique identifier. 

      These inspection plans are intended to be used in inspection reports, so I have a corresponding table (table 2) which contains the inspection points for each line of the inspection plan. 

      When an inspector wants to generate a report, he opens the reports table (table 2) and selects the report number, and the inspection plan number. The report automatically drags across the inspection plan steps and populates the first half of the table with the inspection steps required. The inspector can now simply fill in the checkboxes for the inspection points required for each line, and enter his comments for the report. So far great.

      The problem is that I need to use the same plan multiple times, so every time another inspector uses the inspection plan a new report is generated, so each line of the report in table 2 should also pick up the unique report number. I cannot seem to get this to work. What I need to do seems relatively simple, but I simply cannot figure out how to make it work. Any help woudl be much appreciated. 

       

        • 1. Re: Generating an inspection report based on tables
          philmodjunk

          What exactly have you defined as your relationship between these two tables.

          Which table defines the ID number matched to by records in the other table?

          Do you need to use exactly the same set of inspection steps from table 1 multiple times or do you need to review the steps for possible changes each time?

           

          • 2. Re: Generating an inspection report based on tables
            EdwardBatt

            Please see the attached picture. I have an inspection plan register for registering a unique identifier. This is then used to track the line items of the inspection plan. 

            I have a report register for registering unique report numbers. This should be then used to track the line items of the reports. 

            For the most part we need to use the inspection plan multiple times and rarely have to review the steps. from time to time they will be reviewed, modified and then locked again. Each step in the inspction plan can range from a few words to small paragraphs. The inspection report itself is a series of checkboxes and comments. 

            I think the attached picture should make things a little clearer. 

            • 3. Re: Generating an inspection report based on tables
              philmodjunk

              How do you use this relationship:

              QIP Register::QIP Number = QIP Section 1 details::QIP Number

              I would have expected QIP Number to be an auto-entered serial number in QIP Register but can see that it is not.

              Does one record in QIP Register match to multiple records in QIP Section 1 details or is the reverse true?

              Where I'm headed with this is figuring how you both group inspection results by plan and yet also match each such record to a matching step in your inspection plan. I need to understand the current proces in order to suggest changes.

              from time to time they will be reviewed, modified and then locked again

              And what effect do those changes have on past inspection reports? Have you considered the possibility that these changes could alter past inspection reports? You may need to create a script that duplicates your existing plan so that you can then make changes to this copy for use with future inspections so that existing inspection reports are not altered by your after the fact updates...

              • 4. Re: Generating an inspection report based on tables
                EdwardBatt

                QIP Number is calculated field made up of "QIP"&Division&Number&Client&

                The relationship is one to man, so yes, QIP Number in the register is Unique, and is assocaited with multiple lines within QIP Section 1. 

                As you can see (although minimised) there are several sections for one Inspection Plan, each section has the same relationship, so QIP Number has a one to many relationship with each of the sections. 

                If you look at the Section 1 Report, you can see that there are a number of duplicate fields, my theory being that Section 1 would pick up and store each line item from QIP Section 1 on the date the inspection is carried out, so the inspector only sees the most up to date report. This should avoid any issues of changes to the inspection plan affecting existing reports. 

                 

                In order to differentiate each actual report, I need to have a unique report number with a one to many relationship in from Report Register to Section 1 Report Details. However I can only appear to set the relationship up and get it working with either the QIP number or the Report Number, I cannot seem to get the two to work together, so either I get a report number for each line item in the report and the QIP number is blank, or I get the QIP number for each line of the report, and the Report Number is blank. 

                I've tried to come up with a simpler approch to the data itself, but am afraid that's not possible (or at least I have not nutted it out yet). I am not even sure Filemaker is capable of what I am trying to do, but I am still a novice and beyond basics need to learn a lot more it would appear. 

                • 5. Re: Generating an inspection report based on tables
                  philmodjunk

                  If you look at the Section 1 Report, you can see that there are a number of duplicate fields, my theory being that Section 1 would pick up and store each line item from QIP Section 1 on the date the inspection is carried out, so the inspector only sees the most up to date report. This should avoid any issues of changes to the inspection plan affecting existing reports.

                  Yes, but it won't work that way as far as I can see from here and what will you do after you've used the last such duplicated field to document an update to the plan?

                  You'd be better off assigning a unique ID serial number to your QIP Register table and using that as your Primary Key. A client ID field in QIP Register can link each report to a specific client so that you can then duplicate a record--Which will duplicate everything except the ID number so that you can generate as many revisions as needed to your plan without ever having to redesign your system to accomodate a new update to it.

                  I see what looks like two occurrences of the same table: Section 1 Report Details, which appears to be used to record the inspection results. What I don't see are any matching occurrences for recording data for the remaining sections....

                  Just so you know, I'm leaning towards a pretty radical redesign of your system to accomodate multiple uses of the same inspection plan and in a much more flexible format. I've been asking these questions to make sure that this approach works and to help figure out how best to describe such a set up so that it works for you...

                  • 6. Re: Generating an inspection report based on tables
                    EdwardBatt

                    the two occurrences of the same table are due to the relationship issues between Report Number on the one side and QIP Number on the other side. Needless to say, didn't achieve anything, I just forgot to delete it before sending a snap shot. 

                     

                    I have not created the other Sections for report details yet. Logic being if I could get Section 1 Report Details working, then I could apply the same to the rest. 

                    For the most part the other sections are supposed to work identically, however there are a few more or less inspection points depending on what section we are working on.

                     

                     You'd be better off assigning a unique ID serial number to your QIP Register table and using that as your Primary Key. A client ID field in QIP Register can link each report to a specific client so that you can then duplicate a record--Which will duplicate everything except the ID number so that you can generate as many revisions as needed to your plan without ever having to redesign your system to accomodate a new update to it.

                    Not sure how the above would work, but will play around to see if I am understanding you properly. The QIP is the plan, which if modified for a different client would have it's own unique ID (QIP Number), however to differentiate between the updated plans for individual client I could use a Revision number, which would change the QIP number and keep it linked to the same client. On the other hand I am still lost on how to tie in individual report numbers. I must be missing something. 

                    Sorry if I am a bit slow on the uptake, but I really appreciate your effort in trying to understand my problem and help me out. 

                    • 7. Re: Generating an inspection report based on tables
                      philmodjunk

                      I'm still feeling my way towards a best approach. I raised a side issue about QIP numbers and revision control but it has no direct bearing on the issue of logging results from different inspectors for the same inspection plan. (I would include a revision number and/or an effective date to track revisions, but I would not include this as part of the QIP number. I'd want to see a link from client that listed all past and present inspection plan versions with filtering or sorting used to display only the current plan when such a display is needed.)

                      I'm thinking that this structure makes more sense and leads to much easier reporting and results analysis:

                      QIP Register ----<QIP Steps-------<Inspection Results>----QIP Report    (----< means "one to many" )

                      QIP Register::QIP number = QIP Steps::QIP number

                      QIP Steps::QIPStepID = Inspection Results::QIPStepID

                      QIP Report::ReportID = Inspection Results::ReportID

                      With this approach, you can add as many steps as needed to any given plan by adding records in the QIP Steps table. (QIPSteID would be an auto-entered serial number in QIP Steps.)

                      When an inspector needs to record the inspection results, they start a new record in QIP Report (assumes one 'report' per inspection by one inspector.), selects the number of the appropriate inspection plan by selecting/entering the QIP Number for it and then presses a button to kick off a script that pulls up the QIP Steps and creates one matching Inspection Results record, entering the QIPStepId to link it to a step record and entering a ReportId to link the same record to a specific QIP report record. (Instead of QIP Report, you could use a table of Inspector records with InspectorID's instead of Report ID's.)

                      Note that you could use this same basic idea with a separate table for each inspection step like you have currently, but this is much less flexible and you'd need multiple occurrences or tables for recording the Inspection Results, each linking a specific QIP to a specific Report and/or Inspector. (by specifying the QIPStepID, you directly specify a record in the step table and through that record can access any needed fields in the QIP Register table.)

                      • 8. Re: Generating an inspection report based on tables
                        EdwardBatt

                        I think I'm with you, I'll try to set to set up what you have outlined above and play with it, although I will need to do a stack of reading on the script side of things. My script capability is virtually non-existent, I can set up a script on a button to pull up a specific layout and that's about it. I will pore through the manuals and tutorials tonight. 

                        The only reason I have the separate tables for inspection results, is that the inspection checkpoints for each step vary. For example, for one piece of equipment, in section 1, we have 7 steps in the QIP, and there will be 2 checkpoints for each step in the report. 

                        In section 2 and 3 there are 20 - 30 steps in the plan, and about 8 checkpoints for each step in the report. If I follow your outline above though, I can set up both the inspections plans and the reports as TAB views, which keeps everything organised to a degree. 

                        Thanks very much for your help, I'll let you know how I get on with your suggestions. 

                        • 9. Re: Generating an inspection report based on tables
                          philmodjunk

                          For an inspection plan with 7 steps, you'd create 7 related records in the QIP Steps table. For an inspection plan with 30 steps, you'd create 30 related records in the same table. A portal to QIP Steps on the QIP Register table would list all the related steps and this can be a convenient way to edit the steps when setting up your plan. Each check point can be a specific step in the plan and you can add a field to the QIP Steps table for identifying which steps are check points if you find this useful.

                          One of the advantages to this approach is that you can print or display all the steps for your inspection plan from a single layout. A similar format that includes inspection results from one or several inspections can also be easily produced.