8 Replies Latest reply on May 27, 2016 9:43 AM by erolst

    The Let function

    TKnTexas

      I know that the LET function has been around for awhile, but I have yet to master it.  So far, that has not been a major hindrance.  Now, the LET function is the only real solution.  I am parsing a report to extract data that is not completely exportable from our accounting system.  I print the report to a TEXT file and import to my table in FileMaker Pro 14.

       

      Below is a sample of two lines (records) of the report.  I am working on the field for the Customer ID.  I cannot use the text function of middlewords since the number of words is variable.  So I want to calculate the length between the first and second colon.  Then I can use the middle function.

       

      Customer ID: OMNI HOTELS Salesperson: 437 Balance Type: Open Item Last Invoice: 6/28/2013

      Name: OMNI HOTELS MANAGEMENT CORPORATION Territory: DALLAS Telephone: (469) 442-2241  Ext. 0000 Last Payment: 4/14/2015

       

      My calculation is:

      let (

        vFirstPos = Position(import, ":", 1,1)+2;

        vSecondPos = Position(import, ":", 2,1);

        vLen =vSecondPos - vFirstPos';

       

           Middle(import, vFirstPos, vLen)

      )

       

      The error I get is that "The specified field cannot be found."  It highlights vSecondPos

        • 1. Re: The Let function
          planteg

          Hi,

           

          you have a typo:

           

          vLen =vSecondPos - vFirstPos';

           

          There is a single quote that should not be there .

           

          I know, sometimes it's hard to catch, after many hours of work.

          • 2. Re: The Let function
            wkeja

            Try this (statements between [ ] and typo corrected):

            let ([

             

              vFirstPos = Position(import, ":", 1,1)+2;

             

              vSecondPos = Position(import, ":", 2,1);

             

              vLen =vSecondPos - vFirstPos

            ];

             

                 Middle(import, vFirstPos, vLen)

             

            )

            • 3. Re: The Let function
              erolst

              If you declare multiple variables, you need to wrap the declaration section into brackets:

               

              Let ( [

                vFirstPos = Position ( import ; ":" ; 1 ; 1 ) + 2 ;

                vSecondPos = Position ( import ; ":" ; 2 ; 1 ) ;

                vLen = vSecondPos - vFirstPos

              ] ;

              Middle ( import ; vFirstPos ; vLen )

              )

               

              Also note that there is no semicolon after the last variable declaration.

               

              TKnTexas wrote:

              Now, the LET function is the only real solution.

              Not really:

               

              Middle (

                import ;

                Position ( import ; ":" ; 1,1 ) + 2 ;

                Position ( import ; ":" ; 2 ; 1 ) - Position ( import ; ":" ; 1 ; 1 ) + 2 )

              )

               

              Just playing Devil's Advocate here – Let() is A Good Thing!

              • 4. Re: The Let function
                ch0c0halic

                When you have two or more variable definitions you have to enclose them in square brackets.

                 

                let ( [

                 

                  vFirstPos = Position(import, ":", 1,1)+2;

                 

                  vSecondPos = Position(import, ":", 2,1);

                 

                  vLen =vSecondPos - vFirstPos'

                ] ;

                 

                     Middle(import, vFirstPos, vLen)

                 

                )

                 

                 

                But, this isn't going to work as the "value" will include the field name of the next field. Also the Field names include spaces so removing them will involve additional parsing.

                 

                Actually you you'd be better off scripting parsing each line of data into variables to then be used in Set Fields() script steps.

                 

                I recommend you define each field that is used in a record in the Let() and Prepend a return to each, except the first, so each one is on a separate line. Now you can parse each fields value from its line.

                 

                To parse a single record you can use a Let() statement. But the field assignments and traversing the lines of the import are done within Loop() scripting.

                In this example: Pass in the first line of the import data, as 1 Record, in the variable "$import_data". The result puts each fields value into a variable using the fields name.

                 

                Let ( [

                 

                // Define fields in a record, uncomment next line to test Let() statement

                //import_data = "Customer ID: OMNI HOTELS Salesperson: 437 Balance Type: Open Item Last Invoice: 6/28/2013" ;

                 

                Customer_ID = "Customer ID:" ;

                Salesperson = "Salesperson:" ;

                Balance_Type = "Balance Type:" ;

                Item_Last_Invoice = "Item Last Invoice:" ;

                 

                //Segment each field into a single record.

                import_data  = Substitute ( $import_data ;

                [Salesperson ; "¶" & Salesperson] ;

                [Balance_Type ; "¶" & Balance_Type] ;

                [Item_Last_Invoice ; "¶" & Item_Last_Invoice]

                ) ;

                 

                // Now get each value

                $Customer_ID = Trim ( Substitute ( GetValue ( import_data ; 1 ) ; Customer_ID ; "" ) ) ;

                $Salesperson = Trim ( Substitute ( GetValue ( import_data ; 2 ) ; Salesperson ; "" ) ) ;

                $Balance_Type = Trim ( Substitute ( GetValue ( import_data ; 3 ) ; Balance_Type ; "" ) ) ;

                $Item_Last_Invoice = Trim ( Substitute ( GetValue ( import_data ; 4 ) ; Item_Last_Invoice ; "" ) )

                 

                ] ;

                 

                ""

                )

                 

                Note: when used in the data viewer the first variable definition needs to be uncommented and each of the result variables need to be changed to Global variables.

                • 5. Re: The Let function
                  planteg

                  erolst wrote:

                   

                  If you declare multiple variables, you need to wrap the declaration section into brackets:

                      

                  I missed that one !

                  • 6. Re: The Let function
                    TKnTexas

                    I did not understand the use of the brackets for multiple variables.  But it makes sense. 
                    I create a calc-field for each variable I am extracting.  Then I script moving the calc'd fields to static fields so that Data Lines have everything. 

                     

                     

                     

                    erolst, I have done that 2nd method for years to get around the use of the Let.  The Let does simplify the reading of the calculation.  Now do not be 'poking the bear' I am trying to stretch myself.  Use of FQL (SQL) is the next item on the list.

                     

                    I tried both calculations, with the LET and without.  Both left the field showing blank.  I have printed this to "play more" after lunch.  Thank you for help and feedback.

                    • 7. Re: The Let function
                      TKnTexas

                      I could not wait until after lunch.  I got all of the fields properly parsed.  Now I just have to script the moving of the data from the calc'd fields on the various header records to the Data line.  I think my Manager will be pleased with what this does when it is finished. 

                       

                      Thank you again for the assistance, and the lessons. 

                      • 8. Re: The Let function
                        erolst

                        ch0c0halic wrote:

                        Note: when used in the data viewer […] each of the result variables need to be changed to Global variables.

                        Or just use e.g.

                         

                        List (

                          $Customer_ID ;

                          $Salesperson  ;

                          $Balance_Type ;

                          $Item_Last_Invoice

                        )

                         

                        as result expression of Let() to check the results; then put back the empty expression for production version.