9 Replies Latest reply on Jun 18, 2017 7:38 PM by philmodjunk

# Odd Calculation Bug

I have a legacy calculation field from pre-FMP12 that I was just updating in FMP 15 and discovered some odd behavior.

The calculation concatenates names to create salutations, e.g.

aPREFIX & " " & aFIRST & " " & aMIDDLE & " " & aLAST & " " & aSUFFIX

The problem with it is that if one of the data elements is missing (like aMIDDLE for example) you get double spaces. To avoid this I decided to change the syntax to:

Substitute ( List ( aPREFIX; aFIRST; aMIDDLE; aLAST; aSUFFIX ); [ "¶"; " " ] )

and that's when a funny thing happened. The full calculation that combines two individuals' names depending on if the last name is the same:

Case (

aLAST = bLAST; //same last name

Substitute ( List ( aPREFIX; aFIRST; aMIDDLE; "&"; bFIRST; bMIDDLE; aLAST ); [ "¶"; " " ] );

//different last names

Substitute ( List ( aPREFIX; aFIRST; aMIDDLE; aLAST; "&"; bPREFIX; bFIRST; bMIDDLE; bLAST ); [ "¶"; " " ] )

)

What I expected to get was

Mr. John D. & Catherine T. MacArthur

Mr. John D. & John D. MacArthur

I thought I must have done something wrong, but could find no error in my calculation. But when I reverted it back to using concatenation instead of substitution it worked as expected. This got me looking at the definition of the name elements themselves. Because the records are related, I'm setting them within the calculation like so:

Let ( [

aPREFIX = GetNthRecord ( PERSON::prefix; 1 );

aFIRST = GetNthRecord ( PERSON::firstName; 1 );

aLAST = GetNthRecord ( PERSON::lastName; 1 );

bPREFIX = GetNthRecord ( PERSON::prefix; 2 );

bFIRST = GetNthRecord ( PERSON::firstName; 2 );

bLAST = GetNthRecord ( PERSON::lastName; 2 )

];

Here's where it gets interesting (and where I think the bug lies). If I change this section to concatenate an empty string everything works as expected:

Let ( [

aPREFIX = "" & GetNthRecord ( PERSON::prefix; 1 );

aFIRST = "" & GetNthRecord ( PERSON::firstName; 1 );

aLAST = "" & GetNthRecord ( PERSON::lastName; 1 );

bPREFIX = "" & GetNthRecord ( PERSON::prefix; 2 );

bFIRST = "" & GetNthRecord ( PERSON::firstName; 2 );

bLAST = "" & GetNthRecord ( PERSON::lastName; 2 )

];

Each individual value is correct, and when I add the individual values to the output they display correctly, it's just the string created using Substitute ( List ( ) ) that is incorrect unless I introduce the concatenation with the empty string. Anyone else run into this?

• ###### 1. Re: Odd Calculation Bug

List (and this trick with substitute) has always worked as designed form me.

I'd check the data as well as the calculation.

• ###### 2. Re: Odd Calculation Bug

The data is fine. I'm reticent to call things a bug so I spent 2 hours poking at it to make sure it wasn't just me. For example, when I place it in the data viewer and change the result to include the individual elements as well I get:

Salutation: Mr. Ernest Y. Koe & Mr. Ernest Y. Koe

aPrefix: Ms.

aFirst: Charmaine

aMiddle:

aLast: Chiu

bPrefix: Mr.

bFirst: Ernest

bMiddle: Y.

bLast: Koe

I can see clearly that the variables holding the name elements are correct. I can also see the salutation change as I put the empty string in front of the various elements. For example, when I change it to:

Let ( [

aPREFIX = "" & GetNthRecord ( PERSON::prefix; 1 );

aFIRST = "" & GetNthRecord ( PERSON::firstName; 1 );

aMIDDLE = GetNthRecord ( PERSON::middleName; 1 );

aLAST = GetNthRecord ( PERSON::lastName; 1 );

bPREFIX = GetNthRecord ( PERSON::prefix; 2 );

bFIRST = GetNthRecord ( PERSON::firstName; 2 );

bMIDDLE = GetNthRecord ( PERSON::middleName; 2 );

bLAST = GetNthRecord ( PERSON::lastName; 2 )

];

the output changes to

Salutation: Ms. Charmaine Y. Koe & Mr. Ernest Y. Koe

aPrefix: Ms.

aFirst: Charmaine

aMiddle:

aLast: Chiu

bPrefix: Mr.

bFirst: Ernest

bMiddle: Y.

bLast: Koe

as I concatenate the empty string one by one to each variable the corresponding value in the salutation changes but the other elements remain the same.

• ###### 3. Re: Odd Calculation Bug

I copy/pasted your expressions, replaced the GetNthRecord calls with literal text, and evaluated this test calculation in a data viewer:

Let ( [

aPREFIX = "Mr." ;

aFIRST = "John" ;

aMIDDLE = "D." ;

aLAST = "MacArthur";

bPREFIX = "" ;

bFIRST = "Catherine";

bMIDDLE = "T." ;

bLAST = aLast

];

Case (

aLAST = bLAST; //same last name

Substitute ( List ( aPREFIX; aFIRST; aMIDDLE; "&"; bFIRST; bMIDDLE; aLAST ); [ "¶"; " " ] );

//different last names

Substitute ( List ( aPREFIX; aFIRST; aMIDDLE; aLAST; "&"; bPREFIX; bFIRST; bMIDDLE; bLAST ); [ "¶"; " " ] )

)

)

Here was the result:

Mr. John D. & Catherine T. MacArthur

• ###### 4. Re: Odd Calculation Bug

Yes, if I put literal text in there (or just prepend literal text to my field references) it works fine. That's the bug - it should work with a field reference alone but for me it does not.

I've been trying to figure out how this bug might have been coded and the only thing I can come up with is that there's something wrong with the value referencing. I suspect there's some optimization where my aFIRST variable isn't actually assigned a value but a field reference and when that reference is being used later on in the calculation formula it's not pointing to the correct record. Concatenating something to the field reference, on the other hand, forces assignment of the value and thus the referencing doesn't have a chance to go awry.

• ###### 5. Re: Odd Calculation Bug

What that indicates is that List and substitute have nothing to do with your incorrect results. It would appear to depend on what values are returned by GetNthRecord. The "" included in each calculation, BTW, doesn't appear to be something that would have any effect on the results returned.

You might also look at the context involved here.

Is this calculation part of a script step? An unstored calculation field? An auto-enter calculation?

• ###### 6. Re: Odd Calculation Bug

I'm not saying List() and Substitute() are the problems, I'm saying that something in the way the calculation references the field values is problematic and that those references are fixed when I prepend an empty string.

Functionally speaking

aFIRST = GetNthRecord ( PEROSN::firstName; 1 )

and

aFIRST = "" & GetNthRecord ( PERSON::firstName; 1 )

should product equivalent output. The only difference between the two is prepending the empty string.

When I display the value of aFIRST I get the correct value every time regardless of whether the empty string is prepended. This is what I would expect.

However, when I use the value of aFIRST in defining another variable using the first syntax above:

salutation = Substitute ( List ( aFIRST; aMIDDLE; aLAST; "&"; bFIRST; bMIDDLE; bLAST )

and display the value of salutation I get an incorrect value. There's two ways I've been able to get the correct value. First, I can prepend the empty string in assigning the variables such as:

aFIRST = "" & GetNthRecord ( PERSON::firstName; 1 )

or I can prepend the empty string inside of the salutation variable assignment like so:

salutation = Substitute ( List ( "" & aFIRST; "" & aMIDDLE;  "" & aLAST; "&"; "" & bFIRST; "" & bMIDDLE; "" & bLAST )

Either way, I change nothing else in the calculation, only whether the empty string is concatenated or not, and it changes the value. So it's not strictly what's being returned by GetNthRecord() either. As I mentioned before, when I set the output in the data viewer to be:

List (

"Salutation: " & salutation;

"aFIRST: " & aFIRST;

"aMIDDLE: " & aMDDLE;

"aLAST: " & aLAST;

"bFIRST: " & bFIRST;

"bMIDDLE: " & bMDDLE;

"bLAST: " & bLAST

)

I get the result:

Salutation: Barney Rubble & Barney Rubble

aFIRST: Fred

aMIDDLE: W.

aLAST: Flintstone

bFIRST: Barney

bMIDDLE:

bLAST: Rubble

I can't see how this can be anything other than a bug.

• ###### 7. Re: Odd Calculation Bug

Profound apologies, I've been focused on the wrong part of your sample calculation. I can reproduce the same results as you.

Furthermore, I can simplify the expressions and get the same results:

Let ( [

aFIRST = GetNthRecord ( Child::FirstName ; 1 );

aLAST = GetNthRecord ( Child::LastName ; 1 );

bFIRST = GetNthRecord ( Child::FirstName ; 2 );

bLAST = GetNthRecord ( Child::LastName ; 2 )

];

List ( aFIRST ;

aLAST ;

bFIRST ;

bLAST

)

)

and this expression:

List ( GetNthRecord ( Child::FirstName ; 1 );

GetNthRecord ( Child::LastName ; 1 );

GetNthRecord ( Child::FirstName ; 2 );

GetNthRecord ( Child::LastName ; 2 )

)

Both also demonstrate the bug.

This should be reported over in the Report an Issue part of the forum where it can be brought to the attention of FileMaker Techs. You can post a link to this discussion as a way to save re-posting all of these details.

While the code would definitely be more complex, a possible work around would be to use ExecuteSQL to build your list of values to be composted into this salutation text.

• ###### 8. Re: Odd Calculation Bug

Yes, this isn't unknown. Mixing List() in with GetNth() (or other Aggregate functions) can get wonky. For example, if you made 6 extra calc fields with GetNth() and referenced those in the concatenation field instead, you'd be fine.

1 of 1 people found this helpful
• ###### 9. Re: Odd Calculation Bug

I should have remembered this one as this one has been around a Looong time. It took David's mention of aggregate functions used with GetNthRecord to trigger that memory. I think that I had this one documented in the now defunct Known Bugs List DB that I used to maintain and share here in the forum.

Disappointed that current versions still have this issue, but congrats on a simple work around.