AnsweredAssumed Answered

How do we pull related values from value list?

Question asked by GoodS on Apr 5, 2016
Latest reply on Apr 11, 2016 by user19752

I have a value list containing ID from "Colors" table.  “Colors" table has content such as:

 

IDColorShort
1RedRd
2GreenGr
3BlueBu
4MagentaMa
5YellowYw
6BananaBa

 

Our value list is generated through a script/ calculation and stored in a related table (through a join table).  Value list field is called “color list”, stored in table called “Color Set”.  Content of “color list” is selected ID’s from “Colors” table, as they are obtained through join table “Color set join”. 

 

Current script is:

Set Field [Color set::color list; ValueList ( Color set join::_kf_color )]

Commit Records/Requests [No Dialog]

 

“Color set” table primary key is stored in join table “Color set join”, so that sequence and color selection are stored as a Return delimited list of “Colors::ID” values, stored in “Color set join::_kf_color”.

 

Brief:

We require “Colors::Color” from this process for a priority list used to build layered garments, with primary (first item), separated by a space, then remaining values from “Colors::Short” field.  A process the current work flow participants are expecting.

 

Example result:

“Red BuYwBa”

 

Currently, value list contains only Return delimited ID numbers.  We need Color and Short field content to be concatenated into a string, taken from ID’s taken from “Colors” table two steps away..  These principles apply to other processes we are trying to implement, such as build lists, forensic steps, work flows etc., where we select values stored in a portal, though a related join table.

 

I have seen the ExecuteSQL suggested solution (simple and brilliant) to a similar problem posted recently, but after a whole day trying to figure it out, could a resident genius provide another clue for either script, calculation, or headache tablet?

 

Is this type of conversion possible?  I could not find the way to script the parse via the list of IDs and then lookup their corresponding color value to concatenate and store.  So close, but no banana.

Outcomes