10 Replies Latest reply on Jul 7, 2010 10:49 AM by TSGal

    Bug ? Calculated Field using substitute randomly changes character case.

    Steve Wright

      Summary

      Bug ? Calculated Field using substitute randomly changes character case.

      Description of the issue

      I have just come across a strange issue in FMP 11 I have a layout which is used to print client letters based on a template from a related table ( see below for more info).  Now Lets say I have 20 clients to print letters for, I am getting inconsistent text formatting with random client records... For example : client 1 will print as designed : Dear Mr Smith

      Titlecase UPPERCASE lowercase Titlecase 
      UPPERCASE lowercase Titlecase UPPERCASE lowercaseClients 2, 3, 4, 5 will also print correctly, with their own names of course. Client 6 however will print like :  Dear Mr Jones

      Titlecase Uppercase Lowercase Titlecase
      UPPERCASE lowercase Titlecase UPPERCASE lowercaseBasically, it seems to be changing 1 line of text to "proper" with capitalised first letters.. however this does not continue throughout the paragraph, it then reverts back to how it was entered.Notice the first lines should show UPPERCASE not Uppercase Then in no particular order or pattern I can determine, this continues, so record 7 may print ok, 8 would be wrong, 9, 10, 11, 12, 13, 14, 15, 16 would print ok, 17 would not etc..  Here is a shortened version of how my structure is set up. 1. In the clients table, I have a global field which specifies which letter template is to be usedi.e Client::Template  (Contents for this example are TEST1 - the only related record) 2. In the template table, I have a single record, with 2 fields     template::ID   (contents are TEST1)     template::content  The content field contains something along the lines the of the over-exaggerated text below : Dear {title} {surname}

      Titlecase UPPERCASE lowercase Titlecase
      UPPERCASE lowercase Titlecase UPPERCASE lowercase  4. Back in the clients table, I set up an unstored calculation field, with the following calculation  Substitute ( template::content ;  ["{title}" ; client::title ] ; ["{surname}" ; client::surname ] )  6. In all cases, the fields on the layout are set to use 'Plain' style  Can anybody else reproduce this behaviour ?   Im just starting a new file to see if I can reproduce it further myself, but its getting late, so will probably continue to investigate over the weekend.  It may well be something in my file / data causing it, just seems bizarre how its only 1 line of data randomly changing.  I will check with FM10 tomorrow also.       

        • 1. Re: Bug ? Calculated Field using substitute randomly changes character case.
          Steve Wright

          Ive managed to narrow it down to the name causing this, if I move  {name} further down the letter, it causes a different portion of text to act in the same way.

           

          If I add something like {acc_number} directly after, the text displays normally.

           

          The {name} tag was substituted with :

           

           

           

          Case (not IsEmpty(Title) ; Trim(Title) & " ") &
          Case (not IsEmpty(First Name) ; Trim(First Name) & " ") &
          Trim (Surname)

           

           

           

          Ive since changed it to use the following method, and the problem has now been resolved...

           

           

          Let ( 
          lst = List (Title ; First Name ; Surname ) ;
          Substitute ( lst ; "¶" ; " ")
          )

           

           

          Still, none of the source fields use any text formatting, so where its getting this from I have no idea.

          • 2. Re: Bug ? Calculated Field using substitute randomly changes character case.
            TSGal

            SWS:

             

            Thank you for your posts.

             

            In your post, you mentioned you were going to try this out with FileMaker Pro 10, but I don't see any results.

             

            Do you get the same results on Mac as you do Windows?  Is it always the sixth record or does it change?  Is this file being hosted?

             

            If you have a sample file, I would like to see it, because there may be some contributing factors on the layout that may contribute to this inconsistency.  I have sent you a private message (top of this page - right side - envelope icon just beneath the blue horizontal bar) with instructions where to send the file.

             

            TSGal

            FileMaker, Inc.

            • 3. Re: Bug ? Calculated Field using substitute randomly changes character case.
              Steve Wright

              Hi TSGal

               

              The same behaviour exists in FM10..  although it may not be a bug, perhaps something with the data.

               

              I have prepared a sample file which outlines the issue (also showing the workaround) Ill email this to you now.

              Like I say, its no longer an issue for me since the workaround does seem to solve it. :-P

               

              P.S Its random records, which suggests its the data... there is no sequence from what I can tell.

              • 4. Re: Bug ? Calculated Field using substitute randomly changes character case.
                TSGal

                SWS:

                 

                I received your file.  Thank you.

                 

                I can confirm this exists in both FileMaker Pro 10 and FileMaker Pro 11.

                 

                However, when I view the Calculation formula to test some things out, it displays <Function Missing> twice for one of the fields, and any change I make gives me an error message.  What custom function or plug-in is being used?  It looked like it was checking for "Not IsEmpty", so I just changed it.  (Still shows the same problem)

                 

                I agree there is something with the data since it exists for the same records when you create another calculation using the same formula.

                 

                I have forwarded the file to our Development and Software Quality Assurance (Testing) departments for review and confirmation.  I'll keep you posted.

                 

                TSGal

                FileMaker, Inc.

                • 5. Re: Bug ? Calculated Field using substitute randomly changes character case.
                  philmodjunk

                  Any chance that text in those records is formatted in a manner different from the other records? Substitute is case sensitive after all...

                  • 6. Re: Bug ? Calculated Field using substitute randomly changes character case.
                    Steve Wright

                    Sorry about the function missing.. 

                     

                    To ensure the behaviour remained, rather than creating a new file, I actually deleted all TO's, Tables, Scripts, layouts etc not relating to this from my solution so you could have the same file I am working with, but get to the issue easier.

                     

                    I'm just creating a fresh file using the same structure to test further.

                    The sample data in the file I sent was just imported (from an fp7 file) for this purpose, so if it is the data causing it, it should show up in the new one too after an import.   

                     

                    Still.. like I say, its no longer a real issue since the work around resolved it.. but its still good to get to the bottom of things.

                     

                    Ill get back to you.

                    • 7. Re: Bug ? Calculated Field using substitute randomly changes character case.
                      Steve Wright

                      It is definitely formatting in the data which is being carried over.

                       

                      I exported a single record (No.6 in the sample) to both FP7 and CSV

                      Importing the FP7 reproduces the issue

                      Importing the CSV does not.

                       

                      What I find odd though, is in the sample file, using

                      {Title} {Firstname} {Surname} instead of {name} works as expected

                       

                      So if this is all down to formatted text in the field, surely these would be carrying the formatting over also ?

                       

                       

                       

                      • 8. Re: Bug ? Calculated Field using substitute randomly changes character case.
                        philmodjunk

                        I made the following change and it "fixed" the issue:

                         

                        I edited the Customer merged name field so that the current expression was enclosed in the the function, TextFormatRemove, and then the letter formatted correctly.

                         

                        I Also spotted something odd in the substitute function and removed it, but it didn't fix the issue:

                         

                        ["{Reg}"; Upper(TextStyleAdd(Vehicles::Registration;Uppercase))]

                         

                        One or the other function (upper, textstyleadd) is all that should be necessary here, but I assume you were trying this out of desperation when the field wouldn't format as expected. :smileywink:

                         

                        So I concur that something is off with the style of the text stored in one or more of the fields that Customer Merged Name references.

                        • 9. Re: Bug ? Calculated Field using substitute randomly changes character case.
                          Steve Wright

                           


                          PhilModJunk wrote:

                           

                          ["{Reg}"; Upper(TextStyleAdd(Vehicles::Registration;Uppercase))]

                           


                           

                          Lol... Yes, that was me at the end of a long night :-) testing scenarios.

                           

                          Here is something interesting... I narrowed it down to the Display Title field...

                          With the sample file which both of you have, try the following :

                           

                          Change the problem line to either of the following, both of these cause the issue : 

                           

                             ["{Name}" ; Case (not IsEmpty(Clients::Display Title) ; Clients::Display Title & " ") ] ;

                             ["{Name}" ; Case (not IsEmpty(Clients::Display Title) ; Clients::Display Title ) & " " ] ;

                           

                          Now change it to the following, this does not cause the issue.

                           

                             ["{Name}" ; Case (not IsEmpty(Clients::Display Title) ; Clients::Display Title) ] ;

                           

                          So the question is,  why is the trailing space causing the inherited formatting from the field to continue, whereas with no trailing space, the formatting stops?

                           

                           

                           

                           

                           


                          • 10. Re: Bug ? Calculated Field using substitute randomly changes character case.
                            TSGal

                            SWS:

                            No additional information at this time, but I have forwarded all of your posts to Development and Testing for review.

                            TSGal
                            FileMaker, Inc.