1 2 Previous Next 26 Replies Latest reply on Aug 8, 2015 8:18 AM by taylorsharpe

    Schedule Script killing FMSE on script that works in FMP

    taylorsharpe

      I am working with a script where I am taking calculated and summed data and storing it in a related table.  It works perfectly fine when running in FMP 14 or 13.  But whenever run on FMS 13.0v9 (Mac OS X 10.10.3) as a Schedule Script, it crashes the FMSE and I have to manually restart it (fmsadmin start fmse).  I haven't tried it on FMS 14 yet.  Here is a shortened version of the script and this version kills the FMSE too:

       

      Go to Layout [ “tblJOB Blank Table” (tblJOB) ]

      Enter Find Mode []

      Set Field [ tblJOB::JobSerial ; "==0003" ]

      Perform Find []

       

      Set Variable [ $FieldToSet ; Value: "tblJOB stored calcs::tbl_INVOICE_DATA|sEquipmentQuantCost" ]

      Set Variable [ $Field ; Value: "tbl_INVOICE_DATA::sEquipmentQuantCost" ]

      Set Variable [ $Value ; Value: "" ]   //  can put any value in here and it behaves the same way

      Set Field By Name [ $FieldToSet ; Evaluate ( $Field ) ]

       

      I am storing the results of a number of fields and only a few of them crash the FMSE, most do not.  But "tbl_INVOICE_DATA::sEquipmentQuantCost" does and it does every time.  Another field that crashes it is "tblJOB::cJobTotalExpensesQuant".  The FMSE crash happens at the evaluate function.  I was trying to figure out if there was some pattern like an illegal character in the field name.  But that didn't seem to be the case.  I've tried this script on different records and it consistently crashes FMSE on these fields every time no matter which record.  You can see that Evaluates from other fields in the same tables these two fields come from (tbl_INVOICE_DATA and tblJOB) work just fine.

       

      The field "tbl_INVOICE_DATA::sEquipmentQuantCost" is a summary field that just totals up a calc field.  And "tblJOB::cJobTotalExpensesQuant" is a calculation field with the following calc:

       

      tbl_INVOICE_DATA::sEquipmentQuantCost + tblPAYROLLPORTAL 3::sPayrollPortalEmployeeQuantCost + tblPAYROLLPORTAL 3::sPayrollPortalEmployeeQuantCostPD + tblPURCHASEORDERDATA2::sRebill_POAmount

       

      So I don't see anything strange about the calc.

       

      Here are some of the fields that work and don't crash the FMSE when run as a schedule script:

       

      tbl_INVOICE_Data::sInvoiceSum

      tblJOB::cJobActExp

      tblJOB::cJobTotalExpenses

      tblJOB::JobProjRev

      tbl_INVOICE_DATA::cTotalEquipmentCost

      tblJOB::JobProjectedCost

      tblJOB::JobCostVariance

      tblJOB::cJobActInc

      tblJOB::cJobGrossMargin

      tblJOB::cJobPayrollMin

      tblJOB::cJobPayrollMax

      tblJOB::cJobPayrollMaxMin

      tblJOB::cJobPayrollMaxMinSQL

      tblPAYROLLPORTAL 3::sPayrollPortalEmployeeCost

      tblPAYROLLPORTAL 3::sPayrollPortalEmpHrsNet

      tblJOB::cJobCodeOryx

      tblJOB::JobNameOryx

      lstJOBSTATUS::JobStatus_Description

      tblJOB::JobStatusPercent

      tblJOB::JobDateActualStart

      tblJOB::JobDateActualFinish

      tblJOB::JobProjExp

      tblPAYROLLPORTAL 3::sPayrollPortalEmployeeCount

      tblPAYROLLPORTAL 3::cPayrollPortalEmployeeQuantCostTotal

      tblPURCHASEORDERDATA2::sPurchaseOrderShell_POAmount

      tbl_INVOICE_DATA::cPayrollPortalEquipCost

      tbl_INVOICE_DATA::sInvoicePaid

      tblJOB::cJobGrossMarginPaid

      tblJOB::JobTargetCost

      tblJOB::JobTargetPercent

      tblJOB::sInternalCompanyTotalExpenses

      tblJOB::sInternalCompanyTotalIncOvr

      tblEmp_Master::cEmp_NameFull

      tblCLIENTDATA::ClientName

      tblEmp_Master 3::cEmp_NameFull

      tblJOB::JobProjectType

      tblJOB::cJobRegion

      lstINTERNALCOMPANIES 4::InternalCompanies_Name

      tblPURCHASEORDERDATA2::PO_ReqstDate

      tblPURCHASEORDERDATA2::PO_Num

      tblPURCHASEORDERDATA2::PO_RebillYN

      tblPURCHASEORDERDATA2::PO_InvRebillNum

      tblPURCHASEORDERDATA2::PO_Rental

      tblEmp_Master 12::cEmp_NameFull

      tblPURCHASEORDERDATA2::zzcPurchaseOrderShell_VendorName

      tblPURCHASEORDERDATA2::PO_Amount

      tblPAYROLLPORTAL 6::cPayrollDate

      tblPAYROLLEQUIPPORTAL2::Asset_Hours

      tblPAYROLLEQUIPPORTAL2::Asset_ID

      tblPAYROLLEQUIPPORTAL2::iSerialNumber

      tblAsset_Rates 6::Type_Description

      tblPAYROLLEQUIPPORTAL2::cEquipmentCostAct

      tblAsset_Rates 6::Type_ID_2

      tblPAYROLLPORTAL 6::iPayrollSerial

      tblPAYROLLPORTAL 6::cPay_TicktNum

       

      It is just frustrating that this always works just fine when running on any FMP client, just not on the server.  Is there some limitation of the Evaluate function on FMS?  This is a rather large file.

       

      PS:  Yes, I run a lot of schedule scripts and know it runs the startup script for the file and that script exits at the top when run on the server.  And the startup script doesn't keep the fields in this script that do work from working if I just remove the problematic fields.

        • 1. Re: Schedule Script killing FMSE on script that works in FMP
          Vyke

          Set Variable [ $FieldToSet ; Value: "tblJOB stored calcs::tbl_INVOICE_DATA|sEquipmentQuantCost" ]


          DATA|sE



           

          Is there a pipe in your table name?

          • 2. Re: Schedule Script killing FMSE on script that works in FMP
            Vyke

            Sorry, meant field name

            • 3. Re: Schedule Script killing FMSE on script that works in FMP
              steve_ssh

              Hi Taylor,

               

              Have you tried either of the following two ideas to troubleshoot this?:

               

               

               

                1) Try the same operation without the abstraction of using Evaluate()

               

              I suggest this just so that you can really hone in on whether the problem is tied to the use of Evaluate() and not something else.

               

              In other words, just as a control case, run a script like your sample below, but where the final step is:

               

                Set Field [ tblJOB stored calcs::tbl_INVOICE_DATA|sEquipmentQuantCost ; tbl_INVOICE_DATA::sEquipmentQuantCost ]


              My guess is that the above will run fine, but it would be good not to assume that this is so.

               

               

               

               

                2) Maintain the abstraction, but do so by leveraging the GetField() function instead of Evaluate()

               

              i.e. change the last line to:

               

                 Set Field By Name [ $FieldToSet ; GetField ( $Field ) ]


              If the problem is indeed tied to the use of Evaluate(), perhaps using the GetField()  function has potential to be a workaround?




              HTH, good luck!, and kind regards,


              -steve




               

               

              Taylor Sharpe wrote:

               

              Go to Layout [ “tblJOB Blank Table” (tblJOB) ]

              Enter Find Mode []

              Set Field [ tblJOB::JobSerial ; "==0003" ]

              Perform Find []

               

              Set Variable [ $FieldToSet ; Value: "tblJOB stored calcs::tbl_INVOICE_DATA|sEquipmentQuantCost" ]

              Set Variable [ $Field ; Value: "tbl_INVOICE_DATA::sEquipmentQuantCost" ]

              Set Variable [ $Value ; Value: "" ]  //  can put any value in here and it behaves the same way

              Set Field By Name [ $FieldToSet ; Evaluate ( $Field ) ]

               

              • 4. Re: Schedule Script killing FMSE on script that works in FMP
                taylorsharpe

                Yes, Vyke, the Pipe is a strange character.  However, you will notice that most of the fields work and all of them have a pipe in it.  So I kind of ruled it out.  But I may give that a try in that I have no other rationale as to why it works in FMP and not FMS.  Thanks. 

                • 5. Re: Schedule Script killing FMSE on script that works in FMP
                  taylorsharpe

                  Thanks, Steve.  You are correct, they work on a Schedule Script without the Evaluate function if I hard code the fields.  It is only when running a schedule script and using the Evaluate function that it fails.  That is why I was wondering if there was some type of limit to what an Evaluate can do.  But I would have no idea how to measure such a limit either.  But you're thinking along the lines that I am.  And it works just fine on FMP 13 of FMP 14 with the Evaluate. 

                  • 6. Re: Schedule Script killing FMSE on script that works in FMP
                    taylorsharpe

                    Ooops mispoken, Vyke.  The pipe is in the Field name. 

                    • 7. Re: Schedule Script killing FMSE on script that works in FMP
                      electon

                      You could try using the EvaluationError ( expression ) function to see what error code you get from that Evaluate.

                      Return this with your PSoS script result in a test. If it crashes the engine it may be of no use.

                      Maybe putting it once again in extra quotes would help.

                       

                      Anyhow, I agree with steve, no need to use Evaluate when you have GetField which does precisely what it's designed for.

                      Evaluate will try to parse any expression into a valid filemaker scripting language. Too many things to go wrong.

                      • 8. Re: Schedule Script killing FMSE on script that works in FMP
                        taylorsharpe

                        Electon and Steve.... so noted about using the GetField instead of evaluate.  In the real script, this is over a 500 line script and I've boiled it down to a real basic described above that fails as is.  In the real script, the field names are not known and are variables.  This is because the client does FileMaker development of adding and removing stored fields and layout work, but they don't do the scripting.  My job was to make this script work with any new stored fields or not evaluating any deleted ones.  A hard coded script doesn't do that.  Also, there are more fields than I have shown up above and l loop through the array of fields that I get from ExecuteSQL's evaluation of FileMaker_Fields which determines the currently active fields.  You are correct that hard coding the fields works always including in a schedule script.  Then again, with Evaluate, this always works on FMP.  It is just a schedule script that it fails on. 

                         

                        The crux of the matter is that I need a schedule script to run each day and when a FileMaker function is documented by FileMaker to work a particular way and it doesn't under certain undocumented circumstances, it questions the stability and reliability of FM to produce consistent results. 

                        • 9. Re: Schedule Script killing FMSE on script that works in FMP
                          electon

                          The fact that it fails on server is worrying, you may have discovered a bug.

                          Better to report this to filemaker.

                          • 10. Re: Schedule Script killing FMSE on script that works in FMP
                            jormond

                            I ran into this once when I was beating on a test file. I don't remember now exactly what the cause was, but I do remember doing 2 things:

                             

                            1. Placing a pause before and after the Evaluate.

                             

                            2. Playing with the context. It may have just been how I had thrown the function into the jungle. But because the context is different, I was getting an unexpected result that opened a continuous loop where Evaluate could never complete. Sometimes the effect was immediate. Sometimes I could watch the usage rise.

                             

                            For example, I found some times where Evaluate not only evaluated to the field name, it pulled the contents of that field name. So where I expected "TableName::FieldName", which was the value in the field, it gave me the value that was in the TableName::FieldName field.

                             

                            3. Also, double check the data types. I ran into sometimes where FM crashed because it was trying to evaluate a data type that didn't match and sent FMSE off into space.

                             

                            4. Test to see if the script works using a global field to store the value instead of a variable.

                            • 11. Re: Schedule Script killing FMSE on script that works in FMP
                              steve_ssh

                              Hi Taylor,

                               

                              It sounds as though you might be discarding the option of using GetField() with the thought that it requires a hard reference to a field.

                               

                              Just in case that is so, I would like to call to your attention to the fact that the GetField function will afford you the same capability of abstracting the field name to a $variable and acting on that variable's string value.  It can work in the same "indirect" way of accessing the target field's contents as you able to do with Evaluate().

                               

                              GetField(  "MyTargetFieldName" )  will return a reference to the actual field which is named MyTargetFieldName.  (Note that the argument supplied to GetField is a string value.  It could just as easily be a $variable that holds the name of the target field.)

                               

                              The question that is crossing my mind (and I believe this is the same for electon ) is whether or not GetField will experience the same problem that Evaluate experiences.  I think it is worth a try to see.

                               

                              HTH, and if you already knew all this, sorry for the extra noise.

                               

                              Very best,

                               

                              -steve

                               

                              Addendum p.s.

                               

                              I agree with your thoughts "The crux of the matter is...".  That it does not work reliably is definitely an important issue.  Just trying to see if we can find you a reliable alternate methodology in the meantime.

                              • 12. Re: Schedule Script killing FMSE on script that works in FMP
                                taylorsharpe

                                Thanks, Josh for the comments.  I was very much thinking on those lines too. 

                                 

                                1)  Pause doesn't work on server scripts... or it didn't in the past.  I haven't tested it on 14. 

                                 

                                2)  The scrip runs fine in FMP.  I've tested in FMS to return data and verified it really was in the layout and context that I was in in FMP.  And verified the contents of the evaluate function were the same as what was working on FMP. 

                                 

                                3)  Yes... data types matter especially for SQL.  And I double checked the data types.  Again, it does work on FMP, just not FMS.

                                 

                                4)  Interesting suggestion... I'll try that later today to see if it makes any difference. 

                                • 13. Re: Schedule Script killing FMSE on script that works in FMP
                                  taylorsharpe

                                  Thanks, Steve for your comments.  I'm going to play with some things and the GetField more today and report back here. 

                                   

                                  I will note that the calculation that is being performed going to multiple other very large tables.  This is a really huge calculation bringing together a lot of data.  Basically it is summarizing an entire company project for a big corporation.  That is why I was wondering if there was a limit of the Evaluate function in memory. 

                                   

                                  I've sometimes wondered if FMS handles text differently than say FMP.  For example, a ¶ in the parameter field for a schedule script on FMS is handled differently than a script parameter in FMP.  To make it work on the Server, I have to Substitute ( ScriptParameter, "/¶" ; ¶ ).  Could it be that evaluates have some issue along those lines.  The calculation it is evaluating is large and includes ¶'s and returns in it.  Maybe I could try it avoiding their use.  Of course I hate seeing a formula all strung together as one line, but it is worth testing. 

                                  • 14. Re: Schedule Script killing FMSE on script that works in FMP
                                    jormond

                                    Yeah, I've run into a few scenarios where the scripts worked using a global field instead of a variable. I'm not sure of the reason.

                                     

                                    Evaluate () can obviously be a tricky beast sometimes.  I would also try this calc to pull the fieldName itself.

                                    GetFieldName ( Evaluate ( fieldName ) )

                                     

                                    For those fields that you hard coded in the sample script, to test, I would wrap them in GetFieldName () and see if it works ok then.

                                    1 2 Previous Next