11 Replies Latest reply on Mar 9, 2012 10:30 AM by SeamusDoone

    Function Not working properly.

    SeamusDoone

      Title

      Function Not working properly.

      Post

      Hello I have a field on my database that should be stating whether the total invoice count for each year is showing an increasing trend or a decreasing trend.  Instead it is all over the place and not telling the right answer on some while it is on others.  There is no sense to it.

      Current Equation:  =IF(${2009}>${2010}, "Decreasing", "Increasing")

      What am I doing wrong?

        • 1. Re: Function Not working properly.
          philmodjunk

          What kind of field is 2009 and 2010?

          If they are calculation fields, how are they defined?

          If they refer to data in a related table please describe the relationship linking the tables involved.

          • 2. Re: Function Not working properly.
            SeamusDoone

            2009 and 10 are simply numbers, no calculations because I compiled that data in Excel.  That is why this is so confusing, they are not linked to anything except the total invoice count which is just a simple calculation that adds the years together.  

            • 3. Re: Function Not working properly.
              philmodjunk

              Open Manage | Database | FIelds and check the field types. I predict that they are type "text". Change them to type "Number" and they should evaluate correctly in this expression.

              As text, "2" > "100" is a true statement. Where using numbers, 2 > 100, is false.

              • 4. Re: Function Not working properly.
                SeamusDoone

                No that is not the problem I already did that before posting problem on forum.  I am quite familiar with this function in FileMaker and not sure what is going on but it is definetly not because it is text I have done it as a calculation field and number field and updated the field using these parameters.  What puzzles me is that some are correct and some are incorrect, even though the parameters are set each record has its own answer and about half are correct.

                • 5. Re: Function Not working properly.
                  philmodjunk

                  Can you post some examples of the data, identifying which evaluate correctly and which incorrectly?

                  • 6. Re: Function Not working properly.
                    SeamusDoone

                    Ok, well it is just numbers and they are not coming out correctly.

                    Here is current equation: =IF(2009<2011; "Decline", "Incline")

                    Two correct answers: Incline

                    1)2009:2)2009: 1

                    2010: 42010: 12

                    2011: 132011: 9

                    2012: 42012:

                    Total: 21Total: 22

                    Two incorrect answers: Decline

                    1)2009: 22)2009: 3

                    2010: 92010: 4

                    2011: 132011: 18

                    2012: 12012: 2

                    Total: 25Total: 27

                     

                    • 7. Re: Function Not working properly.
                      SeamusDoone

                      I have even removed the field and recreated equation to no avail.  And I got different results, about half wrong and half correct still.

                      • 8. Re: Function Not working properly.
                        philmodjunk

                        Is there a typo in your latest equation?

                        If ( 2009 < 2011 ; "Decline" ; "Incline" )

                        will always return "Decline" because the number 2009 will always be less than the number 2011.

                        If ( ${2009} < ${2011} ; "Decline" ; "Incline" )

                        Is the correct syntax to compare the values of two fields named 2009 and 2011. Perhaps you left that detail out when you posted. (Note: changing the field names to something other than just a number will eliminate the ${ } notation from your expressions and make them easier to read._

                        It's hard to figure out your data. Did the value run together here when you posted them?

                        did you mean to post this?

                        2009: 2   2009: 1
                        2010: 4   2010: 12
                        2011: 13  20111:9

                        and each column is a different record?

                        2009:  2   2009: 3
                        2010: 9   2010: 4
                        2011: 13   2011: 18

                        What do you see if you click or tab into one of these fields? Does the data shown change--perhaps to display a second line of data?

                        • 9. Re: Function Not working properly.
                          SeamusDoone

                          Yes, as in my first post I have the equation as you have it here: if(${2009}<${2011};"Decline","Incline").  Sorry was writing quickly.

                          Each year is a different field.  and they are part off a record.

                          Yes I meant to post it the way you have it there not sure why it came out the way it did when I posted it.

                          No no other values are contained in these fields only the numbers I have.

                          • 10. Re: Function Not working properly.
                            philmodjunk

                            This is a puzzler. If is a basic function that would be triggering an avalance of bug reports if it didn't evaluate correctly (and I'd be one of those posting reports). Yet I don't see any reason why 2 < 13 would evaluate correctly in the first record and not in the third.

                            Let's do a test here. Define a calculation field as GetAsNumber ( ${2009} ) and select number as the return type. Do the same for ${2011}. compare the values returned to the values in the original fields. Are they the same? If they are, try using these two new calculation fields in place of the original number fields and see if you if function evaluates correctly. You should get the same values, but then the If function should evaluate correctly and does not appear to do so...

                            You might also, just to rule out other possible issues, recover the file and test the recovered copy (even if no problems are reported by the recover) and see if it evaluates these records any differently. If it works correctly, you might try re-indexing these two fields to see if that resolves the problem.

                            • 11. Re: Function Not working properly.
                              SeamusDoone

                              The test worked I set the new equation for the get as number fields and it worked.  Thank you very much this has been a great help.