1 2 Previous Next 26 Replies Latest reply on Oct 11, 2016 8:37 AM by PeterDoern

    Calculation

    RobinNeeves

      Can you please help, I have created the following calculation

       

      Case(

       

      IsEmpty ( ContractorName ) ;

       

      TrafalgarFirePreventionLtd::CompanyAddress1 & "¶" &

      Case(not IsEmpty (TrafalgarFirePreventionLtd::CompanyAddress2) ;TrafalgarFirePreventionLtd::CompanyAddress2 & "¶") &

      Case(not IsEmpty (TrafalgarFirePreventionLtd::CompanyAddress3) ;TrafalgarFirePreventionLtd::CompanyAddress3 & "¶") &

      Case(not IsEmpty (TrafalgarFirePreventionLtd::CompanyAddressCounty) ;TrafalgarFirePreventionLtd::CompanyAddressCounty  &",") & " " &

      Case(not IsEmpty (TrafalgarFirePreventionLtd::CompanyAddressPostCode) ;TrafalgarFirePreventionLtd::CompanyAddressPostCode;

       

      ContractorName & "¶" &

      Case (not IsEmpty (ContractorAddressStreet1) ;ContractorAddressStreet1 & "¶") &

      Case (not IsEmpty (ContractorAddressStreet2) ;ContractorAddressStreet2 & "¶") &

      Case (not IsEmpty (ContractorAddressTown) ;ContractorAddressTown & "¶") &

      Case (not IsEmpty (ContractorAddressCity) ;ContractorAddressCity & "¶") &

      Case (not IsEmpty (ContractorAddressCounty) ;ContractorAddressCounty & " " & ContractorAddressPostCode

       

       

      )

       

      I Get this message;

       

      A number, text constant, field name or “(“ is expected here

       

      I can’t see what I am doing wrong

       

      Thank you for your help

        • 1. Re: Calculation
          PeterDoern

          Hi,

           

          At first glance I see you're missing a closing parenthesis betwee CompanyAddressPostCode and the semicolon.

           

          However, you can simplify your entire calculation like so:

           

          Case (

            IsEmpty ( ContractorName ) ;

            List (

            TrafalgarFirePreventionLtd::CompanyAddress1 ;

            TrafalgarFirePreventionLtd::CompanyAddress2 ;

            TrafalgarFirePreventionLtd::CompanyAddress3 ;

            Substitute ( List ( TrafalgarFirePreventionLtd::CompanyAddressCounty ; TrafalgarFirePreventionLtd::CompanyAddressPostCode ) ; "¶" ; " " )

            ) ;

            // Else

            List (

            ContractorAddressStreet1 ;

            ContractorAddressStreet2 ;

            ContractorAddressTown ;

            ContractorAddressCity ;

            Substitute ( List ( ContractorAddressCounty ; ContractorAddressPostCode ) ; "¶" ; " " )

            )

           

          )

           

          since the List ( ) function will naturally omit empty values from the result.

           

          Also, you posted your question in the FileMaker Developer Challenge 2016 discussion, which is really intended for a very specific topic: the developer's challenge which took place at DevCon 2016. You'll reach a much wider and more appropriate audience if you post your question to the main discussion area.

          • 2. Re: Calculation
            RobinNeeves

            Thank you for your help that worked.

             

            Kind Regards

            Robin

            Robin NeevesTrafalgar Fire Prevention Ltd57 Beechwood DriveCulverstoneMeophamDA13 0TX

            T: 01732 824868M: 07973 482363E: tfpl@hotmail.co.ukE: r.neeves@hotmail.co.uk

            • 3. Re: Calculation
              PeterDoern

              I'm glad I could help.

              • 4. Re: Calculation
                RobinNeeves

                Helo Peter

                 

                Sorry to be a nuisance but I have another one you may be able to help me with, I do appreciate all you help.

                I am trying to create a calculation using the following fields, the idea is that it will list the Name and address using these field crating a margin as It will be used in merge filed to keep the text in line I need to add spacing, I also won’t to omit any empty field.

                Case ( not IsEmpty( AE SiteName ); ( "                     " ) &  AE SiteName & "¶" ) & ( "                       " ) &

                 

                List (A1 Street1;( "                                                                " ) & A2 Street2;( "                                                                " ) & Case ( IsEmpty (A3 Town); A3 Town;( "                                                                " ) & Case ( IsEmpty (A4 City); A4 City;( "                                                                " ) & A5 County &", " )

                ))

                Kind Regards

                Robin

                Robin NeevesTrafalgar Fire Prevention Ltd57 Beechwood DriveCulverstoneMeophamDA13 0TX

                T: 01732 824868M: 07973 482363E: tfpl@hotmail.co.ukE: r.neeves@hotmail.co.uk

                • 5. Re: Calculation
                  PeterDoern

                  I'll admit I'm a little flummoxed by the formula as posted. It looks as if posting by e-mail has munged it nicely. Also, unless I'm missing something it looks as if you're missing a ")".

                   

                  Off the top of my head, I'd replace your strings of spaces with one or more Char ( 9 ), which FileMaker converts into tabs. Then, in your merge field, you can set margins and tab stops to set and adjust the indentation.

                   

                  If you provide some sample output it will be easier for me to come up with a formula for you.

                  • 6. Re: Calculation
                    RobinNeeves

                    Hello Peter

                     

                     

                    Can you please help me again, I am creating a calculation for address field that is all ok but can't figure out how to get Post Code to UPPER CASE, I know how to do it in merge files but not when creating it in a calculation.

                     

                    I am using Filemaker Pro 13.0v5

                     

                    Thank you once again

                     

                    Kind Regards

                     

                    Robin

                     

                    Robin Neeves

                    Trafalgar Fire Prevention Ltd

                    57 Beechwood Drive

                    Culverstone

                    Meopham

                    DA13 0TX

                     

                    T: 01732 824868

                    M: 07973 482363

                    E: tfpl@hotmail.co.uk

                    E: r.neeves@hotmail.co.uk

                    • 7. Re: Calculation
                      PeterDoern

                      No problem, Robin: the function you're looking for is Upper ( text ), which returns text in uppercase. See also: Lower ( ) and Proper ( ) for similar functionality.

                      • 8. Re: Calculation
                        RobinNeeves

                        Hi Peter

                         

                         

                        Thats what I thought but this option dos not seem to be working.

                         

                         

                        This is what I have created.

                         

                         

                        Case(

                         

                         

                        not IsEmpty (ContractorName );

                         

                         

                        ("                                                       ") & ContractorName & "¶" &

                         

                         

                        ("                                                       ") & ContractorAddressStreet1 & "¶" &

                         

                         

                        ("                                                       ") & Case(not IsEmpty(ContractorAddressStreet2);ContractorAddressStreet2 & "¶") &

                         

                         

                        ("                                                       ") & Case(not IsEmpty(ContractorAddressTown);ContractorAddressTown & "¶") &

                         

                         

                        ("                                                       ") & Case(not IsEmpty(ContractorAddressCity); ContractorAddressCity & "¶") &

                         

                         

                        Case(not IsEmpty(ContractorAddressCounty); ContractorAddressCounty & ", " & Upper ( ContractorAddressPostCode ) 

                         

                        )

                         

                        Kind Regards

                         

                        Robin

                         

                        Robin Neeves

                        Trafalgar Fire Prevention Ltd

                        57 Beechwood Drive

                        Culverstone

                        Meopham

                        DA13 0TX

                         

                        T: 01732 824868

                        M: 07973 482363

                        E: tfpl@hotmail.co.uk

                        E: r.neeves@hotmail.co.uk

                        • 9. Re: Calculation
                          PeterDoern

                          Hi Robin,

                           

                          I tried your formula on my machine by putting quotation marks around all of your field names, and the result was:

                           

                                                                                 ContractorName

                                                                                 ContractorAddressStreet1

                                                                                 ContractorAddressStreet2

                                                                                 ContractorAddressTown

                                                                                 ContractorAddressCity

                          ContractorAddressCounty, CONTRACTORADDRESSPOSTCODE

                          So it appears as if the Upper ( ) function is working as expected. How is it not working exactly on your machine? Is the postal code appearing but not converting to upper case?

                           

                          Now that I think about it, is your merge field's style set to something like Title Case (for example), or do you have conditional formatting applied to the merge field that would override the uppercase?

                           

                          Screen Shot 2016-09-30 at 7.54.54 AM.png

                          • 10. Re: Calculation
                            RobinNeeves

                            Yes, the post code is their but only the first letter is upper case.

                             

                             

                            Kind Regards

                             

                            Robin

                             

                            Robin Neeves

                            Trafalgar Fire Prevention Ltd

                            57 Beechwood Drive

                            Culverstone

                            Meopham

                            DA13 0TX

                             

                            T: 01732 824868

                            M: 07973 482363

                            E: tfpl@hotmail.co.uk

                            E: r.neeves@hotmail.co.uk

                            • 11. Re: Calculation
                              PeterDoern

                              Check the formatting of the merge field itself. It sounds like "Title Case" style has been applied either to the merge field entirely or via Conditional Formatting.

                              • 12. Re: Calculation
                                RobinNeeves

                                Hi again Peter

                                 

                                 

                                I checked the field and it was set to Plain Text, I changed it to UPPUER CASE and that resolved it, I still don't know why the Upper (text) find don't work.

                                 

                                 

                                I have another request please.

                                 

                                 

                                I have two columns, one with Quantity and other Type, I want to add these to a merge field and centred sample below.

                                 

                                 

                                Fields

                                 

                                                                                           Quantity      Type

                                 

                                             <<SiteProduct::HydroSpray Count>>       <<SiteProduct::HydroSpray_TextDisplay Type>>

                                 

                                                       <<SiteProduct::Water Count>>      <SiteProduct::Water_TextDisplay Type>>

                                 

                                                       <<SiteProduct::Foam Count>>      <<SiteProduct::Foam_TextDisplay Type>>

                                 

                                 

                                Result

                                 

                                                              Quantity   Type

                                 

                                                                          1    Hydro Spray

                                 

                                                                          1    Water

                                 

                                                                          1    Foam

                                 

                                And So on

                                 

                                Kind Regards

                                 

                                Robin

                                 

                                Robin Neeves

                                Trafalgar Fire Prevention Ltd

                                57 Beechwood Drive

                                Culverstone

                                Meopham

                                DA13 0TX

                                 

                                T: 01732 824868

                                M: 07973 482363

                                E: tfpl@hotmail.co.uk

                                E: r.neeves@hotmail.co.uk

                                • 13. Re: Calculation
                                  PeterDoern

                                  Well, that uppercase issue is a puzzler. If you feel comfortable posting a copy of the file I'd be happy to take a look-see.

                                   

                                  As for your columns issue, what you can do is to use Tab Positions in the Inspector to force your data into columns.

                                   

                                  Make two tab stops in the merge field: a right-aligned tab followed by a left-aligned tab. The first value in each row would be preceded by a tab to force it to the right-aligned tab stop. Then tab again to force the label to the left-aligned tab.

                                   

                                  I hope this makes sense.

                                  • 14. Re: Calculation
                                    RobinNeeves

                                    Hi Peter

                                     

                                     

                                    Please find attached Certificate that i am working on at this time I'm working on the types and quantity, You can see how they look this was created by using two merge fields side by side but I am trying to get all in one merge field so it will all centre up and down and theres fields that will be used depending on the type of equipment.

                                     

                                     

                                    This is what it looks like when creating a calculation field, the quantities are add wrong and there are out of line.

                                     

                                     

                                    24   Hydro Spray

                                     

                                    6   Water

                                     

                                    33   Foam

                                     

                                    73   CO2

                                     

                                    6   Dry Powder

                                     

                                     

                                     

                                    4   Wet Chemical

                                     

                                    26   Blanket

                                     

                                     

                                     

                                     

                                     

                                     

                                     

                                    6   Fire Alarm

                                     

                                    4   Emergency Lighting

                                     

                                    Kind Regards

                                     

                                    Robin

                                     

                                    Robin Neeves

                                    Trafalgar Fire Prevention Ltd

                                    57 Beechwood Drive

                                    Culverstone

                                    Meopham

                                    DA13 0TX

                                     

                                    T: 01732 824868

                                    M: 07973 482363

                                    E: tfpl@hotmail.co.uk

                                    E: r.neeves@hotmail.co.uk

                                    1 2 Previous Next