Dynamic CDT

Question

Hi All, Is there any way to create the fields dynamically in cdt and then assign values. 
 I have a use case, User will be uploading a excel and i need to show the excel data in grid format (excel can be different),So for that i am using readexceldata function and and I am trying to create a cdt with the column names and their respective row values (here column names are dynamic) to use that in the data field for grid field. 
 
 Ex:excel file has 2 columns A,B now the cdt will be an array of row data like {{A:first row A value,B:first row B value}, {A:seconf row A value,B:second row B value}} and if the excel has 3 columsn as A,B,C then the expected cdt should be {{A:first row A value,B:first row B value,C:first row C value},{A:seconf row A value,B:second row B value,C:second row C value}} and use this cdt in gridfield data parameter. 
 
 Thanks in Advance, SSP

Stefan Helzle · Answer

Trying to import Excel files with no clear structure is inherently difficult and prone to errors. 
 Again, what I do for the first step, is to do a full raw import without looking at any structure. The logic comes in the second step. You just have to make sure that your database staging table has enough columns.

Mike Schmitt · Answer

You'd probably need to read just the first (header) row and use those as your data keys. It can be a bit tricky but in the past I've done it by manually transforming the result into a JSON string, then casting that back from JSON to dictionary.

Sanchit Gupta (Xebia) · Answer

You can use readexcelsheetpaging() function available in Excel Tools plugin which return a datasubset that can be directly plugged to the data parameter of gridField(). 
 Example: 
 a!localVariables(
 local!data: readexcelsheetpaging(
 excelDocument: cons!PA_TEST_DOCUMENT,
 sheetNumber: 1,
 pagingInfo: a!pagingInfo(1, 100)
 ).data,
 local!labels: index(local!data, 1, "values", null),
 {
 a!gridField(
 label: "Read-only Grid",
 labelPosition: "ABOVE",
 data: todatasubset(
 arrayToPage: remove(local!data, 1),
 pagingConfiguration: a!pagingInfo(1, 100)
 ),
 columns: a!forEach(
 local!labels,
 a!gridColumn(
 label: fv!item,
 value: fv!row.values[fv!index]
 )
 ),
 validations: {},
 pagingSaveInto: fv!pagingInfo
 )
 }
)

Dynamic CDT

Top Replies