• State Verified Answer
  • Replies 13 replies
  • Subscribers 13 subscribers
  • Views 4687 views
  • Users 0 members are here
Related Discussions
Home » Discussions » New to Appian

Filter rows from read only grid using multichoice dropdown

shamima0001

Hi,

I have a read only grid as shown below, I want to filter data by selecting value from "Status" dropdown and by pressing "Search" button.

Please help me with it.

  Discussion posts and replies are publicly visible

Parents
  • 0
    Appian Employee

    Can you paste the current SAIL you have? It will help explain how to filter correctly. 

  • 0 in reply to Danny Verb

    Hi Danny,

    Please have a look on the SAIL below.

    {
  a!columnsLayout(
    columns: {
      a!columnLayout(
        contents: {
          a!multipleDropdownField(
            label: "Status",
            labelPosition: "ABOVE",
            placeholder: "Select Status",
            choiceLabels: {
              "Option 1",
              "Option 2",
              "Option 3",
              "Option 4",
              "Option 5",
              "Option 6",
              "Option 7",
              "Option 8",
              "Option 9",
              "Option 10",
              "Option 11",
              "Option 12"
            },
            choiceValues: { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 },
            saveInto: {},
            searchDisplay: "AUTO",
            validations: {}
          )
        }
      )
    },
    showDividers: false
  ),
  a!sectionLayout(
    label: "",
    contents: {
      a!columnsLayout(
        columns: {
          a!columnLayout(
            contents: {
              a!buttonArrayLayout(
                buttons: {
                  a!buttonWidget(
                    label: "Search",
                    icon: "search",
                    submit: true,
                    style: "LINK"
                  )
                },
                align: "END"
              )
            }
          )
        }
      )
    },
    marginBelow: "NONE"
  ),
  a!gridField(
    label: "",
    labelPosition: "ABOVE",
    data: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam',
    columns: {
      a!gridColumn(
        label: "Start Date",
        sortField: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{startDate}startDate',
        value: fv!row['recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{startDate}startDate'],
        align: "START"
      ),
      a!gridColumn(
        label: "End Date",
        sortField: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{endDate}endDate',
        value: fv!row['recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{endDate}endDate'],
        align: "START"
      ),
      a!gridColumn(
        label: "Status",
        sortField: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{status}status',
        value: fv!row['recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{status}status']
      )
    },
    pageSize: 25,
    initialSorts: {
      a!sortInfo(
        field: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{changeOrderNumber}changeOrderNumber',
        ascending: true
      )
    },
    selectable: false,
    validations: {},
    spacing: "STANDARD",
    rowHeader: null,
    refreshOnReferencedVarChange: true,
    refreshAfter: "RECORD_ACTION",
    showSearchBox: false,
    showRefreshButton: false,
    actionsDisplay: "LABEL"
  )
}

  • +1
    Appian Employee
     in reply to shamima0001

    In the data parameter on your grid, use the function a!recordData(). Here you can add a!queryFilter() to the filters parameter. To filter by the dropdown, you should create a local variable called local!selection. See code below:

    a!localVariables(
    local!selection,
    {
      a!columnsLayout(
        columns: {
          a!columnLayout(
            contents: {
              a!multipleDropdownField(
                label: "Status",
                labelPosition: "ABOVE",
                placeholder: "Select Status",
                choiceLabels: {
                  "Option 1",
                  "Option 2",
                  "Option 3",
                  "Option 4",
                  "Option 5",
                  "Option 6",
                  "Option 7",
                  "Option 8",
                  "Option 9",
                  "Option 10",
                  "Option 11",
                  "Option 12"
                },
                choiceValues: { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 },
                value: local!selection,
                saveInto: local!selection,
                searchDisplay: "AUTO",
                validations: {}
              )
            }
          )
        },
        showDividers: false
      ),
      a!sectionLayout(
        label: "",
        contents: {
          a!columnsLayout(
            columns: {
              a!columnLayout(
                contents: {
                  a!buttonArrayLayout(
                    buttons: {
                      a!buttonWidget(
                        label: "Search",
                        icon: "search",
                        submit: true,
                        style: "LINK"
                      )
                    },
                    align: "END"
                  )
                }
              )
            }
          )
        },
        marginBelow: "NONE"
      ),
      a!gridField(
        label: "",
        labelPosition: "ABOVE",
        data: a!recordData(
            recordType: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam',
            filters: {
                a!queryFilter(
                    recordType!ABC_EXam.fields.status,
                    "in",
                    local!selection
                )
            }
        ),
        columns: {
          a!gridColumn(
            label: "Start Date",
            sortField: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{startDate}startDate',
            value: fv!row['recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{startDate}startDate'],
            align: "START"
          ),
          a!gridColumn(
            label: "End Date",
            sortField: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{endDate}endDate',
            value: fv!row['recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{endDate}endDate'],
            align: "START"
          ),
          a!gridColumn(
            label: "Status",
            sortField: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{status}status',
            value: fv!row['recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{status}status']
          )
        },
        pageSize: 25,
        initialSorts: {
          a!sortInfo(
            field: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{changeOrderNumber}changeOrderNumber',
            ascending: true
          )
        },
        selectable: false,
        validations: {},
        spacing: "STANDARD",
        rowHeader: null,
        refreshOnReferencedVarChange: true,
        refreshAfter: "RECORD_ACTION",
        showSearchBox: false,
        showRefreshButton: false,
        actionsDisplay: "LABEL"
      )
    }
)

  • 0 in reply to Danny Verb

    Filter worked, but my expectation is it should filter on "Search" button press.

  • 0
    Certified Senior Developer
     in reply to shamima0001

    Hi,
    why do you want to do it on the search button? is there any UX advantag?
    if not, the following code should support you:

    a!localVariables(
local!selection,
local!search,
    {
        a!columnsLayout(
            columns: {
                a!columnLayout(
                    contents: {
                        a!multipleDropdownField(
                            label: "Status",
                            labelPosition: "ABOVE",
                            placeholder: "Select Status",
                            choiceLabels: {
                            "Option 1",
                            "Option 2",
                            "Option 3",
                            "Option 4",
                            "Option 5",
                            "Option 6",
                            "Option 7",
                            "Option 8",
                            "Option 9",
                            "Option 10",
                            "Option 11",
                            "Option 12"
                            },
                            choiceValues: { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 },
                            value: local!selection,
                            saveInto: local!selection,
                            searchDisplay: "AUTO",
                            validations: {}
                        )
                    }
                )
            },
            showDividers: false
        ),
        a!sectionLayout(
            label: "",
            contents: {
                a!columnsLayout(
                    columns: {
                        a!columnLayout(
                            contents: {
                                a!buttonArrayLayout(
                                    buttons: {
                                        a!buttonWidget(
                                            label: "Search",
                                            icon: "search",
                                            saveInto: {
                                                a!save(
                                                    target: local!search,
                                                    value: local!selection
                                                )
                                            },
                                            submit: true,
                                            style: "LINK"
                                        )
                                    },
                                    align: "END"
                                )
                            }
                        )
                    }
                )
            },
            marginBelow: "NONE"
        ),
        a!gridField(
            label: "",
            labelPosition: "ABOVE",
            data: a!recordData(
                recordType: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam',
                filters: {
                    a!queryFilter(
                        recordType!ABC_EXam.fields.status,
                        "in",
                        local!search
                    )
                }
            ),
            columns: {
                a!gridColumn(
                    label: "Start Date",
                    sortField: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{startDate}startDate',
                    value: fv!row['recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{startDate}startDate'],
                    align: "START"
                ),
                a!gridColumn(
                    label: "End Date",
                    sortField: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{endDate}endDate',
                    value: fv!row['recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{endDate}endDate'],
                    align: "START"
                ),
                a!gridColumn(
                    label: "Status",
                    sortField: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{status}status',
                    value: fv!row['recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{status}status']
                )
            },
            pageSize: 25,
            initialSorts: {
                a!sortInfo(
                    field: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{changeOrderNumber}changeOrderNumber',
                    ascending: true
                )
            },
            selectable: false,
            validations: {},
            spacing: "STANDARD",
            rowHeader: null,
            refreshOnReferencedVarChange: true,
            refreshAfter: "RECORD_ACTION",
            showSearchBox: false,
            showRefreshButton: false,
            actionsDisplay: "LABEL"
        )
    }
)

  • 0 in reply to Danny Verb

    Hi ,

    I have one more question here, how can we filter table with two columns. I have been able to filter by one column (SAIL below) but no clue how to include second filter criteria.

    In the table below I have been able to filter by "Status" but I what to filter by "Status= Complete" and "End Date = 11/2/2020" (imagine we have one more text box "Select End Date" just aside of "Status" dropdown).

     a!recordData(
          recordType: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam',
          filters: {
            a!queryFilter(
              'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{status}status',
              "in",
              local!currentStatus
            )
          }
        )

  • 0
    Appian Employee
     in reply to shamima0001 

    a!localVariables(
    local!selection,
    local!date,
    {
      a!columnsLayout(
        columns: {
          a!columnLayout(
            contents: {
              a!multipleDropdownField(
                label: "Status",
                labelPosition: "ABOVE",
                placeholder: "Select Status",
                choiceLabels: {
                  "Option 1",
                  "Option 2",
                  "Option 3",
                  "Option 4",
                  "Option 5",
                  "Option 6",
                  "Option 7",
                  "Option 8",
                  "Option 9",
                  "Option 10",
                  "Option 11",
                  "Option 12"
                },
                choiceValues: { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 },
                value: local!selection,
                saveInto: local!selection,
                searchDisplay: "AUTO",
                validations: {}
              )
            }
          ),
          a!columnLayout(
            contents: {
                a!dateField(
                    label: "End Date",
                    value: local!date,
                    saveInto: local!date
                )
            }
          )
        },
        showDividers: false
      ),
      a!sectionLayout(
        label: "",
        contents: {
          a!columnsLayout(
            columns: {
              a!columnLayout(
                contents: {
                  a!buttonArrayLayout(
                    buttons: {
                      a!buttonWidget(
                        label: "Search",
                        icon: "search",
                        submit: true,
                        style: "LINK"
                      )
                    },
                    align: "END"
                  )
                }
              )
            }
          )
        },
        marginBelow: "NONE"
      ),
      a!gridField(
        label: "",
        labelPosition: "ABOVE",
        data: a!recordData(
            recordType: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam',
            filters: {
                a!queryFilter(
                    recordType!ABC_EXam.fields.status,
                    "in",
                    local!selection
                ),
                a!queryFilter(
                    recordType!ABC_EXam.fields.endDate,
                    "=",
                    local!date
                )
            }
        ),
        columns: {
          a!gridColumn(
            label: "Start Date",
            sortField: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{startDate}startDate',
            value: fv!row['recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{startDate}startDate'],
            align: "START"
          ),
          a!gridColumn(
            label: "End Date",
            sortField: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{endDate}endDate',
            value: fv!row['recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{endDate}endDate'],
            align: "START"
          ),
          a!gridColumn(
            label: "Status",
            sortField: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{status}status',
            value: fv!row['recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{status}status']
          )
        },
        pageSize: 25,
        initialSorts: {
          a!sortInfo(
            field: 'recordType!{dc0053d9-f64e-4075-99f9-0f4948307dab}ABC_Exam.fields.{changeOrderNumber}changeOrderNumber',
            ascending: true
          )
        },
        selectable: false,
        validations: {},
        spacing: "STANDARD",
        rowHeader: null,
        refreshOnReferencedVarChange: true,
        refreshAfter: "RECORD_ACTION",
        showSearchBox: false,
        showRefreshButton: false,
        actionsDisplay: "LABEL"
      )
    }
)

    Similar to the other dropdown, I made a local variable for 'date' and then use that local variable in a queryFilter. If I wanted to have a date range, I could put two dateFields in a sidebyside layout and have one local variable for each field. Then, I would have two query filters to filter for records greater than or equal to the start date and less than or equal to the end date.

  • 0 in reply to Danny Verb

    When I am trying the you suggested and selected a value from "Status" dropdown immediately before even selecting value for "End Date" I am getting this error.

    Could not display interface. Please check definition and inputs. Interface Definition: Expression evaluation error at function a!gridField [line 128]: Expression evaluation error at function a!recordData [line 140]: The a!queryFilter function has an invalid value for the “value” parameter. When the value of “operator” is “=” “value” must not be null or empty.

Reply
  • 0 in reply to Danny Verb

    When I am trying the you suggested and selected a value from "Status" dropdown immediately before even selecting value for "End Date" I am getting this error.

    Could not display interface. Please check definition and inputs. Interface Definition: Expression evaluation error at function a!gridField [line 128]: Expression evaluation error at function a!recordData [line 140]: The a!queryFilter function has an invalid value for the “value” parameter. When the value of “operator” is “=” “value” must not be null or empty.

Children
  • +1
    Appian Employee
     in reply to shamima0001

    Using the 'applyWhen' parameter in queryFilter() https://docs.appian.com/suite/help/20.1/fnc_system_a_queryfilter.html

    You can have the query only be applied when the value is not null

    `not(isnull(local!date))`

  • 0 in reply to Danny Verb

    Hi ,

    Say the table above is a editable grid table. Can you help me how to filter data from that editable grid, I tried with the same filter which is used in read only grid but unfortunately I got and exception (below). Also, how to paginate editable grid ?

    Record data has an invalid value for “recordType”. Record Type can only be a Record Type Reference. Received: List of Variant.

  • 0
    Appian Employee
     in reply to shamima0001

    Sure. a!recordData() is a rule which can only be used within a!gridField where you help describe where your data is located along with filters. To use data from a record type in other places in an interface you'll need to query that data using a!queryRecordType() which looks very similar https://docs.appian.com/suite/help/20.3/fnc_system_queryrecordtype.html

    Paging can seem tricky but it's actually very simple, just have a separate local variable for your a!pagingInfo. In general, if you want to have paging in a grid, you should use a!gridField().

    a!localVariables(
  local!pagingInfo: a!pagingInfo(1,15),
  local!myQuery: a!queryRecordType(
    recordType: 'recordType!Company',
    pagingInfo: local!pagingInfo,
    fetchTotalCount: true
  ),
  a!gridLayout(
    label: "My Grid",
    totalCount: local!myQuery.totalCount,
    rows: a!forEach(
      local!myQuery.data,
      a!gridRowLayout(
        id: fv!index + local!myQuery.startIndex - 1,
        contents: {}
      )
    )
  ),
  a!richTextDisplayField(
    align: "RIGHT",
    labelPosition: "COLLAPSED",
    value: {
      /* Double Left Arrow */
      a!richTextIcon(
        showWhen: local!pagingInfo.startIndex <> 1,
        icon: "angle-double-left",
        size: "MEDIUM",
        linkStyle: "STANDALONE",
        link: {
          a!dynamicLink(
            saveInto: {
              a!save(
                local!pagingInfo.startIndex,
                1
              )
            }
          )
        }
      ),
      "  ",
      /*  Left Arrow  */
      a!richTextIcon(
        showWhen: local!pagingInfo.startIndex <> 1,
        icon: "angle-left",
        size: "MEDIUM",
        linkStyle: "STANDALONE",
        link: {
          a!dynamicLink(
            saveInto: {
              a!save(
                local!pagingInfo.startIndex,
                local!pagingInfo.startIndex - local!pagingInfo.batchSize
              )
            }
          )
        }
      ),
      /*  Current Page Indices  */
      a!richTextItem(
        text: local!pagingInfo.startIndex-local!hiddenItems & " - " & if(
          local!pagingInfo.startIndex-local!hiddenItems < local!totalCount-local!hiddenItems - local!pagingInfo.batchSize + 1 = false,
          local!totalCount-local!hiddenItems,
          local!pagingInfo.startIndex - 1 + local!pagingInfo.batchSize-local!hiddenItems
        ),
        style: "STRONG"
      ),
      /*  Total Count  */
      a!richTextItem(
        text: " of " & local!totalCount
      ),
      /*  Right Arrow  */
      a!richTextIcon(
        showWhen: and(
          local!pagingInfo.batchSize < local!totalCount,
          local!pagingInfo.startIndex< local!totalCount - local!pagingInfo.batchSize + 1
        ),
        icon: "angle-right",
        size: "MEDIUM",
        linkStyle: "STANDALONE",
        link: {
          a!dynamicLink(
            saveInto: {
              a!save(
                local!pagingInfo.startIndex,
                local!pagingInfo.startIndex + local!pagingInfo.batchSize
              )
            }
          )
        }
      ),
      " ",
      /*  Double Right Arrow  */
      a!richTextIcon(
        showWhen: and(
          local!pagingInfo.batchSize < local!totalCount,
          local!pagingInfo.startIndex < local!totalCount - local!pagingInfo.batchSize + 1
        ),
        icon: "angle-double-right",
        size: "MEDIUM",
        linkStyle: "STANDALONE",
        link: {
          a!dynamicLink(
            saveInto: {
              a!save(
                local!pagingInfo.startIndex,
                local!totalCount - local!pagingInfo.batchSize + 1
              ),
            }
          )
        }
      )
    }
  )
)

  • 0 in reply to Danny Verb

    Hi ,

    I guess I have not been able to explain my question, let me try again.

    Below in an editable grid table, I want to filter data instantly (search on the data already fetched from DB, don't want to query table again and again) as soon as I select any value from the filter criteria (Start Date & Status) just above the table.

    Below is my SAIL:

    a!localVariables(
  local!currentStartDate,
  local!currentStatus,
  
  local!rwmActivityData:{
    {
      status:"Complete",
      startDate:"10/16/2020",
    },
    {
      status:"In Progress",
      startDate:"10/11/2020",
    },
    {
      status:"Cancelled",
      startDate:"11/01/2020",
    },
    {
      status:"Pending",
      startDate:"10/16/2020",
    }
  },

  {
    a!columnsLayout(
      columns: {
        a!columnLayout(
          contents: {
            a!dateField(
              label: "Start Date",
              labelPosition: "ABOVE",
              value: local!currentStartDate,
              saveInto: local!currentStartDate,
              validations: {}
            )
          }
        ),
        a!columnLayout(
          contents: {
            a!multipleDropdownField(
              label: "Status",
              labelPosition: "ABOVE",
              placeholder: "Select Status",
              choiceLabels: {
                "Pending",
                "In Progress",
                "Cancelled",
                "Complete"
              },
              choiceValues: {
                "Pending",
                "In Progress",
                "Cancelled",
                "Complete"
              },
              value: local!currentStatus,
              saveInto: local!currentStatus,
              searchDisplay: "ON",
              validations: {}
            )
          }
        )
      },
    
      marginBelow: "STANDARD",
      showDividers: false
    ),

    a!columnsLayout(
      columns: {
        /**Editable Grid**/
        a!columnLayout(
          contents: {
            a!gridLayout(
              label: "",
              labelPosition: "ABOVE",
              headerCells: {
                a!gridLayoutHeaderCell(label: "Start Date"),
                a!gridLayoutHeaderCell(label: "Status")
              },
              columnConfigs: {
               
              },
              rows: a!forEach(
                items:if(
                  and(
                    rule!APN_isBlank(local!currentStartDate),
                    rule!APN_isBlank(local!currentStatus)
                  ),
                  local!rwmActivityData,
                  a!queryRecordType (
                    recordType: local!rwmActivityData,
                    filters: {
                     a!queryFilter(
                        field: fv!item.startDate,
                        operator: "=",
                        value: local!currentStartDate,
                        applyWhen: not(isnull(local!currentStartDate))
                      ),
                      a!queryFilter(
                        field: local!rwmActivityData.status,
                        operator: "in",
                        value: local!currentStatus,
                        applyWhen: not(
                          or(
                            local!currentStatus = "",
                            length(local!currentStatus) = 0
                          )
                        )
                      )
                    }
                  )
                ),
                expression: {
                  a!gridRowLayout(
                    contents: {
                      a!textField(
                        label: "Start Date",
                        labelPosition: "ABOVE",
                        value: fv!item.startDate,
                        saveInto: {},
                        refreshAfter: "UNFOCUS",
                        readOnly: true,
                        validations: {}
                      ),
                      a!textField(
                        label: "Status",
                        labelPosition: "ABOVE",
                        value: fv!item.status,
                        saveInto: {},
                        refreshAfter: "UNFOCUS",
                        readOnly: true,
                        validations: {}
                      )
                    },
                    selectionDisabled: false
                  )
                }
              ),
              selectionSaveInto: {},
              validations: {},
              shadeAlternateRows: true
            )
          }
        )
      }
    ),
  }
)

  • +1
    Certified Senior Developer
     in reply to shamima0001

    hi shamima,

    you need to update the local!filters of your queryEntity via saveInto parameter of the sail form elements.

    try to use an expresison rule with querEntity and ruleInput "any type". 

© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.