user1069

Odd Calculation Bug

Discussion created by user1069 on Jun 18, 2017
Latest reply on Jun 18, 2017 by philmodjunk

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

 

What I got instead was

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?

Outcomes