7 Replies Latest reply on Aug 18, 2014 5:38 AM by philmodjunk

    Concatenate returns odd result

    fblack

      Title

      Concatenate returns odd result

      Post

           Dear All

           I have a problem with a simple table and a concatenate function, that sometimes returns a number instead of the text I expect. I suspect this is to do with forbidden characters, but I need help.

           Each record is of a historical person: first name, last name, year of birth and year of death. All fields are text. The concatenate function (also set to text!) is:  

           First Name & " " & Last Name & " (" & Year of Birth ) & " - " & Year of Death & ")"

           and so, with the fields populated (by composers), I get something like this:

           Dieterich Buxtehude (1637 - 1707) (first screenshot)

            

           However, Buxtehude might have been born in 1639

            

           If I enter "1637/39" in the Year of Birth field, or "1637 or 39", then the concatenate field returns "1.6374e+09".  (second and third screenshots). I'm guessing this is a number, (although the fields are all set to text). I've tried Quote and GetText, within Concatenate, to force the function to return strings of text, but to no avail.

            

           When I click into the concatenate field, I get shown the text result I expect (fourth screenshot).

            

           I am running FMP 12.0v5 on OSX 10.9.4

            

           Many thanks

            

           FERGUS BLACK

            

            

      Untitled.png

        • 1. Re: Concatenate returns odd result
          philmodjunk

               This sounds like your calculation field specifies a Number Result Type in the Specify Calculation dialog.

          • 2. Re: Concatenate returns odd result
            WBSimon

                 Try changing your calculation type from "number" to "text".

            • 3. Re: Concatenate returns odd result
              SteveMartino

                   I tried this and it worked:

                   First Name & " " & Last Name & " (" & GetAsText(Year of Birth ) & " - " & Year of Death & ")"

              • 4. Re: Concatenate returns odd result
                fblack

                     Many thanks for these ideas. 

                GetAsText didn't make any difference, I'm sorry to say.

                     However, the suggestions about calculation type got me thinking. I had checked previously that the type was set to text, but I have several instances of the table in my database. Apparently you can alter these individually. I never knew that. Going back to the correct relationship, allowed me to see that the type was indeed set to number in the table that was referenced in the relationship.

                     I am at the limits of my FMP knowledge here, and must confess that I've never really made the transition from flat-file to relational with what I feel is a sufficient working knowledge. In other words, I kludge things, until they work, sort of. So, apologies if I'm not explaining very well.

                     Thanks for your help. Problem solved.

                     Fergus

                • 5. Re: Concatenate returns odd result
                  philmodjunk
                       

                            I am at the limits of my FMP knowledge here, and must confess that I've never really made the transition from flat-file to relational with what I feel is a sufficient working knowledge.

                       

                            but I have several instances of the table in my database.

                       "Instances" is a slippery term here that might refer to having several "boxes" on your relationship graph that all refer to the same table or it may mean that you save several separate copies of your table. It would appear you have several completely separate copies of the same table given the change to a specify calculation dialog that you've described. That's a potentially serious design issue with your database.

                  • 6. Re: Concatenate returns odd result
                    fblack

                         Thanks, PhilModJunk - that's good advice. I thought I had separate boxes, but you are right that it looks like I might have separate copies.  I'll go through the database, and look at that.

                          

                         Thanks

                    • 7. Re: Concatenate returns odd result
                      philmodjunk

                           For any given table occurrence (box in Manage | Database | Relationships), you can hover the mouse over the top left hand corner and see the name of its data source table pop up.

                           And when you click on the Tables tab, the data source tables defined in your file are listed on the left, and the table occurrences that refer to them are in a comma separated list to the right of them in the column labeled "Occurrences in Graph".