1 2 Previous Next 16 Replies Latest reply on Jan 6, 2015 4:18 PM by DustFairy

    Index Missing



      Index Missing



      I am trying to create a data base to allow me to do inspections of machines and complete a check sheet while doing the inspection.  Wen the inspection is complete the database should send an email to the owner with a list of comments.


      Thus far I have a Company table, Unit table and Checksheet table.  A primary field in Company links to a foreign in Unit.  A primary field in Unit links to a foreign in Checksheets.  At this point I have the system working properly in creating check sheets for each unit and numbering them the way I want them to. 

      On the Checksheet I have a field that calculates the Checksheet number.  My next step was to work out the Comment sheet that I email to the customer.  My idea was to have a fourth table "Comments" that was a "one-to-one" relationship with the Checksheet (possibly not the best database solution, but this is my first db).  The Comment sheet would be a series of Look-ups that checked each Comment field on the Checksheets and returned the comment to the Comment sheet.  If the comment field on the Checksheet was empty the look-up would return nothing.  (This idea may not work if the Look-up that sees nothing in the comment field actually puts a blank line in the Comment sheet.  I don't want to send a customer a page full of blank lines and one or two comments in amongst the blank lines.  But that's the next problem.)

      I tried to set up a new relationship between the Comment and Checksheet using the Checksheet number (a calculated value ) and now I get an Invalid Index showing on my Comment sheet.  

      I have been in the Forum and see that my mistake is that the calculated Checksheet number is not indexed and won't work as a primary key.  From the Forum I see that my solution will probably be a second Table Occurrence but when I try to follow the examples I find in the Forum (one for a fellow with an Omit/EmployeeID problem and another link to an explanation of Table Occurrences) I get lost.

      Can anyone suggest where I go from here?

      Happy New Year




        • 1. Re: Index Missing

          If it's a one to one relationship, you don't actually have to have such a table at all, you can simply use one table for both the checksheet and comments data. (and your look up scheme won't resolve your need to omit blank fields.)

          In the long term, it sounds like you have a single record in CheckSheet that should be broken up into individual related records. Then you can omit "blank" responses easily as there would be no related record for that response. See this link on Survey/questionnaire databases for possible ways to set that up: Need aid on generating a report from a survey layout.

          In the short run, you can either not use an additional table in a one to one relationship, or you can define an auto-entered serial number field in your checksheet table to link to related records in your comments table. It won't matter what value is auto-entered into this field in CheckSheet as long as it is a unique, indexed value.

          • 2. Re: Index Missing

            Thanks, I will try your suggestions and probably be back in a few days with more questions.  

            I am trying to solve this one step at a time.  My first attempt at making the entire database in one go failed miserably.



            • 3. Re: Index Missing

              One step at a time is the best way to go. That's why I provided multiple suggestions with some a "short term" fix and others more of a "long term" fix as they require redesigning portions of your database.

              • 4. Re: Index Missing

                I went to the link you suggested.  Most  helpful.


                I see my situation being:

                Inspection----------<Comment>--------Inspection point>--------Machine type

                The Inspection is each new inspection I carry out and info such as machine owner, unit # etc would be retrieved from existing tables.  I would have a table of Inspection Points and could create different Inspection Checksheets for each Machine type.

                In my situation I have a pop-up menu for each Inspection Point.  The pop-up has four options, OK, Rej, N/A and N/O.  The pop-up is not really important except that I would like save a copy of each Inspection checksheet for future reference.  Next to each pop-up I would have a Comment field and the Comment would be completed only if the pop-up selection was "Rej".  I can not think of a reason why I would want to search my inspection checksheets to determine how many responses to a particular inspection point was OK, Rej, etc.  I do want to search for the Comment fields that have been filled in for each Inspection Checksheet to produce the Report I email to the Owner.  

                So to summarize I think I am going to end up with the following:

                Company-----<Unit-----<Inspection------<Comment>------Questions>-------Machine Type

                The right hand side of the above may be more than I need.  I don't know that I need a table for Machine Type.  Seems to me each Machine Type would just be a different Layout from the Questions table.   I just can't see why I would need to search and sort data according to Machine Type.

                At this point I have things set up in my tables and relationships to get an Inspection checksheet when I select a Company and Unit.  The checksheets have a calculated checksheet number and you mentioned adding a auto enter serial number to solve my "Index Missing" problem.

                My next step seems to be to set up my Inspection Point table and ensure each Comment field has an auto-enter serial number.

                I want the Inspection Points on my Checksheet to have the following look:

                Inspection Point 1    "Pop_up"       Comment field------------------------------------------------------------

                Inspection Point 2    "Pop-up"        Comment field------------------------------------------------------------



                Now do I need a separate field in the Comments table for each Inspection point or can I just use the same field (serialized) repeatedly in Layouts?  Seems to me I can use a serialized Comment field that generates it's own record each time the Comment field is entered.  Then when I finish the inspection and tap the Report button I will have a script set up that searches the Comment fields and adds those with data in them to the Report that I email to the owner.

                Hope you can understand the above.  Many thanks.

                • 5. Re: Index Missing

                  The whole point to the above data model is to NOT have a separate field for each inspection point as that becomes a very inflexible way to go. Take another look at your data model. The records in Comments (which you might choose to rename) will be one record for each inspection point for the purpose of recording the value selected in the pop up menu and also any comment associated with that point. The layout you describe would simply be a list view layout were each row of the layout is a different record for a different inspection point.

                  A key part of this process will be a script that generates a set of records in comments for a new inspection so that all you need do is click a button to set up your system to record data from a new inspection. You don't have a specific need for a serial number field in comments though such may prove useful in the future. What you will need are serial number fields in Inspections and InspectionPoints/Questions so that the records in these two tables can link to specific records in comments.

                  • 6. Re: Index Missing

                    I have changed Comment to Result.


                    Is this on the right track:

                    Company            Unit               Inspection           Result             Inspection Pt           Machine Type          (Tables)


                                            pkUnitID-------<fkUnitID                                                                                                      ( Table Fields )                              

                                                                     pkInspID-----<fkInspID            fkMachType>---------pkMachType



                    • 7. Re: Index Missing

                      It looks reasonable.

                      • 8. Re: Index Missing

                        When you say I will need serial # in my Inspection Point/Questions do you mean a serial # for each Insp Pt or just in the pk InspPt field?

                        I put a serial number in for pk Insp Pt and each point and now they all show the same ID #.  

                        You mentioned in the link for the Survey that a Survey Table would allow for different Survey question banks.  I put in a Machine Type table to allow for different Inspection points for each machine type.  The example in the link doesn't really expand on how to generate the different question banks.  I tried making a couple of layouts for the Inspection Pt table but that didn't seem to work on the first try.



                        • 9. Re: Index Missing

                          do you mean a serial # for each Insp Pt or just in the pk InspPt field?

                          I read that as one and the same thing. Each record in Inpection Point needs a field that uniquely identifies each field in the table. That field is often named with a "pk" as a way to identify this field as it is called the "primary key" in database terminology.

                          and now they all show the same ID #.

                          In what field placed on what layout based on what table occurrence?

                          A script that starts from a specific survey record (machine type record) can then pull up a found set of question records linked to that survey or inspection point records linked to that machine type. The script can then use that set of records and the ID of the respondent (the inspection record) to generate a series of new records in (responses/result) each linked to the same respondent (inspection record) but linked to a different record in that found set of questions (Inspection points).

                          This can be done in more than one way in such a script. Import Records can pull the needed data from InspectionPoint into result followed by a replace fields operation to assign them the same Inspection ID. Or a looping script can loop through the same set of inspection point records one at a time to create the needed result records.

                          Either way, you then get a set of result records linked to the correct inspection and inspection point records ready for you to record data on each inspection point for that inspection.

                          • 10. Re: Index Missing

                            In my Inspection Pt table I defined the pkfield as a auto serial # field, then for each inspection point (there will be up to 80 or 90 in the table) I have defined each of them with an auto enter serial #.   I'm starting to think you meant just the pk field.

                            • 11. Re: Index Missing

                              In your field naming convention if a field name ends in ID does that mean it has a auto enter serial #?  

                              • 12. Re: Index Missing

                                In your field naming convention if a field name ends in ID does that mean the field has an auto enter serial #?

                                • 13. Re: Index Missing

                                  Like I said, it's one and the same thing. Yes, you just need a pk field to uniquely identify each record in the inspection point table.

                                  If the field names starts with __pk, I identify it as a primary key and usually, that means that it auto-enters a serial number, but an auto-entered call to Get ( UUID ) in a text field can also function as a __pk field. A field that ends in ID may be an auto-entered serial number or it may be a plain number (Text if using UUID's) that matches to the __pk field in a relationship. Foreign key fields are identified by a _fk.

                                  • 14. Re: Index Missing

                                    Sorry but I was totally confused on the Inspection Pt table.  I was thinking if I have 80 inspection points that the table would have 82 fields.  The pk and fk fields plus the 80 inspection points.  

                                    I now think that the inspection point table would have a pk, fk, a text field for the description of the inspection pt and another field to identify which type of machine the point referred to.  Then I create 80 records, one for each inspection point.  When I do an inspection a script searches for the inspection points that apply to that machine type.  

                                    Is that correct?

                                    1 2 Previous Next