Controlling WKF table field length

[awijaya]

I have a schema with a string field of length 2100.

In enrichment, an expression field is added which does a substring of that field such as this: Substring(@recommendation, 101, Length(@recommendation)-100)

This is the error I get:

03/03/2020 12:01:29 PM enrich PostgreSQL error: ERROR: value too long for type character varying(255)

Which mean the WKF field is created with length of 255, while the source of that expression field is much longer than that.

Is there a way to define the length for the expression field or maybe controlling the WKF field length instead?

[Jonathon_wodnicki · Accepted solution]

Hi,

This isn't possible with ordinary ootb query activities.

Alternative options in descending order of maintainability:

• Use sql or js activity to alter the temp table's structure and populate it with substrings
• Write substrings to a file then load them into workflow with Data loading (file) activity formatted with long text col
• Create a permanent temp schema with big string col and in workflow truncate then use Update data activity to write substrings to it, with outgoing transition (replaces enrichment)
• Create your own temp schema in the workflow in js with undocumented registerSchema(), setSchemaSqlTable(), buildSqlTable(), etc.

Thanks,

-Jon

[awijaya]

Hi Jon,

These are really thoughtful answer. Thank you for that.

I ended up using Edit Schema activity to create the expression. 

Btw, how do you find those undocumented js functions? I tried to look them up under Configuration > JavaScript codes, but they are not there.

Thanks,

- Alwi

[Jonathon_wodnicki]

That's very interesting. I haven't used Edit Schema before, wonder why it behaves differently here than the other activities it shares UI with. Undocumented stuff you find poking around- CRM connector's js for example uses those functions.

[awijaya]

Seems like Edit Schema is just editing the temporary schema (WKF). Just like editing schema source in configuration, but instead it's on the fly. So, an expression field can be added to the WKF to be used further downstream. Since it is just an expression to the temp schema, it doesn't create a database column and no data being written. In my impression, WKF creation of an enrichment should factor in at least the largest column size involved in the formula/expression. This is not ideal, but maybe better than just use default 255. Thanks for the tip on the undocumented stuff. Really neat.