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

Certified Associate Developer

In my application, the DB contains the fields (id,name,unit,type,isActive). The name and unit values are retrieved based on the type and shown in the UI dropdown and that is the List.

One Particular type contains 13000+ active records and gives Memory Threshold issue when it tries to load the data. In the expression rule, the batch size as set as -1 to retrieve all the data's from DB. 

To avoid this exception, I added the batch size as 100 in a!pagingInfo(). But the problem is now able to see only the 100 records in dropdown field and not all the 13000+records.

 Is there any other option is available to resolve this problem.

Regards

Martina

  Discussion posts and replies are publicly visible

Parents Reply Children
  • 0
    Certified Associate Developer
    in reply to harshavardhanv

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

    local!test:

  • 0
    Certified Lead Developer
    in reply to Martina Joseph

    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.

  • 0
    Certified Associate Developer
    in reply to Stefan Helzle

    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

        )

      }

    )

  • 0
    Certified Associate Developer
    in reply to Martina Joseph

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