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:
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