10 Replies Latest reply on Nov 28, 2011 12:44 PM by paulspafford

    Calculation help with related table

    wwinter

      Hi newbie with a problem iv been trying to fix for a week with no luck please help!!!

       

      i have a form with a simple list with a customers first and second name. on the form i want to create a new field joining the first and last name into just a name field.

      i have tried calculating from the related table (first name+last name) but the result is always 1 (it counts instead of displaying the text from these fields)

      what am i doing wrong??? please someone help me.

      i have attached an image of the calculation problem.

       

      will

        • 1. Re: Calculation help with related table
          usbc

          Will,

          You are on the right track ... In the related table use a calculation field with a text result.

          first name & " "& last name   (there is a space between the quotes)

          Chuck

          • 2. Re: Calculation help with related table
            wwinter

            hi chuck,

             

            thanks for answering me however i have done this but bento still tries to count the fields. if you check the image i attached i have the answer format as text but for some reason it still counts. i am trying not to put this calculation in the related table but in the new form instead. i can get numbers to add up, etc but cannot get text to work from related forms. starting to think its a bug in bento 4?

             

            Will

            • 3. Re: Calculation help with related table
              CarstenLevin

              Try using a formula like this, here assuming that there are 3 fields and that Name_middle can be empty.

              Name_first, Name_middle, Name_Last

               

              Name_first &

              if(Name_middle ≈ ""

                ; " " & Name_middle & " "

                ; "" )

              & Name_last

               

              ≈ is "not equal to"

               

              The same principle could be used for Name_title

               

              if(Name_title ≈ ""

                ; " " & Name_title & " "

                ; "" ) &

              Name_first &

              if(Name_middle ≈ ""

                ; " " & Name_middle & " "

                ; "" )

              & Name_last

              • 4. Re: Calculation help with related table
                CarstenLevin

                SORRY SORRY SORRY

                 

                I did not get the point: Bento. I rest my case.

                But can you set the file type in bento. Since you get the result you may have choosen "number" or maybe "bolean". Try choosing type text. If that is available.

                 

                I just assumed that this is a FileMaker Developer area

                • 5. Re: Calculation help with related table
                  wwinter

                  Hi Carsten

                   

                  yeah i was looking at that like eh? ha

                   

                  i have it set as text in the origional form and have the answer to be set in text but bento still tries to count it. please have a look at my attached image and check i have it as text ( starting to doubt myself lol)

                   

                  thanks for the reply tho.

                   

                  is this a bug?

                  • 6. Re: Calculation help with related table
                    paulspafford

                    Hey Will,

                     

                    You need to remove the "Count" that you have before each field in the calculation.

                     

                    Paul

                     

                     

                     

                     

                     

                     

                     

                    --

                    Paul Spafford

                    FileMaker Database Superhero,

                    Spafford Data Solutions

                     

                     

                    Tel: 613 838 9956

                    www.paulspafford.com

                    www.ottawafilemakerdeveloper.com

                    www.fmlayoutmode.com

                    www.fmscripts.com

                     

                     

                    FileMaker User Group in Ottawa:

                    www.meetup.com/fudgeo

                    • 7. Re: Calculation help with related table
                      wwinter

                      Hi Paul,

                       

                      yeah i no the count is the problem but how do i remove it from the formula.

                      whenever i click insert on the calculation window it automatically comes up in the formula and is attached to the names. when i try to select it to delete it just gives me other terms to insert like average, max, min, etc. is there a way in bento to manually insert these formulas to i could remove these "count" options?

                      • 8. Re: Calculation help with related table
                        paulspafford

                        Hey Will,

                         

                        Just had to go into Bento and see how. The problem is that you're trying to do this calculation from a related table. When you do that, Bento assumes that you are wanting to summarize the related data, and it forces that Count (with other options) before each field; it can't be removed.

                         

                        So what you need to do is create your calc field in the table that actually has the first name and last name fields. Then it isn't an option to use those aggregate functions.

                         

                        Paul

                         

                         

                         

                        --

                        Paul Spafford

                        FileMaker Database Superhero,

                        Spafford Data Solutions

                         

                        Tel: 613 838 9956

                        www.paulspafford.com

                        www.ottawafilemakerdeveloper.com

                        www.fmlayoutmode.com

                        www.fmscripts.com

                         

                        FileMaker User Group in Ottawa:

                        www.meetup.com/fudgeo

                        • 9. Re: Calculation help with related table
                          wwinter

                          Thanks Paul,

                           

                          So there is no way to get data from a related table into a new field in a form?

                          • 10. Re: Calculation help with related table
                            paulspafford

                            I don't think there is the way that you're trying to do it. You'd probably have better luck getting an answer from the Bento forum though. Someone on there would know better:

                             

                            <http://forums.filemaker.com/groups/a13a2dc617/summary?lang=en_US>

                             

                            Paul

                             

                             

                             

                            --

                            Paul Spafford

                            FileMaker Database Superhero,

                            Spafford Data Solutions

                             

                            Tel: 613 838 9956

                            www.paulspafford.com

                            www.ottawafilemakerdeveloper.com

                            www.fmlayoutmode.com

                            www.fmscripts.com

                             

                            FileMaker User Group in Ottawa:

                            www.meetup.com/fudgeo