Inserting/Updating a field values based on the updated or inserted values on other column.

[Anonymous]

I need to populate the specific values that I extracted from one of the column in a table

The value in these fields is to be populated, as the name suggests, when the record is created and updated or even deleted respectively.
So when loading a record from the source into the destination I need to check (based on the destination's primary key) if the record exists in the number columns or not. If it does not then I need to put that values on remarks column, if the values is still exists I need to update or insert it on the new_number column and when the time comes that the data has been deleted, I also need to put it on remarks column.

I'm extracting the values and get the number on address column using this expression:

row2.ADDRESS.contains("%REFER%") ? row2.ADDRESS.trim(): ((row2.ADDRESS.substring(row2.ADDRESS.indexOf("ABC") +4, Math.min(row2.ADDRESS.indexOf("ABC") + 11, row2.ADDRESS.length())).replace("-","").replaceAll("\\D","")))

I used a lookup and try to get the right result by doing a left join and link that expression on the key. 

But the problems is:

1. If you will noticed on the screenshot instead of only those 6 numbers which are the valid values for this column should be extracted, it is also extracted some other values which should not be there.

2. Since 012006 is the only number exists on first column, that is the only values should be there and the others should be on remarks column. (if exists put values, if not,put on remarks).

Hope there's someone there who can tell me what is the other things I need to do here and or help me to get and have only the values I need and not those values that are not needed on that 2nd column.

Thanks

[Anonymous · Accepted solution]

I got it already, please disregard this. I solved it by comparing the integer before UPDATE.