12 Replies Latest reply on Mar 8, 2013 5:19 PM by tmilas

    Field Calculation error

    tmilas

      My Fellow FMers,

       

      I've been mid level guy. However this time I stumbled on something and it seems can't go forward.

      Here is my dilemma:


      I created DB with records of products/items.

      My company has the strict rules for serial numbers:

       

      1VAL12B123ADVAC

       

      1VAL is always there, 12 means a year of manufacture, B means a second month, 123 is an actual serial number. ADVAC is a type of product.

      12 - year can be obtained from "date created" field (auto-enter today's date), so is the month B from the date created (calculation).

      Serial number starts for new month at 100, still OK and sequential number is done by adding 1.

       

      There are several fields I created for getting this done. Now for similar solution I duplicated the formula and am getting unclear error message while the same calculation works fine in different DB.

       

      Here is the calculation for Data Base "A" attachment (130204.fp7) that works OK:

       

       

      If(Cat#="";"";Let ( [

       

      part1 = "1VAL" ;

      part2 = Period ;

       

      part3 = If ( theStart > 0 ; 100 + SN - theStart ; 100 ) ;

      /*part3 = TextColor ( part3 ; RGB ( 200 ; 0 ; 50 )) ;*/

      part4 = (Type1)] ;

       

       

      Identical application in Data Base "B" attachment (Padmount...) gives me the following error:

       

      If(Style#="";"";Let ( [

       

      part1 = "1VAL" ;

      part2 = Period ;

       

      part3 = If ( theStart > 0 ; 100 + SN - theStart ; 100 ) ;

      /*part3 = TextColor ( part3 ; RGB ( 200 ; 0 ; 50 )) ;*/

      part4 = (Type)] ;

       

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

       

      Pls HELP!!!, anyone?

       


        • 1. Re: Field Calculation error
          taylorsharpe

          I'm not sure what you're getting at.  I rewrote it below trying to make sense out of it and changed teh part numbers.  What is part5 and how does it fit in?  I added part6 instead of the leading Style If formula so that everything was in the Let statement.  I assumed if true you wanted part4, but wasn't sure. 

           

          Let ( [

           

          part1 = "1VAL" ;

          part2 = Period ;

          part3 =  If ( theStart > 0 ; 100 + SN - theStart ; 100 ) ;

          part4 = TextColor ( part3 ; RGB ( 200 ; 0 ; 50 )) ;

          part5 = (Type) ;

          part6 = If ( Not IsEmpty ( Style# ) ; part4 )

           

          ] ;

           

          part6

           

          )

          • 2. Re: Field Calculation error
            wimdecorte

            You're missing the closing bits for both the IF and the LET

            I wouldn't delcare the LET inside the IF.  Do the LET first and then evaluate the IF, that will make it a lot more readable.

             

            If(

                Style# = "";

                "";

                Let(

                    [

                        part1 = "1VAL";

                        part2 = Period;

                        part3 = If( theStart > 0; 100 + SN - theStart; 100 );

                        /*part3 = TextColor ( part3 ; RGB ( 200 ; 0 ; 50 )) ;*/

                        part4 = ( Type )

                    ];

                     )

            )

            • 3. Re: Field Calculation error
              wimdecorte

              You are also not returning something in the Let, there has to be something after the ];

              • 4. Re: Field Calculation error
                DaveRawcliffe

                To add, Wim & Taylor mentioned but not explicitly that :

                 

                Cat# = ""  should be: IsEmpty ( Cat# )

                and

                Style# = "" should be: IsEmpty ( Style# )

                 

                & As Wim stated a Result was not stated

                 

                Case (

                    IsEmpty ( Style# ) ; "" ;

                    Let(

                        [

                            part1 = "1VAL";

                            part2 = Period;

                            part3 = If( theStart > 0; 100 + SN - theStart; 100 );

                            /*part3 = TextColor ( part3 ; RGB ( 200 ; 0 ; 50 )) ;*/

                            part4 = ( Type ) ;

                            Result = part1 & part2 & TextColor ( part3 ; RGB ( 200 ; 0 ; 50 ) ) & Part4

                        ] ;

                            Result

                         ) //End Let

                ) //End Case

                 

                and per ch0c0halic

                 

                First lets go over field naming do's and don'ts.

                 

                1. Don't use separate numbers in a field name. FMP doesn't know that "Work Phone 2" is a complete name or two fields and a number.

                2. Don't use spaces in a field name. See number one above.

                3. Use only alpha-numerics and underscore. If you ever want to make this web published or use the ExecuteSQL() command fields names like "Id #" won't work.

                4. New Be aware of SQL reserved words, for example: "current_user" is a reserved word in SQL and is difficult to as a field name in a query.

                 


                • 5. Re: Field Calculation error
                  tmilas

                  David,

                   

                  Thanks a million.  It worked perfect.  One thing that bothers me--my original inept (obviously) calc formula worked fine in different solution (first DB).  I never used wrapping script in "Case" statement.  Very clever.  Obviously I have a lot to learn yet.

                   

                  Thanks again,

                   

                  Tom

                   

                  By the way other answers from other people returned the same error.  Error message wasn't very useful.  Do you know any good references to learn this stuff?

                  • 6. Re: Field Calculation error
                    taylorsharpe

                    Every developer has their own style, but I have to say, I find is a strange concept having a Let statement inside other functions. It works, but I normally have one let statement with all the calculations inside of it.  It is always interesting see the habits of other developers.  We may make something that works exactly the same, but our formulas and scripts can be vastly different, achieving the same ends - <grin>.  But I have to admit, the Let statement is probably my favorite FileMaker calculation function because it is so powerful in combining numerous calculations into one. 

                    • 7. Re: Field Calculation error
                      DaveRawcliffe

                      Taylor,

                       

                      I must agree with you that :

                      "We may make something that works exactly the same, but our formulas and scripts can be vastly different, achieving the same ends - <grin>."

                      Thank heavens.

                       

                      However, I would argue that there is nothing inherently wrong with wraping a Let ( ) or a List ( ) within another function.

                      Thus the Let ( ) wont fire uless it passes the Case

                       

                      The above could have been simplified further to:

                       

                      Case (

                          not IsEmpty ( Style# ) ;

                          Let(

                              [

                               and soforth.

                       

                      If the Case ( "Fails" it aborts ( less ticks ) else proceeds with the Let (

                       

                       

                      The previous:

                       

                      Case (

                          IsEmpty ( Style# ) ; "" ;

                          Let(

                              [

                       

                      Runs an extra tick [ "" ] before proceeding with the Let (

                       

                      Just a thought on optimization

                      • 8. Re: Field Calculation error
                        tmilas

                        Dear David,

                         

                        Sorry to bother you again.

                        Your solution that works so well fails when I create "duplicate record." Serial numer gets duplicated as well instead advancing to the next (red 3 digits).  My original one that I couldn't use in this data base however when I duplicated record did advanced of one number.  Things is that sometimes we make i.e. 30 identical products and duplicating would save a lot of manual entries that have to be done when making a new record.  Another words only new record advances serial number but duplicating does not.  Any ideas?

                        I never struggle so much with DBs but this one seems cursed.

                         

                        Also what references would you recommend for learning scripting?

                         

                        Best,

                         

                        Tom

                        • 9. Re: Field Calculation error
                          keywords

                          There is something odd there then. When you duplicate a record that contains an autoenter serial number, the serial number will always take precendence over the existing data, so your duplicate record will contain a new SN but duplicate data in all other data fields.

                          • 10. Re: Field Calculation error
                            taylorsharpe

                            Maybe the autoenter serial number option wasn't checked????  I know autoenter serial numbers increase when duplicating records, so if that option is checked, it will work. 

                            • 11. Re: Field Calculation error
                              BruceHerbach

                              Hi,

                               

                              If the serial number is based on the formula in the first message in this thread and the auto-enter has "Do not replace exisitng value(if any) checked it will get the value from the original record even though the actual serial number field has incremented.

                               

                              If the field is set to be a calculation then it should update. 

                               

                              If you can post an example version of the DB I'll take a look at it.

                               

                              Bruce

                              • 12. Re: Field Calculation error
                                tmilas

                                You are right. I unclicked that option at works perfect.

                                 

                                Thanks,

                                 

                                Tom