Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Using long text datatype in delivery

NickC_
Level 2
Level 2

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

3 Replies
annesophiecy
Level 1
Level 1

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
Level 7
Level 7

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
Level 1
Level 1

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