How do I extract the first occurrence of any item in a list from a field?

Field A contains lengthy text. Within that text is the description of a fabric. The predominate fabric always appears ahead of the lesser fabric i.e., “...75% polyester, 25% cotton...”

I want Field B to show the first occurrence of any of the values in the following list. “Polyester” in the above example.

Any help would be appreciated.

...full grain cowhide

full grain pigskin

full-grain cowhide

full-grain pigskin

suede cowhide

polypropylene

Work-Dry® mesh

PVC

CoolMax® polyester

wool

polyester

polyester fleece

Merino wool

cotton rigid denim

cotton denim

cotton canvas

cotton drill...

Something like this, I suppose:

Let ( [

pos = Position ( listOfValues ; searchString ; 1 ; 1 ) ;

start = Position ( ¶ & listOfValues ; ¶ ; pos ; -1 ) ;

end = Position ( listOfValues & ¶ ; ¶ ; pos ; 1 )

] ;

Middle ( listOfValues ; start ; end - start )

)

OK, I don't get it.

Where does the value for searchString come from?

Is this formula searching a text field for any value from the list?

Perhaps I don't understand your question. The value of searchString would be "“Polyester” in your example, and given the same list of values, it would find the "polyester" value in the list. Now that I read it, I see it could be interpreted the other way round, that is you want to find values from the list in the text.

I think he wants to take a list of values and find which one of those occurs first in a block of text.

Sounds like a job for a recursive CF.

"Take a list of values and find which one of those occurs first in a block of text"

This is what I am after.

Yes, it would be best to use a custom function here. Would you know how to adapt this one (actually it's two)? Or perhaps just the highlighting itself might suit your needs:

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

I will take a look and give it a try. I don't have much experience with CFs, but now is as good a time as any to start.

Thanks for steering me in the right direction.

On second thought, this is not so simple at all. Consider a description like:

“...75% polyester fleece, 25% polyester...”

If you search for "polyester" in this description, it's going to have the same position as "polyester fleece", because "polyester" is contained in "polyester fleece". So it's more or less a flip of the coin whether you get "polyester" or "polyester fleece" as the result, unless...

You either look for the longest value among the values that scored equal, or you extract the materials from the description and look for them in the list of materials - somewhat similar to my first suggestion.

I have implemented the former in the attached file, using two repeating fields instead of a custom function. Perhaps if we knew the actual purpose here, a simpler way to solve the real problem behind this could be found.

FirstValueR.fp7.zip

Hello,

can be I will seek to extract starting from the first list.

I try this calculation

Let ( [



Fabric = "75% Polyester, 45% Cotton" ; /* carefull to the double spaces and other - control of seizure */

ListOfValues = YourListOfValues ;



Values = Substitute ( Fabric ; ["% " ; ¶] ; ["%" ; ¶] ; ["," ; ¶] ) ; // cleaning datas

Value = GetValue ( FilterValues ( Values ; ListOfValues ) ; 1 ) ; // Find First Value - respect the order of lists



Pos = Position ( ¶ &  ListOfValues & ¶ ; ¶ & Value  & ¶ ; 1 ; 1 ) ; // Position Value in listOfValues

Color = Replace ( ListOfValues ; Pos ; Length ( Value ) ; TextColor ( Value ; RGB ( 250 ; 0 ; 0 ) ) ) ; // Color Value in your list....

NbrValuePos = ValueCount ( Left ( ListOfValues ; Pos ) ) // number in list



] ;

Value & " is " & NbrValuePos & " value in the list" & "¶ -  -  -  -  - ¶" & Color

)

but I added variables because I think I do not understood everything...

perhaps

Agnès

Edited May 22, 200817 yr by Guest
Olé !!! much color in the code!!

Field A contains lengthy text. Within that text is the description of a fabric.

I think we need more rules here. It's easy to break both suggested methods if the text and/or the fabric description do not adhere to SOME consistent pattern.

For example, my method will falsely identify any fabric that appears in the "lengthy text" BEFORE the description as the first occurrence, and Agnès' method will break if the description items are not separated by commas.

At minimum, we should be able to identify where in the text the description begins. Otherwise this is an attempt to arm Filemaker with artificial intelligence, and it's doomed to fail.

I will try the calculation. But, I think it may be faster to manually review 600 records than to work out the calcs.

Thanks for the help.