6 Replies Latest reply on Apr 9, 2014 9:24 PM by isamudysan

    help with relationship and calculations

    isamudysan

      this is my first try at FM. i'm very new to the relationship concept (although i have watched some VTC vids on it a few times), calculations,

      and scripting. scripting questions will have to come later. for now, though, i'm in a dire need to solve a problem that i have regarding a

      solution that i have created. please bare with me while i explain what's going on here.

       

      i have 2 tables -- one is called "data_capture" and the other is called "failcodes." the data capture table has 7 fields, 6 of which are used to enter

      the failcodes. the 7th (called judgement) is an auto-enter (browse mode checked off in inspector) that will auto-enter the judgment of the

      first 2 failcodes.

       

      generically, data_capture table looks like this:

       

      visual test (drop-down list, and the value list is failcode::failcode)

      functional test 1 (drop-down list, and the value list is failcode::failcode)

      functional test 2 (drop-down list, and the value list is failcode::failcode)

      functional test 3 (drop-down list, and the value list is failcode::failcode)

      functional test 4 (drop-down list, and the value list is failcode::failcode)

      functional test 5 (drop-down list, and the value list is failcode::failcode)

      judgment (displayed data from failcodes::judgment, edit box)

       

      as stated above, the 2 most important fields for the judgement field are the visual test field, and functional test 1 as their info is critical and cannot

      be left blank. an exception to this rule is when 1 of 2 ultimate failcodes are entered into the visual test field, thus the user will enter no failcode into the functional test 1 field.

       

      now, table 2, titled failcodes, has all the failcodes and their respected judgment. there are 3 judgments, let's called them DEF1, DEF2, and DEF3. for example, a failcode of M013 has a judgment of DEF1.

      in total, there are 45 or so failcodes. so, in failcodes table, i have 3 fields:

       

      failcode description judgment

      M01 DEF1

      M02 DEF1

      M03 DEF1

      M04 DEF1

      M05 DEF2

      M06 DEF3

      M07 DEF1

       

      and so on....

       

      the problem is that the judgment field can only have 1 judgment -- DEF1, DEF2, or DEF3 -- between visual test and functional test 1 (again, as these 2 are the most important fields).

       

      onto relating the 2 tables: in relationships i have a relationship between data_capture::functional test 1 and failcodes::failcodes. the result of this relationship is this, if i select a failcode from the drop-down list in the visual test field, there is nothing auto-entered in the judgment field.

      if i select a failcode in the functional test 1 field, the judgment field auto-enters correctly. i am safe to assumee that that is the case because of the relationship betgween the 2 tables? it holds true, if the relationship was between data_capture::visual test and failcodes::failcodes.

       

      i know that i need to find a way to resolve this relationship issue, so that a possible calculation can be drawn up in the judgment field to display the correct judgment that is associated with its failcode. my problem is how does one calculate something like this once the relationship has been resolved? it needs to be something like:

       

      if a failcode is entered in functional test 1, then the judgment that associated with that failcode will be auto-entered into the judgment field.

      however, if functional test 1 is empty, then the failcode that has been or entered in visual test field, the judgment associated with that failcode will auto-entered accordingly into the judgment field.

      furthermore, if there is a failcode entered in both fields, then the judgment from functional test 1 will be auto-entered into the judgment field as functional test 1 takes precedence over visual test.

       

      hope that makes some sense, lol. i have read, re-read, and re-read some more regarding the case () statment vs. that of the if () statement in calculations. still, i'm quite lost when it comes to this.

       

      i do greatly appreciate all help that comes this way whether it's small or big. thank you.

        • 1. Re: help with relationship and calculations
          erolst

          See if this helps you. Two things to note:

           

          1. Many things are (much) easier if you use (related) records instead of (multiple similar) fields.

           

          2. Calculations often become trivial if you structure your data properly.

          • 2. Re: help with relationship and calculations
            rrrichie

            The Case statement works like this :

             

            Case(test1; answer1; test2; answer2; .... ; defaultanswer)  e.g

             

            tempIn=18

            tempOut=28

             

            $variable = Case(tempIn<20;"Cold Inside";tempOut<30;"Cold Outside";"Feel for yourself)

             

            $variable wil be Cold Inside cause tempIn is less than 20 and the other tests never get done.

             

            or

             

            $variable Case(tempIn<20;"Cold";tempIn<30;"Warm";tempIn<40;"Pretty Warm";"Hot!")

            • 3. Re: help with relationship and calculations
              isamudysan

              thank you for your attachment. i'm gonna need to look at your file, disect it, and study it.  never occured to me to be using a portal for something like this. gonna have to go back and re-watch the VTC vid on portals again.

              if there are any other vids that will help me benefit on portals, please let me know, thank you.

               

              my apologies for not attaching my FM file in my OP. Due to NDA, i had to clean it out to so it will somewhat similar to yours so it can be upload it to here. please take a look and critique it, and point out the good, the bad, and the ugly.

              i want to get better, and want to be better at building this solutions (and others in the future). my feet is just beginning to get wet, and i like it.

               

              thanks again, and i greatly appreciate your file, thoughts, and input

              • 4. Re: help with relationship and calculations
                isamudysan

                thank you and appreciate your reply, rrrichie . i'm gonna keep on working at it.

                • 5. Re: help with relationship and calculations
                  erolst

                  isamudysan wrote:

                   

                  thank you for your attachment. i'm gonna need to look at your file, disect it, and study it.  never occured to me to be using a portal for something like this.

                   

                  You should try to think primarily in terms of relationships …

                   

                  On the other hand, if you only have six fields, and only two of those are pertinent, you can get by doing this with fields. But you'll still need additional relationships to independently find the judgement values for Functional Test 1 (f1) and Visual Test (v). That's why you didn't get a result for v – your existing relationship can only find Failcodes matching on f1.

                   

                  As for your calculation.

                   

                  1. Always check the result type; your judgements are text, so leaving the result type as number will give you inpredictable results at best, or no result at worst.

                  2. Make sure you define your case tests in a logical order; if e.g. you first check f1 for not isempty (), and then for a specific value to return a different result, the second test will never be reached if f1 is not empty, regardless of its (specific) content.

                   

                  I think you want something like the following (assuming you have two relationships, matching Test::Functional Test 1 and Test::Visual Test, respectively, to Failcodes::failcode  …

                   

                  Let ( [

                    f1 = Test::Functional Test 1 ;

                  hasF1 = not IsEmpty ( f1 ) ;

                    hasV = not IsEmpty ( Test::Visual Test ) ;

                    fJudge = failcodes_forF1::judgment ;

                    vJudge = failcodes_forVisual::judgment

                    ] ;

                    Case (

                      not ( hasF1 + hasV ) ;

                        "No valid failcodes" ;

                     hasF1 ;

                        Case (

                          f1 = "L992" and hasV ;

                          vJudge ;

                          fJudge

                          ) ;

                      vJudge

                    )

                  )

                   

                  … but I'm not entirely sure … This would of course be easier to codify if there was no "L992" exception.

                  • 6. Re: help with relationship and calculations
                    isamudysan

                    thank you for your help. i really like your calculation(s) in your first attachment, and as well as the above post. i hope you don't mind me using it for what i have put together. i do have another question, but it probably deserves its own thread/post. again, thank you very much, erolst