11 Replies Latest reply on Jun 8, 2016 6:33 AM by mjohnson24

    Not Enough Memory to complete operation calc

    mjohnson24

      I have filemaker solution that is connected to a MySQL database that is used by our phone system to pull a call report. it works ok but im now getting an error. The below calc is in a calc field and on the layout im getting a ? across the board. If i copy the same calc in the dataviewer i get a error about sorry not enough memory to complete operation.

       

      If i change the variables that have the ~ in front of them and hard code the data instead of pulling them from fields then i get the output i need. Whats the issue with this?

       

      Let ( [

       

      ~FindExt = $$ext ;

       

      ~src = cdr::src ;

      ~srcLength = Length ( ~src ) ;

       

      ~dst = cdr::dst ;

      ~c_dst = cdr::_c_dst ;

      ~dstLength = Length ( ~c_dst ) ;

       

      ~c_dstChannel = cdr::_c_dstChannel ;

      ~c_accountCode = cdr::_c_AccountCode ;

       

      ~c_EmployeeExt = cdr::_c_EmployeeExt ;

       

      ~direction = Case (

       

        ~src = ~dst ; "Incoming" ;

       

        ~srcLength = 3 and ~dstLength = 3 and ~c_dst = ~c_EmployeeExt ; "Incoming" ;

        ~srcLength = 3 and ~dstLength = 3 and ~dst = ~FindExt and ~src ≠ ~FindExt ; "Incoming" ;

        ~srcLength = 3 and ~dstLength = 3 and ~dst ≠ ~FindExt and ~src = ~FindExt ; "Outgoing" ;

        ~srcLength = 3 and ~dstLength = 3 and ~src = ~c_EmployeeExt ; "Outgoing" ;

       

        ~srcLength = 3 and ~c_dst ≠ ~c_dstChannel ; "Outgoing" ; 

        ~srcLength = 3 and IsEmpty ( ~c_dst ) ; "Outgoing" ;

        ~srcLength = 3 and ~c_dst = "i5" ; "Outgoing" ;

       

        ~dstLength = 3 and ~c_dst = ~c_dstChannel ; "Incoming"

       

       

        )

       

      ] ;

       

      ~direction

       

      )

        • 1. Re: Not Enough Memory to complete operation calc
          siplus

          strange, I don't see problems, other than not having a default result in the case().

           

          Try putting everything into a custom function, to which you provide

           

          $$ext ; cdr::src ; ;cdr::dst ; cdr::_c_dstChannel ; cdr::_c_AccountCode ; cdr::_c_EmployeeExt

           

          which get assigned to your local vars.

          • 2. Re: Not Enough Memory to complete operation calc
            mjohnson24

            ok ill try that. Heres the problem.. it was working earlier this morning and now its giving me this error. Ill try the custom function though

            • 3. Re: Not Enough Memory to complete operation calc
              mjohnson24

              put it into a custom function and still the same error. 'Sorry not enough memory..."

              • 4. Re: Not Enough Memory to complete operation calc
                mjohnson24

                I also tried adding a default result of "unknown" so it looks like below and still nothing.

                 

                ~dstLength = 3 and ~c_dst = ~c_dstChannel ; "Incoming" ;

                 

                "unknown"

                • 5. Re: Not Enough Memory to complete operation calc
                  siplus

                  looks like you've hit the upper limit on the mysterious number - or memory - that the pool of local vars can reach / use.

                   

                  If you quit Filemaker than reenter and you try invoking this custom function immediately, do you still get the error ?

                  • 6. Re: Not Enough Memory to complete operation calc
                    mjohnson24

                    yea i quit and restarted filemaker, i quit and restarted my whole computer and still the same error. The only thing i havent restarted is filemaker server where this filemaker file resides.

                     

                    But heres the thing, i wrote a custom function a few years ago that is using a TON of local vars and yes it does look crazy and nuts and probably not good but it only gets invoked when a script is ran. it runs fine with no errors at all.

                     

                    Let ( [

                    // SERVICES INFO

                    ~unitType = T01m_OHCServicesJoin__||ClientID_IDClient_ServiceType::UnitType ;

                     

                    ~serviceStatus = T01m_OHCServicesJoin__||ClientID_IDClient_ServiceType::Status ;

                    ~serviceList = List ( T01m_OHCServicesJoin__||ClientID_IDClient_ServiceType::ServiceType ) ;

                     

                    ~distList = List ( T01h_DistMethod__|ClientID_IDClient::DistributionMethod ) ;

                     

                    ~musicChoiceOnly = If ( ValueCount ( ~distList ) = 1 and ~distList = "Email" and PatternCount ( ~serviceList ; "On Hold" ) = 1 and PatternCount ( ~unitType ; "iLink - MC" ) = 1; 1 ; 0 ) ;

                     

                    ~unitSerial = T01m_OHCServicesJoin__||ClientID_IDClient_ServiceType::UnitSerialNumber ;

                    ~messageLength = T01m_OHCServicesJoin__||ClientID_IDClient_ServiceType::MessageLength ;

                    ~notAutomixable = If ( T01o1_UnitTypes__UnitType_UnitName::NotAutomixable = 1 ; 0 ; 1 ) ; // If ~notAutomixable = 0 then NOT automixable

                     

                    ~audioApproval = If ( T01_Customer Scripts::Audio Approval Needed = "Yes" ; 1 ; 0 ) ;

                     

                    // PARAGRAPH AND SPACING LENGTH CHECK

                    ~OHMLength = Minute ( ~messageLength );

                    ~OHMLengthSec = GetAsNumber (  ~OHMLength * 60 - 1 ) ;

                    ~totalParas = T01_Customer Scripts::_TOTAL_ScriptParagraphs ;

                    ~totalParasSec = Sum ( OLDScriptParagraphsJoin|OLDScriptParagraphs::_CALC_TimeInSeconds ) ;

                    ~spacingAvail = GetAsNumber ( ~OHMLengthSec ) - GetAsNumber ( ~totalParasSec ) ;

                    ~calcSpacing = GetAsNumber ( ~spacingAvail ) / GetAsNumber ( ~totalParas ) ;

                    ~finalSpacing = GetAsNumber ( Int ( ~calcSpacing ) ) ;

                    ~defaultSpacing = If ( ~totalParas ≤ 3 ; 15 ; 10 ) ;

                    ~spacingCheck = Case ( GetAsNumber ( ~finalSpacing ) > ~defaultSpacing ; ~defaultSpacing ; GetAsNumber ( ~finalSpacing ) < 2 ; 2 ; GetAsNumber ( ~finalSpacing ) ) ;

                    ~totalLengthParas = ~totalParasSec + ~totalParas * ~spacingCheck;

                     

                    ~p = GetAsNumber ( ~totalLengthParas ) ;  // paragraph length field in customer scripts as seconds with calculated spacing

                    ~l = GetAsNumber ( ~OHMLengthSec ) ;  // OHM Message Length in seconds

                    ~s = If ( GetAsNumber ( ~spacingCheck ) < 2 ; 0 ; 1 ) ;  // calculated spacing after algorithm runs

                     

                    // checking for unit type. This is Music Choice. Not automixable due to M.C. loads it themselves

                    ~u1 = Case (

                                 PatternCount ( ~unitType ; "iLink - MC" ) = 1 ; 1 ; /* This is Music Choice. Not automixable due to Music Choice loads it themselves */

                                 PatternCount ( ~unitType ; "iLink" ) = 1 ; 1 ;

                                 PatternCount ( ~unitType ; "Telink 700" ) = 1 ; 1 ;

                                 PatternCount ( ~unitType ; "Telink 750" ) = 1 ; 1 ;

                                 PatternCount ( ~unitType ; "Telink 1200" ) = 1 ; 1 ; 0 ) ;

                     

                    ~IsActive = If ( ~serviceStatus = "ACTIVE" and ~notAutomixable = 1 ; 1 ; 0 ) ; // If service is ACTIVE and unit type is automixable (Specific to NTL units only)

                     

                    ~m = If ( ~unitType = "iLink - MC" ; If ( ~musicChoiceOnly = 1 ; 1 ; 0 ) ;

                                not IsEmpty ( T01o2_NTLCustomer__|Policy#_AccountNo::AccountNo ) and T01o2a_NTLUnit__|CustomerID_CustomerID::SerialNo = ~unitSerial ) ; // If Music Choice then True other wise if NTL Record Exists and NTL serial and Filemaker serial match

                     

                    ~n = If ( ~unitType = "iLink - MC" ; If ( ~IsActive = 1 and ~u1 = 1 and ~m = 1 ; 1 ; 0 ) ;

                                If ( ~IsActive = 1 and ~u1 = 1 and not IsEmpty ( ~m ) ; If ( ~IsActive = 1 ; T01e3a_NTLUnit__|CustomerID_CustomerID::Memory * 60 ; 0 ) ; 1 ) ) ;  // NTL Studio Unit Limit

                     

                    ~eNTL = If ( ~unitType = "iLink - MC" ; 1 ;

                                     If ( ~IsActive = 1 and ~u1 = 1 ; If ( GetAsNumber ( ~n ) < ~p ; 0 ; 1 ) ; 1 ) ) ;  // NTL Unit limit less than total script length

                     

                    ~ePara = If ( ~p > ~l ; 0 ; 1 ) ;  // total script length greater than ohm length in seconds

                    ~e = ~eNTL & ¶ &  ~ePara ;

                    ~e1 = If (  not IsEmpty (  FilterValues ( ~e ; 0 ) ) ; 0 ; 1 ) ; // **** if any zeros exist in ~e return delimited list

                     

                    ~m1 = If ( IsEmpty ( List ( T01c_CustomerScriptsMusicBeds__|ActionID_IDCustomerScripts::PCPath ) ) ; 0 ; 1 ) ;

                    ~d = If ( ~IsActive = 1 and ~u1 = 1 ; If ( not IsEmpty ( T01_Customer Scripts::LoadDate ) ; 1 ; 0 ) ; 1 ) ;

                    ~t = If ( ~IsActive = 1 and ~u1 = 1 ; If ( not IsEmpty ( T01_Customer Scripts::LoadTime ) ; 1 ; 0 ) ; 1 ) ;

                    ~a = List ( OLDScriptParagraphsJoin|OLDScriptParagraphs::FileExist ) ;

                    ~pf = Case (

                                 not IsEmpty ( FilterValues ( ~a ; 0 ) ) ; 0 ;

                                 not IsEmpty ( FilterValues ( ~a ; "ERROR" ) ) ; 0 ;

                                 IsEmpty ( ~a ) ; 1 ;

                                 1 ) ;

                     

                    ~w = If ( not IsEmpty ( FilterValues ( List ( OLDScriptParagraphsJoin|OLDScriptParagraphs::FileExt ) ; ".wav" ) ) ; 0 ; 1 ) ;

                    ~w1 = List ( OLDScriptParagraphsJoin|OLDScriptParagraphs::FileExt ) ;

                     

                    ~result = If  ( // (-IF-) UNIT TYPE IS OUR NTL EXCLUDING MUSIC CHOICE ILINK

                     

                      ~u1 = 1 ; // (-THEN-) NTL UNITS

                     

                      If (

                          ~IsActive = 1 and // Related service is active and Unit type is Loadable

                             ~audioApproval = 0 and // Audio Approval isn't needed

                          ~s = 1 and // Spacing is good

                          ~m = 1 and // Record exist in NTL Studio

                          ~e1 = 1 and // NTL unit memory and Paragraphs are good

                      ~m1 = 1 and // Music Bed and PC Path for that music bed exists & MB ID number is available.

                          ~d = 1 and  // Load Date not empty on customer scripts record

                          ~t = 1 and // Load Time not empty on customer scripts record

                          ~pf = 1 and // Paragraph files exist for all paragraphs on customer script

                      ~w = 1 // No wav files exist

                     

                      ; 1 ; 0 )

                     

                      ; // (-ELSE-) NON NTL UNITS

                     

                      If (

                          ~IsActive = 1 and // Related service is active and Unit type is Loadable

                             ~audioApproval = 0 and // Audio Approval isn't needed

                      ~m1 = 1 and // Music Bed and PC Path for that music bed exists & MB ID number is available.

                          ~pf = 1 and // Paragraph files exist for all paragraphs on customer script

                      ~w = 1 // No wav files exist

                     

                      ; 1 ; 0 )

                     

                      )

                     

                    ] ; // END LET BRACKET

                     

                      # ( "result" ; ~result ) &

                      # ( "notAutomixable" ; ~notAutomixable ; "" ) &

                      # ( "s" ; ~s ; "") &

                                            a.param ( "AudioApproval" ; ~audioApproval ; "") &

                      # ( "p" ; ~p ; "") &

                      # ( "m" ; ~m ; "") &

                      # ( "n" ; ~n ; "") &

                      # ( "musicChoiceOnly" ; ~musicChoiceOnly ; "") &

                                            a.param ( "UnitType" ; ~unitType ; "") &

                      # ( "u1" ; ~u1 ; "") &

                      # ( "IsActive" ; ~IsActive ; "") &

                                            a.param ( "distList" ; ~distList ; "" ) &

                                            a.param ( "serviceList" ; ~serviceList ; "" ) &

                      # ( "m1" ; ~m1 ; "") &

                      # ( "d" ; ~d ; "") &

                                            a.param ( "~AA" ; ~audioApproval ; "") &

                      # ( "t" ; ~t ; "") &

                      # ( "pf" ; ~pf ; "") &

                      # ( "w" ; ~w ; "") &

                      # ( "eNTL" ; ~eNTL ; "") &

                      # ( "ePara" ; ~ePara ; "") &

                                            a.param ( "spacing" ; ~spacingCheck ; "") &

                                            a.param ( "finalSpacing" ; ~finalSpacing ; "" ) &

                      # ( "messageLength" ; ~messageLength )

                     

                     

                    ) // END LET

                    • 7. Re: Not Enough Memory to complete operation calc
                      mjohnson24

                      Unless its something to do with Connecting to a External MySQL table with ODBC. We have Filemaker 14 server that uses Actual Technologies ODBC driver to connect to the MySQL tables that we use through out the system.

                      • 8. Re: Not Enough Memory to complete operation calc
                        siplus

                        that's for sure a helluva Let, even if we strip out comments.

                         

                        However, what remains is that your calc works when used with hardcoded data and does not when transferring the values to local vars, which points me into the direction of you reaching some limits of the locals.

                         

                        It never occurred to me, and I can assure you that our main solution is pretty hefty and local vars do rain all over. Maybe somebody from FM could chime in with limits that we're unaware of.

                        • 9. Re: Not Enough Memory to complete operation calc
                          mjohnson24

                          lol yep its a long one. one day ill re-write it smaller if i can but it works for now and its only triggered by a script so thats good for now.

                           

                          The one thing i just did is one by one play with each Let var and found out the part that is causing the issue is the let var ~c_EmployeeExt = cdr::_c_EmployeeExt. the_c_EmployeeExt is a field calc that references an ExecuteSQL function i wrote that gets the Employee's Extension when passed to it. if i hard code that then it all works but if i dont then it gives me the error. So something with that execute SQL statement makes it go to sorry out of memory error. That execute SQL statement is below.

                           

                          // ------------  BEGIN EXECUTESQL BLOCK ------------ 

                          Let ( [

                          ReturnSub = "\n" ; // We need to swap out carriage returns in your results with a different character, so specify the character here.  \n is the default.

                          SQLResult = ExecuteSQL (

                           

                          // ------------  BEGIN QUERY ------------

                           

                          "SELECT a.\"Phone\Extension\"

                          FROM \"OHMPersonel\" a

                          WHERE a.\"Employee Name\" = ?" ;

                           

                          // ------------  END QUERY ------ ------

                          // ------------  BEGIN FIELD AND ROW SEPARATORS ------------

                           

                          "" ; "" ;

                           

                          // ------------  END FIELD AND ROW SEPARATORS ------------

                          // ------------  BEGIN ARGUMENTS ------------ 

                           

                          // ------------  These arguments are pulled from the values you entered when running your query.  You can substitute your own values, fields or variables here. One argument per ? in your query, in query order, separated with semicolons. ------------

                           

                          Name

                           

                          // ------------  END ARGUMENTS ------------ 

                           

                          ) ] ;

                           

                          // ------------  BEGIN CARRIAGE RETURN SUBSTITUTIONS ------------ 

                           

                          Substitute ( SQLResult ; "," ; ¶ )

                           

                          // ------------  END CARRIAGE RETURN SUBSTITUTIONS ------------ 

                           

                          )

                           

                          // Compliments of SeedCode… Cheers!

                           

                          // ------------  END EXECUTESQL BLOCK ------------ 

                          • 10. Re: Not Enough Memory to complete operation calc
                            mjohnson24

                            So i think i know what it was or atlas what i think.

                             

                            _c_EmployeeExt utilized an ExecuteSQL code to get the extension from a table which i pass the name of the employee to it from the _c_EmployeeName calc.

                             

                            _c_EmployeeName utilized the _c_CallType calc field to se if its an incoming or outgoing call but the _c_CallType calc field required the _c_EmployeeExt calc field to operate.

                             

                            So i guess they were trying to work together and both needed each other in order to get the data but one wouldn’t get the data with out the other so i guess it created a loop of some sort.

                             

                            if that makes any sense.

                            • 11. Re: Not Enough Memory to complete operation calc
                              mjohnson24

                              So far what i got is working now. Thanks for he help siplus