AnsweredAssumed Answered

help with relationship and calculations

Question asked by isamudysan on Apr 3, 2014
Latest reply on Apr 9, 2014 by 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.

Outcomes