1 2 Previous Next 17 Replies Latest reply on Apr 11, 2016 8:28 AM by Eldberg

    How to append one text field to another?

    Eldberg

      Problem: How to get the contents of two text fields and put them together into another field.

      So e.g. field 1 contains "Super" and field 2 contains "duper". I want to read these fields into a new field containing "Superduper".

       

      Redundant reality background: I have a database describing a cemetery.

      It is based on persons, not graves. Every person buried has a post in the database.

       

      The cemetery is organized in areas which are designated by a letter.  Each grave in an area is then identified by a number.

      So a typical grave would be A 132. Meaning area A and grave 132.

      So John Smith is buried in A 132. So is his wife Mary and about five other people, over the years. Every one of these has a post in the database. All of these posts contain the area A in one field and the number 132 in another field.

       

      When I'm looking at a post, I can only see one person. I only want to see one person. But I need to also see if there are others buried in the same grave. I want to display the total number of burials that have been done in this grave, on each record for a person buried there. When I look at Mary Smith, there should be a field telling me that there are a total of 7 people in A 132.

       

      The counting is not a problem; I do that with a Self-Join. But since A and 132 are, and must be, in separate fields, I need to perform the count of occurrences on a field that contains both A and 132.

        • 1. Re: How to append one text field to another?
          Mike_Mitchell

          To answer your basic question, the "&" symbol performs a concatenation in FileMaker calculations. Hence,

           

          Area & " " & Grave

           

          would give you (in your example) "A 123".

           

          However, you'd probably be better served in this case by simply using both fields for the self-join. Just add the extra field (whichever one isn't already there) by editing the relationship. Then your related record set will be only those records that match both fields.

           

          HTH

           

          Mike

          • 2. Re: How to append one text field to another?
            Eldberg

            Thanks! Some things are very easy but not when one is ignorant of what e.g. the & sign does…

            Åke

            • 3. Re: How to append one text field to another?
              Eldberg

              Thanks Mike, this seemed to solve my problem. I followed your last advice and made a self-join based on both fields. However, the resulting calculation does not seem to be done.

               

              The number of like fields was calculated correctly in existing records using the calculated fields: "Gimme a one" and "Number of likes". Thus, there are e.g. two records for grave A 123, and the number of likes comes up as 2.

               

              But today I created a new record for A 123, and nothing changes. The two old records still yield 2 likes and the new one yields 1. I was expecting all three records to yield 3 likes. How do I get FileMaker to re-calculate?

               

              Gravely yours,

              Åke

              • 4. Re: How to append one text field to another?
                Mike_Mitchell

                I have no idea what this means:

                 

                The number of like fields was calculated correctly in existing records using the calculated fields: "Gimme a one" and "Number of likes". Thus, there are e.g. two records for grave A 123, and the number of likes comes up as 2.

                 

                You'd need to tell me how you're calculating this - how these fields are defined - for me to give you a complete answer. However, usually, if fields are not recalculating, it's because (a) you have no trigger in the calculations that causes them to update, (b) the calculation is stored and you're attempting to reference related fields. I suspect (b) in this case, but can't be sure until I have more information.

                • 5. Re: How to append one text field to another?
                  Eldberg

                  Sorry for being unclear.

                   

                  The file is a registry for a cemetery.

                  There is one record for every person buried.

                  Graves (locations) are identified by two fields, "Area" and "GraveNumber" which are present in every record.

                  Some graves contain more than one person, meaning each of those records will have the same Area and GraveNumber.

                  I wanted to make a field which would display the number of persons buried in the same grave. So if I'm looking at John Smith who is buried in A 123 together with his wife Mary, each one of them would have a separate record, and both those records would display the location A 123, and also "Number of people buried in this grave" being 2.

                   

                  The method I used was to create a self-join relationship based on Area and GraveNumber. It is named SelfJoin.

                  Then a calculated field named "Gimme a one", the value of which is 1.

                  Then a calculated field named "Number of likes" which is "Sum(SelfJoin::Gimme a one)"

                   

                  I can't claim to understand all this, but that is supposed to give me a count of all records with the same Area and GraveNumber as the one I'm looking at. And it seemed to work. But today when I entered a new person in A 123, FileMaker still shows 2 in "Number of likes" for the two earlier records and 1 in "Number of likes" for the new record. All three records should show 3, if a recount has been made. Right?

                   

                  Hope this is clearer.

                  • 6. Re: How to append one text field to another?
                    Mike_Mitchell

                    Well, you shouldn't need the "gimme a one" field. You can just do a Count of some field that's always populated (like the primary key). (It'll also process a bit faster.)

                     

                    Yes, you should be seeing the field update (assuming the records are committed). A calculated field based on a related table is always unstored, so it should recalculate when it's called. Are you sure the data are correctly entered in the join fields? If I put Count ( SelfJoin::pk ) in the Data Viewer, what do I get?

                    • 7. Re: How to append one text field to another?
                      Eldberg

                      I don't know what the Data Viewer is. Nor do I know what a primary key is.

                       

                      Originally, I was trying to create a field combining the contents of "Area" and "GraveNumber", and count occurrences in that. You told me that a Self-join relationship might be better.

                       

                      Area and Gravenumber are both text fields. "Number of likes is" calculated numeric. The contents of "Number of likes" is not stored.

                      • 8. Re: How to append one text field to another?
                        Mike_Mitchell

                        Can you post a copy of your file?

                        • 10. Re: How to append one text field to another?
                          Eldberg

                          It's in Swedish. All the field names I've given you are anglified to make them easier to read. If you still feel this could work, how do I go about posting the field without getting all the 1,800 records as well?

                          • 11. Re: How to append one text field to another?
                            Eldberg

                            Sorry I meant file, not field.

                            • 12. Re: How to append one text field to another?
                              Mike_Mitchell

                              Normally, you'd save a clone of the file. (File > Save a Copy; choose "clone").

                               

                              But take a look at the example I posted first and see if that solves your question.

                              • 13. Re: How to append one text field to another?
                                Eldberg

                                You posted an example? I don't see it. There is no Clone command here, the closest I can get is "Export an empty copy".

                                • 14. Re: How to append one text field to another?
                                  Mike_Mitchell

                                  You have to log into the forum on the web to see attachments.

                                   

                                  menu.png

                                   

                                  dialog.png

                                  1 2 Previous Next