Hello all,

 

 

I'm trying to update an Access table of mine using generated GUIDs, lets say I have the following:

 

Table_one

 

PARENT_GUID,FIELD_GUID

 

ABC123-EFT1,TEST23

 

ABC123-EFT1,TEST24

 

ABC123-EFT2,TEST23

 

ABC123-EFT2,TEST25

 

 

Also:

 

Table_two

 

PARENT_GUID,MEMO_FILLED_WITH_GUIDS

 

ABC123-EFT1,#IF##AND##CONDITION#{TEST23}ISNULL#ENDCONDITION##THEN##DO#{TEST24}ISMANDATORY

 

ABC123-EFT2,#IF##AND##CONDITION#{TEST23}ISNULL#ENDCONDITION##THEN##DO#{TEST25}ISMANDATORY

 

 

Now, due to some reasons, as you can see the FIELD_GUID is non-unique. I want to make it unique, and replace the FIELD_GUIDs with freshly replaced ones. However, seeing Table_two.MEMO_FILLED_WITH_GUIDS is a memo field filled with guids, I'd thought of using the replace function, within a SQLExecutor, like this:

 

 

UPDATE Table_two SET MEMO_FILLED_WITH_GUIDS= replace(MEMO_FILLED_WITH_GUIDS, @Value(FIELD_GUID, @Value(NEW_GUID_GENERATED)) WHERE PARENT_GUID = @Value(PARENT_GUID)

 

 

Where NEW_GUID_GENERATED is a GUID generated using FME.

 

 

However, this results in the following:

 

ERROR |MS Access Reader: Query failed. (-2147217900) Undefined function 'replace' in expression.'

 

 

Is there a way I can still use replace? Or is there a FME transformer which I can use to replace a value in a string based on another column, and use the PARENT_GUID in a where kinda clause?

 

 

Thanks in advance!