Calculation Within Script Required Please

[adyf]

If I have a field that contains values such as:

01 08 14 22 Q

Can I write a script step such as 'Insert Calculated result' that will remove the 'Q' from this field and leave the other numbers in the field? There can different numbers in different quantities separated by a space. If it helps the 'Q' will always be the last value in the field. I was thinking one of the following functions such as PatternCount, Replace or Substitute may help.

[Daniel Wood]

Hi adyf,

You are correct in that the Substitute function can be used. if you are confident that the values in the fields always follow the same formatting, and, and that Q will be the last value in the list, you can use the following substitute formula:

Substitute ( YourField ; " Q" ; "" )

This will replace all occurrences of a space followed by a Q, and remove it essentially. Note that the substitute function is case sensitive, so if you cannot guarantee the case of the Q, try:

Substitute ( YourField ; [ " q" ; "" ] ; [ " Q" ; "" ] )

The substitute function as mentioned is for your ideal case, it may fail if the Q is in any other place in the field.

You can then use the Replace Field contents script step to perform this replace on every record in your found set. Use it with the calculation option & use the substitute function in there.

[bcooney]

Having multiple "facts" per field is often a sign of poor design. Shouldn't each of these values be a separate record?

However, if you wish to strip the Q, and it's always the last value, then newField= left ( origField; length (origField)-1)

Use this approach if you wish to maintain the origField rather than Replace.