Save row values of one CDT as column values of another CDT

Question

Hi Team, I have a CDT in the below format : 
 id , item id, field name, field value, field seq no where id is the primary key. Eg of values : 1, 1, Name, Alex, 1 
 2, 1, Age, 45, 2 
 3, 1, DOJ, 2022-04-05 08:56:00, 3 
 4, 1, Designation, Engineer, 5 
 Field values in the above CDT are taken from user input in a form. 
 I have another CDT in below format : 
 id, item id, value 1, value 2, value 3, value 4, value 5, value 6 I want the field values from 1st CDT to be saved to value columns of second CDT as per field seq no. Like for the above example of CDT 1, my CDT2 should return : 1, 1, Alex, 45, 2022-04-05 08:56:00, null, Engineer, null How can we achieve this in Appian? Our first preference would be achieving this either in an interface or expression rule. We use process model in back end so we can use that also. Appian version 21.1 
 Any suggestions would help us out.

Dave Lewis · Answer

a!save( 
 CDT2,
 update(
 data: CDT2,
 index: a!forEach(
 items: CDT1.fieldSeq,
 expression:choose(key: fv!item, choice1: "value1", choice2: "value2"...),
 )/* a!forEach returns a list, in this case a list of indeces */
 value: CDT1.fieldValue /*list of values goes into list of indeces*/
 )
) 
 Bear in mind this is pseudo-code that I'm not trying to test out with the above scenario. Also keep in mind that choose function requires that every value from 1 to n be represented. If you have a 5, you also must have 4, 3, 2, and 1. You can't skip any. It doesn't look like you do, so it's a good use case for choose. 
 Let us know if it works.

Dave Lewis · Answer

Choose should work fine so long as you have the maximum possible value in the choose function. It is iterative in a sense, because for each individual value, you pick the location to save to in CDT 2. So if you have sequences 5, 17, 4, as long as the choose goes at least up to 17, the forEach loop will return "value5", "value17", "value4". 
 Now, if the name of the place that you're supposed to save a "5" changes depending on the size of the CDT, that's a nightmare I don't want to think about right now. In that case, you might have to create a gigantic if() nest, or if the sets of possibilities is finite in length, you might consider a Decision object. 
 Also, you can use updateCDT to achieve similar effect, but a!update is better. You'll probably have to a!forEach to create each individual updateCDT statement. Consider upgrade, or telling others that you should upgrade.