Using long text datatype in delivery

[NickC_]

Hi guys,

I'm trying to use a long text field in my delivery as targetData.

When i add the field with an enrichment as @terms, if i then display the target just after the enrichment, terms is not displayed but if i right click/configure list i can bring it in and it populates fine.

I add the field as targetData.terms in my delivery, the delivery proof goes out fine but is doesn't display anything, even though it definitely is populated for that record.

Any help would be appreciated.

Thanks

Nick

[annesophiecy]

Hello nickc_

I had the same issue a few days ago. The workaround that we found is to convert the content of the memo field to a string via the enrichment box.

Unfortunately, the existing function ToString() doesn't work if the length of the text field is more than 255 characters.

So we created and executed a new SQL script function (convertToString) that permits to convert a text to a VARCHAR without length limit.

Then in the enrichment box you just need to use the function as following convertToString(@terms) and use this field converted in the delivery. This has solved the issue on our side.

Best regards,

Anne-Sophie

[DarrenBiz]

annesophiecy​ - i am having the same issues and I have tried explicitly casting the memo field using a PostgreSQL command (memo)::VARCHAR but it didn't seem to fix the issue.

Can you help with how you did the actual conversion of memo (TEXT) type to String using a SQL function?

[anne-sophiec407]

Hello DarrenBiz​.

Here is the SQL script that we created:

DROP FUNCTION IF EXISTS convertToVarchar(parmValue text);

CREATE OR REPLACE FUNCTION convertToVarchar(parmValue text)

RETURNS VARCHAR AS '

DECLARE v_var_value VARCHAR DEFAULT null;

BEGIN   

    v_var_value := parmValue::TEXT;   

    RETURN v_var_value;

END;

' LANGUAGE plpgsql;

Best regards,

Anne-Sophie