robhepworth

How to select certain text from a single line of text (FM Pro 13)

Discussion created by robhepworth on Oct 12, 2014
Latest reply on Oct 13, 2014 by robhepworth

Hello everybody,

 

I would really appreciate some help with a problem I am struggling with.

 

In this exmample I have thousands of records with a field I have named "Product description". The typical product description field looks like these:

 

HP 9000/9040/9050/9500 2000 Sheet Tray Casette Only (RG5-6212) or (C8531-69019)
HP 9000MFP Flatbed Intermediate PCB (RH5-3073)
HP 9040/9050/9500 Copy Connect Board (Q6006-60001)
HP 9040/9050/9500/9500MFP EIO Copy Processor (Q6005-67901)
HP 9040MFP / M9050MFP Cover, Flatbed, Right, ADF (RB2-8063)

 

I am trying to strip out the part number which is contained at the end of each field between the "(" and ")" . As you can see from the first record, sometimes there is two part numbers but it is the last one for each record that I am interested in.

 

The problem is, is that the part numbers and all different lengths and the content of the field in question is also different lengths.

 

I have managed to solve this in excel but I ideally need to do it in filemaker. In Excel, I created an additional 5 fields the fifth being the result

 

The 1st field, calculates the furthest along "(" in terms of characters

=FIND("^",SUBSTITUTE(A2,"(","^",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))

 

2nd field was:

=MID(A2,B2,50)

 

3rd field was:

=FIND(")",C2,1)

 

4th field was:

=LEFT(C2,D2)

 

5th (result field) was

=MID(E2,2,D2-2)

 

 

 

I tried to replicate it in Filemaker but I can't do it. Does anyone know the best way to do it?


Kindest regards


Rob

Outcomes