How to save data spanned over multiple CDTs columns?

Question

My scenario is to create an editable grid which takes user data and simply save into the database but the catch is the column data in the grid is from multiple different CDTs or tables. 
 For example : Two columns "Quantity" and "Price" are from one CDT table called "Request" 
 Another one column "Comment" is from table "Comment" and there is "RequestType" from reference table "RequestTypeRef" which can have any of the two values "Supply" or "Service" and lastly "Item" which is dependent on "Request Type", if it is "Supply" then "Item" should save in "SupplyTypeName" of "SupplyTypeRef", else in "ServiceTypeRef". 
 Below is the code I've written and for now taking rule inputs as request , requestTypeRef , serviceTypeRef , supplyTypeRef and comment , all are array of these datatypes because user can make multiple requests (not more than 5) at a time. 
 For better understanding, attaching the screenshot of what I've developed so far. 
 
 a!localVariables(
 a!formLayout(
 label: "Create New Request",
 instructions: "Create a new Request Type, maximum 5 requests can be created at a time",
 contents: {
 a!gridLayout(
 headerCells: {
 a!gridLayoutHeaderCell(
 label: "Request Type"
 ),
 a!gridLayoutHeaderCell(
 label: "Item",
 helpTooltip: "Enter the Item Name to request"
 ),
 a!gridLayoutHeaderCell(
 label: "Quantity"
 ),
 a!gridLayoutHeaderCell(
 label: "Price ($)"
 ),
 a!gridLayoutHeaderCell(
 label: "Comment"
 )/*,*/
 /*a!gridLayoutHeaderCell(*/
 /*label: ""*/
 /*)*/
 
 },
 columnConfigs: {
 a!gridLayoutColumnConfig(
 width: "DISTRIBUTE",
 weight: 3
 ),
 a!gridLayoutColumnConfig(
 width: "DISTRIBUTE",
 weight: 3
 ),
 a!gridLayoutColumnConfig(
 width: "DISTRIBUTE",
 weight: 3
 ),
 a!gridLayoutColumnConfig(
 width: "DISTRIBUTE",
 weight: 3
 ),
 a!gridLayoutColumnConfig(
 width: "DISTRIBUTE",
 weight: 3
 ),
 a!gridLayoutColumnConfig(
 width: "ICON"
 )
 },
 rows: a!gridRowLayout(
 contents: {
 a!dropdownField(
 label: "request type",
 placeholderLabel: "-- Please Select-- ",
 choiceLabels: cons!OSR_TEXT_ARRAY_REQUEST_TYPE,
 choiceValues: cons!OSR_TEXT_ARRAY_REQUEST_TYPE,
 value: index(
 ri!requestTypeRef,
 "RequestTypeName",
 {}
 ),
 saveInto: ri!requestTypeRef.RequestTypeName,
 required: true
 ),
 a!textField(
 label: "item",
 value: if(
 ri!requestTypeRef.RequestTypeName = "Service",
 index(
 ri!serviceTypeRef,
 "ServiceTypeName",
 {}
 ),
 index(
 ri!supplyTypeRef,
 "SupplyTypeName",
 {}
 )
 ),
 saveInto: if(
 ri!requestTypeRef.RequestTypeName = "Service",
 ri!serviceTypeRef.ServiceTypeName,
 ri!supplyTypeRef.SupplyTypeName
 ),
 required: true
 ),
 a!integerField(
 label: "quantity",
 value: index(
 ri!request,
 "Quantity",
 {}
 ),
 saveInto: ri!request.Quantity,
 required: true
 ),
 a!textField(
 label: "price",
 value: todecimal(
 index(
 ri!request,
 "Price",
 {}
 )
 ),
 saveInto: ri!request.Price,
 required: true
 ),
 a!paragraphField(
 label: "comment",
 value: index(
 ri!comment,
 "Comment",
 {}
 ),
 saveInto: ri!comment.Comment
 )
 }
 ),
 rowHeader: 1
 )
 },
 buttons: a!buttonLayout(
 primaryButtons: {
 a!buttonWidget(
 label: cons!OSR_TEXT_BUTTON_SUBMIT,
 submit: true,
 style: "PRIMARY"
 )
 },
 secondaryButtons: {
 a!buttonWidget(
 label: cons!OSR_TEXT_BUTTON_CANCEL,
 value: true,
 saveInto: ri!cancel,
 submit: true,
 style: "NORMAL",
 validate: false
 )
 }
 )
 )
)

Peter Lewis · Accepted Answer

The way you have it set up now, you have added a single grid row, which means that the grid will always have exactly one row. However, you mentioned that you wanted to be able to add up to 5 rows depending on the data. 
 In order to set that up, you will need to use a!forEach() to iterate over the list of items. See this recipe for how to set up the a!forEach() for your grid rows: Add, Edit, and Remove data in an inline editable grid . 
 As far as saving into multiple CDTs, you will need to make sure that each CDT has the same number of items. You will notice in the recipe that an add row link appends a new item to the CDT used; you will need to do this for all of your CDTs to ensure they are the same length array.

Krishna Chaitanya · Answer

a!localVariables(
 a!formLayout(
 label: "",
 contents: {
 a!gridLayout(
 totalCount: count(
 ri!name
 ),
 headerCells: {
 a!gridLayoutHeaderCell(
 label: "Name"
 ),
 a!gridLayoutHeaderCell(
 label: "Age"
 ),
 a!gridLayoutHeaderCell(
 label: "Comment"
 ),
 a!gridLayoutHeaderCell(
 label: ""
 )
 },
 columnConfigs: {
 a!gridLayoutColumnConfig(
 width: "DISTRIBUTE",
 weight: 3
 ),
 a!gridLayoutColumnConfig(
 width: "DISTRIBUTE",
 weight: 3
 ),
 a!gridLayoutColumnConfig(
 width: "DISTRIBUTE",
 weight: 3
 ),
 a!gridLayoutColumnConfig(
 width: "ICON"
 )
 },
 rows: a!forEach(
 items: ri!name,
 expression: a!gridRowLayout(
 contents: {
 a!textField(
 label: "first name " & fv!index,
 value: fv!item,
 saveInto: fv!item,
 required: true
 ),
 a!textField(
 label: "Age",
 value: ri!age[fv!index],
 saveInto: ri!age[fv!index],
 required: true
 ),
 a!paragraphField(
 label: "Comment",
 value: ri!comment[fv!index],
 saveInto: ri!comment[fv!index],
 required: true
 ),
 a!imageField(
 label: "delete ",
 images: a!documentImage(
 document: a!iconIndicator(
 "REMOVE"
 ),
 altText: "Remove Employee",
 caption: "Remove ",
 link: a!dynamicLink(
 value: fv!index,
 saveInto: {
 a!save(
 ri!name,
 remove(
 ri!name,
 save!value
 )
 ),
 a!save(
 ri!age,
 remove(
 ri!age,
 save!value
 )
 ),
 a!save(
 ri!comment,
 remove(
 ri!comment,
 save!value
 )
 )
 }
 )
 ),
 size: "ICON"
 )
 },
 id: fv!index
 )
 ),
 addRowlink: a!dynamicLink(
 label: "Add Employee",
 value: null,
 saveInto: {
 a!save(
 ri!name,
 append(
 ri!name,
 save!value
 )
 ),
 a!save(
 ri!age,
 append(
 ri!age,
 save!value
 )
 ),
 a!save(
 ri!comment,
 append(
 ri!comment,
 save!value
 )
 )
 }
 ),
 rowHeader: 1
 )
 },
 buttons: a!buttonLayout(
 primaryButtons: a!buttonWidget(
 label: "Submit",
 submit: true
 )
 )
 )
)
 
 use this code as reference

Mike Schmitt · Answer

Your primary issue is that you're only defining one row in the rows: parameter of your grid. This should instead be an array of a!gridRowLayout() objects (not just one like you currently have). Krishna's example above has this configured correctly, for reference. In your case I'd guess you should iterate over your ri!requestTypeRef array (using a!forEach) and define the rows that way. Luckily this only requires a few specific edits to your current code. 
 Secondly I'm a bit confused what you're trying to do while saving into serviceTypeRef or supplyTypeRef - if these each are associated with a specific "Request Type" row, where is the logical connection between them? From your screenshot I can tell that "service type ref" has only ID and Name as distinguishing features, so what relation does each entry of this have to a specific supply type row? My recommendation overall is that you reexamine your overall data structure and make sure it's doing what you think it should be doing, because the impression I get here is that there are missing elements.

Mike Schmitt · Answer

In that case, you should probably modify your Supply Type and Service Type CDTs to include a Request Type ID, so that they can be tied together in the database and in your editable grid in a meaningful way. Otherwise it will be difficult to nearly imossible to meaningfully tie a new entry of either to the appropriate row in your grid, for starters.

How to save data spanned over multiple CDTs columns?

Top Replies