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



2nd field was:



3rd field was:



4th field was:



5th (result field) was





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

Kindest regards