I have a return separated list of values (from a drivers license) where each line has a 3-character designator such as:

 

DACFrank

DCSSample

DAGSample Street

DAK90210

 

What I need to be able to do is to identify the line I want using the designator and grabbing the values to the right of it. In other words, using the list above, I only want the street (DAG) returned. What's the best way to do this?

Have a look at the Text functions.

 

If it's always 3 characters, something like this will do it:

Let([
   first = GetValue( yourField ; 1 ) ;
   last = GetValue( yourField ; 2 ) ;
   street = GetValue( yourField ; 3 ) ;
   zip = GetValue( yourField ; 4 ) 
   ] ; 

Middle( street ; 4 ; 9999 )
)

Middle( street ; 4 ; 9999 )

 

Don't get caught by the “never waste a single CPU cycle” police … 

 

Right ( street ; Length ( street ) - 3 )

I would not argue that your way isn't more "correct." Still... my intuition is that it takes more cycles to calculate the length than to grab extra characters. That would be interesting to test.

I would not argue that your way isn't more "correct." Still... my intuition is that it takes more cycles to calculate the length than to grab extra characters. That would be interesting to test.

 

It sure would. In the meantime, watch some experts arguing about this very topic … 

I actually thought of that, but then wondered about whether or not the order can change which is why I was wanting to look for the 3-character designator rather than depending on the order as your expression suggests. I don't know if the order can change, but if it can, then your Let statement won't populate the correct values.

There's a few ways to do it. This won't be the most efficient or elegant, but as an untested (I haven't got time to start up "Classic" mode), basci solution off-the-top-of-my-head which is (hopefully) easy see what's going on ...

The first step would be to find the three-letter code you want (I've used a Global Field to hold that, since then the same Script / Calculation can be used to extract the text following any code):

CodeLocation = Position(MyField; g_SearchCode; 1; 1)

Using that you can extract all the text from directly after the Code onwards:

AfterCodeOnwards = Right(MyField; CodeLocation + 3; 99999)

Within that text you can find the position of the first carriage return / paragraph marker, but you need to include an extra carriage return / paragraph marker in case the text you want is at the end of the Field's data:

EndofLineLocation = Postition(AfterCodeOnwards & "{CR}"; "{CR}"; 1; 1)

where {CR} is really the carriage return / paragraph mark.

Lastly you can extract everything to the left of that carriage return / paragrahp marker ... which gives you the text you want:

WantedText = Left(AfterCodeOnwards; EndofLineLocation - 1)

You can of course put that altogether in one function, but it gets messy and can be difficult to make changes to. It's easier to split it into steps.

Those functions can be made into a custom function in newer versions of FileMaker, or used with Global Fields and the Set Field command in Script, or normal Calculation Fields ... whichever is best for your database.

BUT ...

You may have problems here. For example, if you are wanting to extract the "DAG" text, but the person's name is "Dagger", then

DACDagger

DCSSample

DAGSample Street

DAK90210

will cause the extraction to return the wrong data (in this case "ger") beacuse it has found incorrectly, but logically, found the first occurance of "Dag".

To get around that issue, the best option may be to add an extra carriage return / paragraph marker to the start of the data and search for "{CR}DAG" instead, e.g.

CodeLocation = Position("{CR}" & MyField; "{CR}" & g_SearchCode; 1; 1)

AfterCodeOnwards = Right("{CR}" & MyField; CodeLocation + 4; 99999)

It sure would. In the meantime, watch some experts arguing about this very topic … 

 

That was fun -- glad I read all the way through the last post -- my intuition was right (the extra calc is slightly slower)!

 

I ran the tests myself too, on 10k records:

18 sec., Middle( street ; 4 ; 99999 )

19 sec., Middle( street ; 4 ; 9999 )

17 sec., Middle( street ; 4 ; 999 )

19 sec., Middle( street ; 4 ; Length... )

 

Weird. Maybe I'll run some averages later. Would also have to test over LAN, WAN, etc. With a more realistic data set. This isn't one I'll lose sleep over.