7 Replies Latest reply on Feb 7, 2012 7:56 AM by RedFile

    Finding Difference Value Between Two Fields

    RedFile

      Title

      Finding Difference Value Between Two Fields

      Post

      I have a project where up to six people rate a product between 0.1 to 5.0. The script is very repetitive (too long to put here) and performs inconsistently in Filemaker 7 and I cannot afford to upgrade. The Perform button in the Define Script window performs slightly differently than if I run the SAME script from my project button. My Filemaker scripting knowledge is limited, so the obvious may need to be stated.

      The object of the script is to find two votes in the group of up to six that differ more than 1 point and give a result in a message field that asks anyone who is outside the range of most voters to "challenge" the other voters.

      I used the IsValid and IsEmpty functions to determine which field votes to include called "Voter1 Ave", "Voter2 Ave",…

      I have isolated the problem to be in the following script, which I just don't see, but I am sure it will be easy for someone else. I thought it would be a space or parentheses -- not much difference when I change them. I have tried placing parentheses in different places, but the script still jumps around. This script finds votes outside the mode of the other voters when Voters 1,2,3 and 6 are voting and 4 and 5 are blank.

      IsValid ( Products::Voter1 Ave ) and IsValid ( Products::Voter2 Ave ) and IsValid ( Products::Voter3 Ave ) and

      IsEmpty ( Products::Voter4 Ave ) and IsEmpty ( Products::Voter5 Ave ) and IsValid ( Products::Voter6 Ave ) and

      ((Products::Voter1 Ave - Products::Voter2 Ave) > 1 or  (Products::Voter1 Ave - Products::Voter2 Ave) < -1 or
      (Products::Voter1 Ave - Products::Voter3 Ave) > 1 or  (Products::Voter1 Ave - Products::Voter3 Ave) < -1 or
      (Products::Voter1 Ave - Products::Voter6 Ave) > 1 or  (Products::Voter1 Ave - Products::Voter6 Ave) < -1 or
      (Products::Voter2 Ave - Products::Voter3 Ave) > 1 or  (Products::Voter2 Ave - Products::Voter3 Ave) < -1 or
      (Products::Voter2 Ave - Products::Voter6 Ave) > 1 or  (Products::Voter2 Ave - Products::Voter6 Ave) < -1 or
      (Products::Voter3 Ave - Products::Voter6 Ave) > 1 or  (Products::Voter3 Ave - Products::Voter6 Ave) < -1)

      Insert Text (Select; Products::VoterResult; "1,2,3 and 6 Meet"

      Perform Script ["Voter Challenge"] .....

        • 1. Re: Finding Difference Value Between Two Fields
          johnhorner

          hi redfile,

          i am not an expert either, but it seems to me there could be a couple things interfering with the scripts ability to execute properly.  for one, as there are roughly 50 values to choose from (assuming the voters can input ratings between 0.1 and 5.0 in increments of 0.1) it seems to me that it is quite likely there will be no mode (i.e. there will be 6 different votes... no duplicates).  so whatever field or variable you are using to store the mode will be empty quite frequently.

          a second potential problem, is that your "isvalid" steps don't prevent the field from containing numbers outside of your desired range.  a "0" or a "-11.3" or "123,498" would all evaluate as valid and could be throwing off your other calculations unless you are validating the range some other way (field level validation or with a script, for example).

          one thing you could do to simplify the calculation would be to use the absolute value function. i am pretty sure that  the expression: abs ( Products::Voter1 Ave - Products::Voter2 Ave ) > 1 will yield the same result as: (Products::Voter1 Ave - Products::Voter2 Ave) > 1 or  (Products::Voter1 Ave - Products::Voter2 Ave) < -1.

          i also don't undetstand why you need to test for scenarios where there are specific configurations of votes cast.  the mode, if there is one, will be the mode regardless of the number of voters casting a valid vote, and determining if they are more than 1 away from the mode also does not require any knowledge about the number of other voters.  even if you do need to test for these different scenarios, which i assume means you essentially have this set of steps reproduced somethign like 100 times for the different combinations of potentially empty fields?  i'm pretty sure you could reduce this way down using more efficient test.  off the top of my head i think you could do it in at least as few as 15 lines.  somethign like: abs ( Products::Voter1 Ave - Products::Voter2 Ave ) > 1 and not isempty ( Products::Voter1 Ave ) and not isempty ( Products::Voter2 Ave ).  then repeat for each unique pair of fields (15 total... i think?)

          i am also thinking perhaps you might actually be using the mean or median value?  what is "voter1 Ave", is it simply their vote or an average of something?  i don't see where your calculation is comparing a vote to the "mode".  it seems like you are only comparing either votes to other votes, or averages to other averages.

          one final thing i notice, that is not really a problem as far as i know, but that is the use of spaces in your field names.  i know there are a lot of different opinions about filed naming conventions, but it seems like most of them avoid the use of spaces... i don't why, but it is something i have noticed.  and especially when i don't understand the reasoning behind something, i tend to copy what seems like the most common practice.  i'm sure there are others who may disagree.  hopefully someone with a better grasp of this than i will be able to help out.

          good luck!

          • 2. Re: Finding Difference Value Between Two Fields
            philmodjunk

            I think using separate records for each vote might make for much simpler processing of this data instead of a series of separate fields in the same record.

            Spaces in you field names aren't likely to be the source of your trouble here. Defining field names that do not include spaces makes for slightly easier editing (a double click can select the entire field name instead of a part of it) and can reduce syntax errors that trigger error messages when you click OK to close the specify calculation dialog.

            • 3. Re: Finding Difference Value Between Two Fields
              RedFile

              I realized I couldn't really get an accurate mode, especially on just 3-6 votes -- I probably meant meridian. This is why I am instead finding those votes that vary by one point. I didn't want a different record for each vote, because I have at least 50 other fields per record or product.

              I want the Isvalid to just validate any number, in the voter's field, hoping that "0" equates to valid and a blank equates to not valid or IsEmpty.

              Thanks for pointing me to the absolute value, I just didn't think Abs meant absolute.

              Ré:i also don't understand why you need to test for scenarios where there are specific configurations of votes cast. Not too sure what this means, but what I have is about 7 scenarios, depending on which Voter fields are valid, each with similar scripts to find votes that are >1. I wrote a simple script that would ask for more votes if less than 3 people vote, to avoid writing more scenarios.

              Yes "Voter1 Ave" is the average of different votes for quality, ease of use, design…

              Thanks again for naming conventions - guess it would help for double clicking purposes.

              • 4. Re: Finding Difference Value Between Two Fields
                RedFile

                The problem appears to be in the "Isvalid" and "IsEmpty" and "and" functions. It could be all the elseif commands too. I really hate trial and error.

                • 5. Re: Finding Difference Value Between Two Fields
                  RedFile

                  I did the long way around and fixed the problem by having a 8 different scripts for each scenario as the "and" "or" operators seemed to mess up the script.

                  I also added two separate scripts for the dialogue box: one for votes that are out of the range of one point and one for votes that are withing.

                  ---

                  If

                  [IsValid ( Products::Voter1 Ave ) and IsValid ( Products::Voter2 Ave ) and IsValid ( Products::Voter3 Ave ) and

                  IsEmpty ( Products::Voter4 Ave ) and IsEmpty ( Products::Voter5 Ave ) and IsValid ( Products::Voter6 Ave ) ]

                  Insert Text (Select; Products::VoterResult; "1,2,3 and 6 Need to Meet for Product Debate"

                  Perform Script ["Voter Challenge1236"]

                  Exit Script

                  End If

                  If [IsValid… <<<<<<<<<<<<<<<<<- this continues on to other scenarios calling up the other "Voter Challenge" scripts.

                  ---

                  Script "Voter Challenge1236" is:

                  ----

                  If

                  Abs (Products::Voter1 Ave - Products::Voter2 Ave)  > 1or
                  Abs (Products::Voter1 Ave - Products::Voter3 Ave) > 1  or
                  Abs (Products::Voter1 Ave - Products::Voter6 Ave) > 1 or 
                  Abs (Products::Voter2 Ave - Products::Voter3 Ave) > 1 or 
                  Abs (Products::Voter2 Ave - Products::Voter6 Ave) > 1 or
                  Abs (Products::Voter3 Ave - Products::Voter6 Ave) > 1

                  perform script "Voter OutofRange script"

                  Else

                  perform script "Voters Agree script"

                  Insert text [Select; Products::VoterResult; "All Voters agree within range."

                  End If

                  End Script

                  ----

                  Since this script comes after the initial script the first VoterResult message is replaced with the second "All Voters agree within range." It gets a little more complicated than this as I also had a second message in another field that recorded how well the product was received.

                  Tedious, but easier and a lot faster than figuring out the Repeat, Get… and other functions that may have shortened the repetitive script.

                  • 6. Re: Finding Difference Value Between Two Fields
                    johnhorner

                    hey redfile... it sounds like you have everything worked out... glad to hear that.  i noticed phil had suggested a structural change of sorts.  he has helped me with more things than i can remember and he really know his... stuff.  so i would strongly encourage you to consider his suggestion seriously... it could greatly simplify your solution.

                    before i saw that you had resolved your issue, i was thinking more about your problem and i think a couple of observations may still apply.  first, you mention your intent with the "isvalid" test is to catch empty fields.  i don't think this is how it works.  i think an empty field evaluates as being valid.  i looked up the definition in the "filemaker pro advanced help" section, and while it does not specifically address empty fields, the conditions that result in isvalid being true ("1"), are things such as a data mismtach (exclusively text when a field is defined as a number field, or numbers when a field is a date field, etc).  they list a couple other conditions that evaluate true, but being empty is not one of them.  you may need to either replace it or add on to it a "not isempty" test if that is what you are trying to prevent.  i am pretty sure that the section of code you posted last will evaluate true if all 6 votes are empty and, if it is the first test you perform, you will be calling the script "Voter Challenge 1236" even though all fields are empty.

                    this would have another possibly unintended consequence depending on how filemaker evaluates the "greater than" operator when comparing an empty field to one containing a number greater than 1.  for example, i don't know if your calculation: Abs (Products::Voter1 Ave - Products::Voter2 Ave)  > 1 is true or false when "voter1 Ave" is 3.5 and "Voter2 Ave" is empty.  maybe it's fine but it might evaluate true in which case you script will not produce the intended result.

                    about the different scenarios.  i am assuming that this particular piece of code is attempting to test for a specific condition where exactly 2 of the fields are empty (namely, 4 and 5).  i also assumed that you were therefore testing for every other condition where 2 fields could be empty.  i came up with 15 for this as the following combinations of 2 empty fields exists: 1-2; 1-3; 1-4; 1-5; 1-6; 2-3; 2-4; 2-5; 2-6; 3-4; 3-5; 3-6; 4-5; 4-6; 5-6.  additionally if you were going to test for situations where there were 3 empty fields (of which there are even more than 15 combinations), and/or 4 empty fields, and/or 5 empty fields and/or 6 empty fields, that's a lot of tests (a little over 50 i think) but, of course, quite possible.  hope that is helpful.

                    • 7. Re: Finding Difference Value Between Two Fields
                      RedFile

                      I tried the Not IsEmpty and didn't seem to make a difference, probably because the "Voter Ave" fields were number fields.

                      Yes, The purpose of the scenarios and IsEmpty /IsValid functions was to determine if the fields were empty and not to include them in the script. So Abs (Products::Voter1 Ave - Products::Voter2 Ave)  > 1 is true because those fields were already checked to see if they were valid with a real number ;  If one was "empty" it would result as a "0" in the calculation. I just tested it with a real "0" in the field and it calculates it correctly as if a Voter gave the product a "0", meaning their vote would be valid.

                      I "simplified" the 15 scenarios into 8 by requiring to physically use the first 3 fields or voters. When I have more time, I will add them to the mix. It's like writing the names down on a form; it's not that common for someone to skip the first 3 lines. Just the way I layed out the Filemaker page; I put the first three at the top. If someone scrolls down to the other three without first filling out the first three, they just have to do it over and put them at the top ; )

                      Thanks for the input, everyone.