When is 5 not 5?

[Chuck]

Can anyone explain how this calculation:

List (
  PRSN::c_seventh ;
  Length ( PRSN::c_seventh ) ;
  PRSN::c_seventh = 5 ;
  GetAsNumber ( PRSN::c_seventh ) = 5
)

is returning this result

5
1
0
0

The "PRSN::c_seventh" field is an unstored calculation that I've triple checked returns a Number.

This arose from a more complicated calc that was returning the wrong value from a list.

Let (
  [
    _levels = List (
      "Very Low" ;
      "Low" ;
      "Moderately Low" ;
      "Moderate" ;
      "Moderately High" ;
      "High" ;
      "Very High"
    )
  ] ;

  _levels & ¶ & ¶ &
  PRSN::c_seventh & ¶ & ¶ &
  GetValue ( _levels ; PRSN::c_seventh ) & ¶ & ¶ &
  GetValue ( _levels ; 5 )
)

is returning

Very Low
Low
Moderately Low
Moderate
Moderately High
High
Very High

5

Moderate

Moderately High

 

Edited January 24, 2018 by Chuck

[MonkeybreadSoftware]

If you install the MBS Plugin, you could use FM.DataType function to check the data type.

It may be text comparing to number?

[Josh Ormond]

@Chuck I just put that calc in the data viewer and it returns the correct selection for me.

 

Let (
  [
    _levels = List (
      "Very Low" ;
      "Low" ;
      "Moderately Low" ;
      "Moderate" ;
      "Moderately High" ;
      "High" ;
      "Very High"
    ) ; 

	c_seventh = 5
  
] ;

  _levels & ¶ & ¶ &
  c_seventh & ¶ & ¶ &
  GetValue ( _levels ; c_seventh ) & ¶ & ¶ &
  GetValue ( _levels ; 5 )
)

Very Low
Low
Moderately Low
Moderate
Moderately High
High
Very High

5

Moderately High

Moderately High

 

Also, I tried making it text, it still worked correctly.

40 minutes ago, Chuck said:

Can anyone explain how this calculation:

List (
  PRSN::c_seventh ;
  Length ( PRSN::c_seventh ) ;
  PRSN::c_seventh = 5 ;
  GetAsNumber ( PRSN::c_seventh ) = 5
)

is returning this result

5
1
0
0

 

As for this piece. If you select all and copy. Then throw that in the data viewer, are there any other characters? Seems like there is either a non-printing character or a piece of text in that field.

If you pull up the index, is there anything weird in there? ( Cmd+i or ctrl+i )

[MonkeybreadSoftware]

We had a boolean problem earlier last year:

see

https://community.filemaker.com/thread/179926

[Chuck]

43 minutes ago, MonkeybreadSoftware said:

It may be text comparing to number?

I thought of that, which is why the first example has the "GetAsNumber" function. But `"5" = 5` returns 1, so that's not likely the source of the problem. And even if it were, that doesn't explain why it's getting the item before the one it should.

I can only think of two possible explanations so far. Either there's an esoteric bug somewhere in FileMaker itself or the file is corrupted. But if it's corrupted, then four other very similar fields, differing only in which other field they're referencing, are showing the exact same behavior, which seems unlikely

But, you don't see any obvious error I've made? I'm not going crazy here, right?

39 minutes ago, Josh Ormond said:

@Chuck I just put that calc in the data viewer and it returns the correct selection for me.

It works for me if the value is coming from a variable too. But when it's coming from that PRSN::c_seventh field, I get the discrepancy. I can't figure out why. And it's happening with four other fields as well. I mostly don't get how I can output a field's value, copy what that is, and then test for equality with it (PRSN::c_seventh = 5) and get 0.

39 minutes ago, Josh Ormond said:

As for this piece. If you select all and copy. Then throw that in the data viewer, are there any other characters? Seems like there is either a non-printing character or a piece of text in that field.

 This is all coming from the data viewer's calc and results fields using copy and paste.

39 minutes ago, Josh Ormond said:

If you pull up the index, is there anything weird in there? ( Cmd+i or ctrl+i )

c_seventh is an unstored calculation, so there's no index. It can't be indexed because it's referencing related data.

It does use some custom functions I have, so I'm going to try unpacking those (none use recursion) and see if the problem persists.

[Josh Ormond]

Gotcha. I forgot it was unstored.

Just as a test, maybe on a copy, short-circuit that calculation and make it GetAsNumber ( 5 ), and see if it works. If it does not, something is wrong with the field. Create a new field, with the original calc, and see if that works. That will tell you if the field is showing signs of corruption.

It is also possible that the layout object is corrupted and not the field. I would not think this is the actual reason for the error, but a possibility.

[Chuck]

9 minutes ago, Josh Ormond said:

Just as a test, maybe on a copy, short-circuit that calculation and make it GetAsNumber ( 5 ), and see if it works. If it does not, something is wrong with the field.

Commenting out the field's calculation and setting it to `GetAsNumber ( 5 )` worked. The first calc I quoted returned `5¶1¶1¶1` instead of `5¶1¶0¶0`.

9 minutes ago, Josh Ormond said:

Create a new field, with the original calc, and see if that works. That will tell you if the field is showing signs of corruption.

I did create a new `c_seventh_2` field and copied and pasted the calc, and that failed to work just like the original field.

9 minutes ago, Josh Ormond said:

It is also possible that the layout object is corrupted and not the field. I would not think this is the actual reason for the error, but a possibility.

I've been doing all this on a developer field in table view, but going to a user layout shows the same problem.

[Chuck]

OK, I fixed it, and the process might be interesting. Here's the original calc.

Case (
  dev.IsSet ( c_score_cache ) ;
  math.Fractile ( c_score_cache ; List ( prsn_PRSN~cross::c_score_cache ) ; 7 ) ;

  dev.Nil
)

I started unwrapping the custom functions. `dev.IsSet` is just `not IsEmpty` and `dev.Nil is an empty string. Here's `math.Fractile`:

// math.Fractile ( _value ; _list ; _pieces )
// Purpose:      Return which fraction of a list of numbers the value appears in.
// Parameters:   _value:  The value to get the fractile of
//               _list:   The list of value the value is found within
//               _pieces: How many pieces to break the list into
// Requirements: list.ValuePosition
//               math.RoundUpTo
// Version:      1.0 - Charles Ross - 17-12-10
// Notes:        Fractile is my own term for percentail or septile where the number of pieces determined by a parameter.
//               Percentiles can be computing by passing 100 to `_pieces` for example, and septiles by passing 7.

Let (
  [
    _list  = SortValues ( _list ; 2 ) ;

    _pos = list.ValuePosition ( _value ; _list ; 1 ) ;
    _count = ValueCount ( _list ) ;

    _fraction = _pos / _count
  ] ;

  math.RoundUpTo ( _fraction ; 1 / _pieces ) * _pieces
)

I was mistaken earlier when I said there were no  recursive functions being called. `list.ValuePosition` is, so for the time being I left that in. Unwrapping the three custom functions in the original calculation:

Case (
  not IsEmpty ( c_score_cache ) ;
  Let (
    [
      _list  = SortValues ( List ( prsn_PRSN~cross::c_score_cache ) ; 2 ) ;

      _pos = list.ValuePosition ( c_score_cache ; _list ; 1 ) ;
      _count = ValueCount ( _list ) ;

      _fraction = _pos / _count
    ] ;

    math.RoundUpTo ( _fraction ; 1 / _pieces ) * _pieces
  ) ;

  ""
)

At this point, the behavior was the same. So I unwrapped `math.RoundUpTo`. That function is:

// math.RoundUpTo ( _number; _precision )
// Purpose:    Rounds the number up to the nearest multiple of the precision.
// Parameters: _number:    The number to round.
//             _precision: The multiple to round to.
// Version:    1.0 - Mikhail Edoshin - 06-01-01
// Notes:      http://web.archive.org/web/20100122090742/
//             http://edoshin.skeletonkey.com/2006/01/rounding_to_a_g.html
//             To round time use a precision that is the number of seconds you want to round to, for
//             example, a precision of 300 would round to the nearest 15 minutes.
//             To round currency to the nearest quarter, use a precision of 0.25.
// Example:    ( math.RoundUpTo ( 5 ; 2 ) = 2 )
//               and ( math.RoundUpTo ( Time ( 1 ; 23 ; 45 ) ; 15 * 60 ) = Time ( 1 ; 30 ; 0 ) )

Ceiling ( _number / _precision ) * _precision

Unwrapping that in the calculation brings us (with the addition of parentheses where necessary to preserve the original) to:

Case (
  not IsEmpty ( c_score_cache ) ;
  Let (
    [
      _list  = SortValues ( List ( prsn_PRSN~cross::c_score_cache ) ; 2 ) ;

      _pos = list.ValuePosition ( c_score_cache ; _list ; 1 ) ;
      _count = ValueCount ( _list ) ;

      _fraction = _pos / _count
    ] ;

    ( Ceiling ( _fraction / ( 1 / 7 ) ) * ( 1 / 7 ) * 7 )
  ) ;

  ""
)

Again, at this point, the behavior hasn't changed. But that straight substitution above isn't necessary. Outside the `Ceiling` call we're multiplying by 1/7 and then by 7. That's redundant.

Case (
  not IsEmpty ( c_score_cache ) ;
  Let (
    [
      _list  = SortValues ( List ( prsn_PRSN~cross::c_score_cache ) ; 2 ) ;

      _pos = list.ValuePosition ( c_score_cache ; _list ; 1 ) ;
      _count = ValueCount ( _list ) ;

      _fraction = _pos / _count
    ] ;

    Ceiling ( _fraction / ( 1 / 7 ) )
  ) ;

  ""
)

At this point, it works.

So my guess here is that at the end, even though everything said the field stored the value 5, it was actually something like 4.99999999... because of the division and multiplication by a number whose reciprocal wasn't easily represented in binary, or, in math ( 1 / 7 ) * 7 = 1, in FileMaker, not exactly. When I asked for the value at index 4.9999... it truncated it and gave me the value at index 4. If the client had asked for octiles instead, with 1/8 exactly representable in binary, it probably wouldn't have been a problem.

I went back to the original calculation's form, but edited the math.Fractile function to round to the nearest integer before returning the result.

Edited January 25, 2018 by Chuck