Skip to main content
sivasuryap0002
sivasuryap0002
April 25, 2023
Solved

Dynamic CDT

  • April 25, 2023
  • 12 replies
  • 0 views

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

Best answer by stefanhelzle0001

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.

12 replies

stefanhelzle0001
stefanhelzle0001
April 25, 2023

While I do not recommend to load a unknown volume of data into memory, is there a reason a map does not make it? Alternatively a dictionary. Did you try any of these two?

sivasuryap0002
sivasuryap0002Author
April 25, 2023

Here The data will be minimal only and map and dictionary both are not possible as the field names are dynamic and logic based ie.,headers of the Excel and the excel cannot be static.
Ex:  a!map(local!exceldata.result[1].values[1]):"value")
Error : Expression evaluation error at function a!map [line 48]: Total keys and values must be equal. Received 0 keys and 1 values

shikhat
shikhat
April 25, 2023

Hi , Can you share the output of local!exceldata variable above?

S
sanchitg0002
April 25, 2023

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
    )
  }
)

Terms & ConditionsAccessibility statement