I've just been playing around with the ExecuteSQL function and it is going to be really useful, I think.

I'm still trying to figure out a good way to assign the results of a Select statement on multiple fields, into separate variables. For instance, I need to set the variables $leaseNumber and $leaseRoyalty, which both will contain multiple values. Right now I'm running separate SELECT statements for each variable. But the WHERE criteria are identical, and I know I can select multiple fields in a single statement. If the statement was returning a single row, I could figure that out, but since it is returning multiple values for each field, I'm not sure how to parse those out to assign them to their respective variables.

Any help would be appreciated.

Thanks,

Tom

You can specify what you want as a delineator and then easily parse it out.

Hi John -

Thanks for your reply. As well as making me laugh (at myself), your answer made me realize that I should probably be more succinct in phrasing my questions. If I were going to rephrase it, I would say something like -

Given that you can specify delineators for separating fields and records, what would a good choice of delineators be (or would the defaults work)? And, could anyone give me an idea of a method for parsing out the multiple values for each field into separate variables? If I had some general tips or a bit of pseudo-code, I could probably figure it out.

Thanks,

Tom

Given that you can specify delineators for separating fields and records, what would a good choice of delineators be (or would the defaults work)?

Choose a delimiter character that is not likely to be part of the data set. If the data never has carriage returns in it then returns are good delimiters. If the data never has commas then commas are good.

Returns are convenient because FMP has many text functions that work natively with return delimited values. If the data has returns then it might be possible to replace the returns (pipe characters are common) at either end.

It might help to see a sample of the text. Otherwise, it can be counter productive guessing the patterns.

Why not post a sample file or copy and paste a snippet of a few lines.

Lee

could anyone give me an idea of a method for parsing out the multiple values for each field into separate variables?

Perhaps this might help:

http://www.briandunning.com/cf/877

I wonder which would be more efficient: getting all the columns at once, then parsing them out - or constructing an individual query for each column?

Thanks for all the replies. The custom function at Brian Dunning's site looks like it would work great. I'll have to play around with it.

I was thinking more in general terms about the problem, because I think I'll have many situations to apply the technique. But the fields I'm working with at the moment are id_lease, leaseNumber, and leaseRoyalty; examples of their contents are attached.

In this case, the default delimiters would work fine, I think.

Thanks again,

Tom

The custom function at Brian Dunning's site works like a charm. I love it when there's already a solution to my problem. Now if I can just figure out how to make my queries with Join & Where statements in them to work, I'll be in good shape.