Hello. I need to find a way to extract a value from a valuelist based on a partial match only.

For example, the valuelist contains the following data:

r123-A2

s123-12x16

t123-50x70

If my search value on the list was "12x16",  I would want "s123-12x16" to be returned from the calculation.

There would not be any circumstance where more than one match was possible. There would also be the possibility of no matches.

Hopefully someone can help as I've tried everything I can think of to no avail. I'm able to do it if I know the exact match, but I can't find a way to get it to work when I only have a partial match.

Many thanks in advance...

 

 

The calculation could be simply:

Let ( 
i = ValueCount ( Left ( listOfValues ; Position ( listOfValues ; searchString ; 1 ; 1 ) ) )
;
GetValue ( listOfValues ; i )
)

But I would ask: is there a reason why you need to "search" a value list, instead of storing the data in records and performing a find?

Fantastic - that was exactly what I was looking for and works!

As to the reason why....

I'm an artist who sells their artwork on Etsy and several other sites. In my database the Artwork Table contains a record of all my artworks (16000+). Each artwork can have up to 30 different sizes available. When a certain size is ordered I create the digital file of the artwork and send it to my printers. I then add the filename to the relevant record in the Artwork Table so that I know what I have already sent and do not have to create or resend the same file again. (The resizing/renaming/moving of these digital files is all done automatically via the DB calling various applescripts linking with Photoshop  - however I digress!)

Back to the matter at hand - With the filenames recorded against each artwork I can therefore generate a valuelist of the filenames/sizes for each specific artwork that has been created.

I use this valuelist on the the Sales Table - this is where I record each sale and generate the order email which gets sent to my printers. On that email I have to include the filename for the order.

At the moment to I have to manually click on the filename field and select the filename from the valuelist that is generated for that artwork. What I wanted to do instead was to just click a button and the field would automatically populate with the correct filename (based on other information I had already entered).

Basically it's just a way to speed things up and help avoid manual input errors.