Memory Threshold issue when filter large size data from DB- Any solution

Hi Martina

A single drop-down with 13000+ records is pretty unwieldy. You might want to consider some alternate patterns:

• Can you categorize the data so that you can have two related dropdowns, the first to pick a category and the second to pick from a subset of the records that are in the selected category? For example: you might have a list of cars, so the first category might be the manufacturer of the car and the second the specific model.
• Do your users have any pre-knowledge of what might be in the list? Could you use a custom picker component and let the user type into it something that would return a subset of the records?

Thank you, Stewart.

Unfortunately, not able to categorize the data right now.

In actual the Custom Picker field should be implemented in the Row List. But I tried with Single Custom Picker field.

While search, the filter shows only value "1" and throws the exception when the value is selected.

Code Snippet:

a!localVariables(

  local!selectedName,

  local!selectedRecord:rule!DSC_GetProductNameByType(cons!DSC_CONS_PRODUCTTYPES[2]),

  local!recordLabels: local!selectedRecord.productName,

  local!recordValues: local!selectedRecord.id,

  a!sectionLayout(

    contents:{

      a!pickerFieldCustom(

        label: "Product Name",

        placeholder: "Enter Name",

        maxSelections: 1,

        suggestFunction: rule!ucArrayPickerFilter(filter: _, labels: local!recordLabels, identifiers: local!recordLabels),

        selectedLabels: a!forEach(

          items: local!selectedName,

          expression: index(

            local!recordLabels,

            wherecontains(

              touniformstring(fv!item), 

              touniformstring(local!recordValues)

            )

          )

        ),

        value: local!selectedName,

        saveInto: local!selectedName

      )

    }

)

)

The trick with the custom picker is, to put the database query inside the suggest function.

Hi Martina,

Did you try to create a record type on this and call this data from the record picker as that is very fast. i had the same issue but when i turned that to a record picker it was fast.

After using casting also, couldn't get the values in the desired format and only null values.

local!test:

What are we looking at here? When you read the documentation here

- https://docs.appian.com/suite/help/22.4/Custom_Picker_Component.html

- https://docs.appian.com/suite/help/22.4/recipe-configure-an-array-picker.html

This includes all required info.

When you explain what you tried and provide some code snippets, we can support you in a better way.

Hi Stefan,

I am Changing the dropdown field into Custom picker due to the Memory Threshold issue.

The custom picker is inside the Editable Grid. After selecting the custom picker, the remaining values should be displayed. When I tried the below code, the corresponding row result is null.

a!localVariables(

  /*local!test: 'type!{urn:com:appian:dsc}DSC_ProductInfo'(),*/

  local!test1,

  local!option: rule!DSC_GetProductNameByType(cons!DSC_CONS_PRODUCTTYPES[2]),

  local!materialsHeader:{

    "Part No.",

    "Material",

    "UoM",

    "Qty",

    "",

    concat(

      "Contract Price ",

      if(

        rule!DSC_Empty_or_Null(ri!project.currency),

        "",

        concat("(", ri!project.currency.isoCode, ")")

      )

    ),

    concat(

      "Cost ",

      if(

        rule!DSC_Empty_or_Null(ri!project.currency),

        "",

        concat("(", ri!project.currency.isoCode, ")")

      )

    ),

    ""

  },

  local!currentMaterials: a!refreshVariable(

    value: if(

      rule!DSC_Empty_or_Null(ri!costEstimate.materials),

      null,

      reject(fn!isnull, ri!costEstimate.materials.productInfo)

    ),

    refreshOnReferencedVarChange: true

  ),

  local!materialsSelection: difference(

    local!option,

    cast(

      'type!{urn:com:appian:dsc}DSC_ProductInfo?list',

      local!currentMaterials

    )

  ),

  {

    a!gridLayout(

      headerCells: a!flatten(

        a!forEach(

          items: local!materialsHeader,

          expression: {

            if(

              or(fv!item = "Part No.", fv!item = "UoM", fv!item = "Material"),

              a!gridLayoutHeaderCell(label: fv!item, align: "LEFT"),

              a!gridLayoutHeaderCell(label: fv!item, align: "RIGHT")

            )

          }

        )

      ),

      columnConfigs: {

        a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 3),

        a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 5),

        a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 5),

        a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 3),

        a!gridLayoutColumnConfig(width: "ICON"),

        a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 3),

        a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 5),

        a!gridLayoutColumnConfig(width: "ICON")

      },

      rows:{

        a!forEach(items: ri!costEstimate.materials, expression:  a!gridRowLayout(

          contents: {

            /*Part No.*/

            a!richTextDisplayField(

              value: a!richTextItem(

                text: a!richTextItem(text: fv!item.productInfo.partNo)

              )

            ),

            /*Personnel*/

            /*a!dropdownField(*/

              /*label: "Language",*/

              /*labelPosition: "ABOVE",*/

              /*placeholder: "Material",*/

              /*choiceLabels: if(*/

                /*contains(*/

                  /*cast(*/

                    /*'type!{urn:com:appian:dsc}DSC_ProductInfo?list',*/

                    /*local!currentMaterials*/

                  /*),*/

                  /*fv!item.productInfo*/

                /*),*/

                /*property(*/

                  /*insert(*/

                    /*local!materialsSelection,*/

                    /*fv!item.productInfo,*/

                    /*1*/

                  /*),*/

                  /*"productName"*/

                /*),*/

                /*local!materialsSelection.productName*/

              /*),*/

              /*choiceValues: if(*/

                /*contains(*/

                  /*cast(*/

                    /*'type!{urn:com:appian:dsc}DSC_ProductInfo?list',*/

                    /*local!currentMaterials*/

                  /*),*/

                  /*fv!item.productInfo*/

                /*),*/

                /*insert(*/

                  /*local!materialsSelection,*/

                  /*fv!item.productInfo,*/

                  /*1*/

                /*),*/

                /*local!materialsSelection*/

              /*),*/

              /*value: fv!item.productInfo,*/

/*             */

              /*saveInto: {*/

                /*a!save(fv!item.productInfo, save!value),*/

                /*a!save(local!test1, save!value),*/

                /*a!save(fv!item.productPrice, rule!DSC_GetProductPrice(fv!item.productInfo.id, fv!item.productInfo.partNo, ri!project.id)),*/

                /*a!save(fv!item.contractPrice, fv!item.productPrice.price)*/

              /*},*/

              /*disabled: ri!project.isDosGenerated*/

            /*),*/

           a!pickerFieldCustom(

              label: "Material",

              instructions: "Enter the Material Name.",

              maxSelections: 1,

              suggestFunction: rule!ucArrayPickerFilter(filter: _,

              labels: if(

                contains(

                  cast(

                    'type!{urn:com:appian:dsc}DSC_ProductInfo?list',

                    local!currentMaterials

                  ),

                  fv!item.productInfo

                ),

                property(

                  insert(

                    local!materialsSelection,

                    fv!item.productInfo,

                    1

                  ),

                  "productName"

                ),

                local!materialsSelection.productName

              ),

              identifiers:if(

                  contains(

                    cast(

                      'type!{urn:com:appian:dsc}DSC_ProductInfo?list',

                      local!currentMaterials

                    ),

                    fv!item.productInfo

                  ),

                  insert(

                    local!materialsSelection,

                    fv!item.productInfo,

                    1

                  ),

                  local!materialsSelection

                ),

              ),

              selectedLabels:

                a!forEach(

                items: fv!item,

                expression: index(

                  if(

                    contains(

                      cast(

                        'type!{urn:com:appian:dsc}DSC_ProductInfo?list',

                        local!currentMaterials

                      ),

                      fv!item.productInfo

                    ),

                    property(

                      insert(

                        local!materialsSelection,

                        fv!item.productInfo,

                        1

                      ),

                      "productName"

                    ),

                    local!materialsSelection.productName

                  ),                  

                  wherecontains(

                    fv!item.productInfo,                    

                      if(

                      contains(

                        cast(

                          'type!{urn:com:appian:dsc}DSC_ProductInfo?list',

                          local!currentMaterials

                        ),

                        fv!item.productInfo

                      ),

                      insert(

                        local!materialsSelection,

                        fv!item.productInfo,

                        1

                      ),

                      local!materialsSelection

                    ),

                     )

                )

              ),

              value: fv!item.productInfo,

              saveInto:{

                /*a!save(fv!item.productInfo, save!value),*/

                a!save(local!test1, save!value),

                a!save(fv!item.productInfo,

                  cast(

                    'type!{urn:com:appian:dsc}DSC_ProductInfo?list',

                  local!test1

                )),

                /*a!save(fv!item.productPrice, rule!DSC_GetProductPrice(fv!item.productInfo.id, fv!item.productInfo.partNo, ri!project.id)),*/

                /*a!save(fv!item.contractPrice, fv!item.productPrice.price)*/

              },

              disabled: ri!project.isDosGenerated

            ),

            /*UoM*/

            a!richTextDisplayField(

              value: a!richTextItem(

                text: fv!item.productInfo.uom

              ),

              align: "LEFT"

            ),

            /*Days*/

            a!integerField(

              value: fv!item.numberOfUsage,

              saveInto: {

                a!save(fv!item.numberOfUsage, save!value),

                a!save(

                  fv!item.cost,

                  fv!item.numberOfUsage * if(

                    rule!DSC_Empty_or_Null(fv!item.contractPrice),

                    0,

                    fv!item.contractPrice

                  )

                )

              },

              disabled: ri!project.isDosGenerated,

              align: "RIGHT"

            ),

            a!richTextDisplayField(

              value: a!richTextItem(

                text: "                                       "

              ),

              align: "LEFT"

            ),

            /*Contract Price (USD)*/

            a!floatingPointField(

              value: fv!item.contractPrice,

              saveInto: {

                a!save(fv!item.contractPrice, save!value),

                a!save(fv!item.productPrice.price, save!value),

                a!save(

                  fv!item.cost,

                  fv!item.contractPrice * if(

                    rule!DSC_Empty_or_Null(fv!item.numberOfUsage),

                    0,

                    fv!item.numberOfUsage

                  )

                )

              },

              disabled: ri!project.isDosGenerated,

              align: "RIGHT"

            ),

            /*Cost (USD)*/

            a!richTextDisplayField(

              value: a!richTextItem(

                text: if(

                  rule!DSC_Empty_or_Null(fv!item.cost),

                  0,

                  fixed(roundup(fv!item.cost))

                )

              ),

              align: "RIGHT"

            ),

            a!buttonArrayLayout(

              buttons: a!buttonWidget(

                icon: "remove",

                saveInto: a!save(ri!costEstimate.materials, remove(ri!costEstimate.materials, fv!index)),

                style: "LINK",

                showWhen:

                /*and(*/

                  rule!DSC_Empty_or_Null_or_False(ri!project.isDosGenerated)

                /*?, count(ri!costEstimate.materials)>1)*/

              ),

              align: "START"

            )

          },

          selectionDisabled: false

        ))

      },

      addRowLink: a!dynamicLink(

        label: "Add Row",

        saveInto: {

          a!save(

            ri!costEstimate.projectId,

            ri!project.id

          )

          ,

          a!save(

          ri!costEstimate.materials,

          append(

            ri!costEstimate.materials,

            'type!{urn:com:appian:dsc}DSC_WellboreGeneralInfo'(type: cons!DSC_CONS_PRODUCTTYPES[2])

          )

        )},

        showWhen: rule!DSC_Empty_or_Null_or_False(ri!project.isDosGenerated)

      ),

      borderStyle: "LIGHT"

    ),

    /*Sub Total (USD)*/

    a!cardLayout(

      contents: {

        a!columnsLayout(

          columns: {

            a!columnLayout(

              contents: {

                a!richTextDisplayField(

                  value: a!richTextItem(

                    text:  concat(

                      "Sub Total ",

                      if(

                        rule!DSC_Empty_or_Null(ri!project.currency),

                        "",

                        concat("(", ri!project.currency.isoCode, ")")

                      )

                    ),

                    style: "STRONG"

                  ),

                  align: "LEFT"

                )

              },

              width: "WIDE"

            ),

            a!columnLayout(

              contents: {

                a!richTextDisplayField(

                  value: a!richTextItem(

                    text: "",

                    style: "STRONG"

                  ),

                  align: "CENTER"

                )

              },

              width: "WIDE"

            ),

            a!columnLayout(

              contents: {

                a!richTextDisplayField(

                  value: a!richTextItem(

                    text:

                    concat( if(

                      rule!DSC_Empty_or_Null(ri!project.currency),

                      "",

                      ri!project.currency.symbol

                    ),

                    " ",

                    padright(   if(

                      rule!DSC_Empty_or_Null(ri!costEstimate.materials),

                      0,

                      fixed(roundup(sum(ri!costEstimate.materials.cost)))

                    ), 15)),

                    style: "STRONG"

                  ),

                  align: "RIGHT"

                )

              },

              width: "WIDE"

            )

          },

          alignVertical: "MIDDLE"

        )

      },

      style: "#E4E8EC",

      marginBelow: "STANDARD",

      showBorder: false

    )

  }

)

I used local! test1 to see how the data is getting printed