Controlling WKF table field length

Avatar

Avatar
Validate 1
Level 1
awijaya
Level 1

Likes

0 likes

Total Posts

3 posts

Correct reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
awijaya
Level 1

Likes

0 likes

Total Posts

3 posts

Correct reply

0 solutions
Top badges earned
Validate 1
View profile
awijaya
Level 1

03-03-2020

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)

 

awijaya_0-1583266812833.png

 

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?

 

 

 

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Establish
MVP
wodnicki
MVP

Likes

982 likes

Total Posts

1,097 posts

Correct reply

517 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile

Avatar
Establish
MVP
wodnicki
MVP

Likes

982 likes

Total Posts

1,097 posts

Correct reply

517 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile
wodnicki
MVP

03-03-2020

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

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.

Answers (0)