AnsweredAssumed Answered

The Let function

Question asked by TKnTexas on May 26, 2016
Latest reply on May 27, 2016 by erolst

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

Outcomes