3 Replies Latest reply on May 6, 2016 5:22 AM by ninja

    Fields as questions to get info..

    gamezfher

      Hi

       

      Hope, someone could help me..

       

      I have a Database for Inspection plans

       

      I have a parent table as header for main information called: Plan 

      And have a child table with all points to inspect for each inspection plan called: Method

       

      Table Plan contain:

      Id_plan

      Part number

      Order

      Date

      Revision

       

      Table Method contain:

      Id_method

      Id_plan

      Characteristic_to_evaluate

      Method

       

      Until here is not a problem, i have a layout with the parent as header and a Portal with all child records on it working very well, with this i have stored my plan with all characteristics to evaluate for the operator.

       

      My problem is now that when i want to use a Inspection Plan to inspect a Product can't use the Method field as Questions in a layout and stored the answers in another table..

       

      Example of information stores in Child table

       

      Characteristic to evaluate: Review that the dimensions of product.

      Method: Vernier

       

      So, then i need a layout or something that when user type the ID_Plan ask to user for Review the dimensions of product, and user could store the dimensions that he or she got with the vernier and all that data could be stored and reviewed...

       

      Is only an example, each Inspection Plan could have many Characteristic to evaluate.. so we need question to user each characteristic and he or she type the results..

       

      In addition all plans can be used N times as times we run the product for different orders, and any time we need to question the same to users and they have to respond the same questions..

       

      Thank you to read me and sorry for my english is not to good hope you understand my idea..

      Regards!

        • 1. Re: Fields as questions to get info..
          Extensitech

          I'll try to take a stab at this... I believe I recognize this challenge from a couple of solutions where I've had to create a sort of "questionnaire" setup. (To try and make this understandable, I'm using your table names Plan and Method, although where I've set it up I have different tables like Questionnaire and Questions.)

           

          First, as you've noted, you'll need a separate table for Answers, which has a foreign key for Plan and a foreign key for Method.

           

          Problem is, your Method records are independent of any particular Plan, while the Answers need to be just for one particular Plan. So a portal of Plan_Methods can't be (easily) linked to Plan_Methods_Answers, since you don't have a Method::fk.Plan to link to Answers fk_Plan.

           

          If this doesn't make sense, I'll save you some time and suggest I might be off-base already.

           

          One method I've used is to set a global $$Plan on recordload in Plan, to the primary key of the current Plan, and calculate a "fk.Plan_current" (unstored) in Method, thus providing the missing foreign key. Then I can see all current Methods in the Plan_Method portal, and relate-create answers in Plan_Method_Answer. This has worked, but in the end it was... fiddly. It was heavily dependent on the calculated current plan key getting set, and evaluating, and ended up involving refreshes periodically...

           

          Another reason for switching is that when you look at an Answer, you want to know the details of the Method when the answer was entered, not what the Method says now (which could be different if you've updated the Methods).

           

          To address the "fiddliness" and the requirement to show the Method as it was at the time, I've switched to scripting the addition of Answers when a Method is added, and/or adding answer records for any "missing" methods onRecordLoad. The Answers table does a lookup for Method details, so they can be "frozen" and won't change if a Method later gets added. The answers don't actually have an "answer" yet, but a record exists for any answer we're going to need, and those answers have a Plan key, so it's a simple relationship.

           

          In order to script the addition of Answers to a Plan, you can either use an off-screen portal with a separate relationship that allows creation, or (this is my preferred way) you can export the Methods to a tab-separated file in the temp folder, import those into the Answers table, then replace the Plan key.

           

          HTH

          Chris Cain

          Extensitech

          1 of 1 people found this helpful
          • 2. Re: Fields as questions to get info..
            gamezfher

            Thanks for your fast answer.. i'll try your suggestion, i'm not sure i can do that at first but i'll try.. this is the most complicated filemaker solution for me..

             

            Regards!!

            • 3. Re: Fields as questions to get info..
              ninja

              Another possible way to approach this (based on one big assumption that each test gives one result or answer) is to simply hold the Test and result in the same record...or in a one to one join.

               

              Plan--< MethodandResult

              or

              Plan--< Method -- Result

               

              If there is only one result per test, your Method table could be

              Table Method contain:

              Id_method

              Id_plan

              Characteristic_to_evaluate

              Method

              Result

               

              This approach would still let you create a value list of Methods so it is easy to populate tests on a new plan.

              And it would let you show the result on your main Plan layout in the same portal.

               

              And even if a method gives more than one result, the grandchild table of Result can simply be a one to many join by Id_Method, and show via portal on a Method layout.