Editable grid

Hi,

I am designing a page which is having editable grid containing checkboxes(property_id). These checkboxes will be either checked or unchecked based on existing data. If I make any changes to checkbox (either checked or unchecked)

status(is_active: boolean) should changes in database. My database table structure is following:

CREATE TABLE `user_property_info` (
`user_property_info_id` int(11) NOT NULL,
`user_id` int(11) DEFAULT NULL,
`property_id` int(11) DEFAULT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT '1',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Note 1: user_property_info_id is a auto increment primary key where as user_id & property_id are foreign keys

Note 2: If there are 3 properties (lets say 100, 101, 102) which are associate with user_id (222) than on page load pre-filled rule input looks like. Based on this rule input grid selections happens.

{
{
user_property_info_id : 1,
user_id : 222,
property_id: 100,
is_active: true
},
{
user_property_info_id : 2,
user_id : 222,
property_id: 101,
is_active: true
},
{
user_property_info_id : 3,
user_id : 222,
property_id: 103,
is_active: true
},
}

Case 1: If I checked new property_id (let say 109) then the above rule input should be like this (New record appends and user_property_info_id will be null)
{
{
user_property_info_id : 1,
user_id : 222,
property_id: 100,
is_active: true
},
{
user_property_info_id : 2,
user_id : 222,
property_id: 101,
is_active: true
},
{
user_property_info_id : 3,
user_id : 222,
property_id: 103,
is_active: true
},
{
user_property_info_id : null,
user_id : 222,
property_id: 103,
is_active: true
},
}

Case 2: If I unchecked existing property_id (let say 101) then the above rule input should be like this (is_active will set to false. Please see second record having property id 101)
{
{
user_property_info_id : 1,
user_id : 222,
property_id: 100,
is_active: true
},
{
user_property_info_id : 2,
user_id : 222,
property_id: 101,
is_active: false
},
{
user_property_info_id : 3,
user_id : 222,
property_id: 103,
is_active: true
},
{
user_property_info_id : null,
user_id : 222,
property_id: 103,
is_active: true
},
}


Case 3: Any new property which will be added and then removed (on same process) will not impact on rule input.


Once we get propert rule input we will pass these for further database insert / update operations. Here if user_property_info_id is null it is treated as insert operation else act as a update operations.


Thanks!

  Discussion posts and replies are publicly visible

  • Can you please mention how you are displaying the data in the grid? I didn't exactly get the issue that you are facing. The checkbox field should be something similar to below code. local!isActive should be replaced with your CDT field. 

    load(
    local!isActive:false(),
    a!checkboxField(
    choiceLabels: {""},
    choiceValues: {true()},
    value: if(or(rule!APN_isBlank(local!isActive),local!isActive=false()),"",local!isActive),
    saveInto: a!save(local!isActive,if(rule!APN_isBlank(save!value),false(),local!isActive))
    )

  • a!localVariables(
     local!existingUserPropertyMappingInfo: if(
        isnull(
          ri!userId
        ),
        null,
        a!queryEntity(
          entity: cons!OR_USER_PROPERTY_INFO,
          query: a!query(
            filter: a!queryFilter(
              field: "user_id",
              operator: "=",
              value: ri!userId
            ),
            pagingInfo: a!pagingInfo(
              startIndex: 1,
              batchSize: - 1
            )
          )
        ).data
      ),  
    
      local!existingUserPropertyIds: {
        a!forEach(
          items: local!existingUserPropertyMappingInfo,
          expression: fv!item.property_id
        )
      },
      local!gridSelection: a!gridSelection(
        selected: local!existingUserPropertyIds,
        pagingInfo: a!pagingInfo(
          startIndex: 1,
          batchSize: - 1,
          sort: a!sortInfo(
            field: "property_name",
            ascending: true
          )
        )
      ),
      local!properties: a!queryEntity(
        entity: cons!OR_PROPERTY_INFO,
        query: a!query(
          pagingInfo: a!pagingInfo(
            startIndex: 1,
            batchSize: - 1,
            sort: a!sortInfo(
              field: "property_name",
              ascending: true
            )
          )
        ),
        fetchTotalCount: true
      ),
      
      
      a!sectionLayout(
        contents: {
          a!columnsLayout(
            columns: {
              a!columnLayout(
                contents: {
                  a!richTextDisplayField(
                    label: "",
                    labelPosition: "COLLAPSED",
                    value: {
                      a!richTextHeader(
                        text: "Select Properties"
                      )
                    }
                  ),
                  a!gridField(
                    label: "Select Properties",
                    labelPosition: "COLLAPSED",
                    totalCount: local!properties.totalCount,
                    columns: {
                      a!gridTextColumn(
                        label: "Property Name",
                        field: "property_name",
                        data: index(
                          local!properties.data,
                          "property_name",
                          {}
                        )
                      ),
                      a!gridTextColumn(
                        label: "Address",
                        field: "address_line1",
                        data: index(
                          local!properties.data,
                          "address_line1",
                          {}
                        )
                      )
                    },
                    identifiers: local!properties.identifiers,
                    value: local!gridSelection,
                    saveInto: {
                      local!gridSelection,
                      a!save(
                        target: ri!userPropertyInfo,
                        value: if(
                          length(
                            local!gridSelection.selected
                          ) = 0,
                          null,
                          a!forEach(
                            items: local!gridSelection.selected,
                            expression: {
                              property_id: fv!item,
                              is_active: true(),
                              user_property_status: true()
                            }
                          )
                        )
                      )
                    },
                    selection: true,
                    selectionStyle: "CHECKBOX",
                    shadeAlternateRows: true,
                    rowHeader: 1
                  ),
                  a!textField(
                    label: "Selected Property IDs",
                    value: if(
                      length(
                        local!gridSelection.selected
                      ) = 0,
                      "No Property selected",
                      joinarray(
                        local!gridSelection.selected,
                        ", "
                      )
                    ),
                    readOnly: true
                  )
                }
              )
            }
          ),
          a!columnsLayout(
            columns: {
              a!columnLayout(
                contents: {
                  a!buttonArrayLayout(
                    buttons: {
                      a!buttonWidget(
                        label: "Cancel",
                        saveInto: {
                          a!save(
                            target: ri!actionEvent,
                            value: "listUsers"
                          ),
                          a!save(
                            target: ri!userId,
                            value: null
                          ),
                          a!save(
                            target: ri!userDetails,
                            value: null
                          )
                        },
                        style: "NORMAL"
                      ),
                      a!buttonWidget(
                        label: "Save",
                        submit: true,
                        style: "PRIMARY",
                        disabled: false/*,
                        saveInto: a!save(
                          target: ri!userPropertyInfo,
                          value: a!forEach(
                            items: local!existingUserPropertyMappingInfo,
                            expression: 
                              a!foreach(
                                items: ri!userPropertyInfo
                                expression:
                                if()
                              )
                            
                          )
                        )*/
                      )
                    },
                    align: "END"
                  )
                }
              )
            }
          )
        }
      )
    )

  • 0
    Certified Lead Developer
    in reply to husaina0001

    There are a couple confusing things here.

    1) in your top post you say you're making an editable grid, but in your sample code you're using a!gridField (read-only aka paging grid) with row-selection enabled.

    2) you're using a!localVariables which suggests you're on 19.2, but the setup for your paging grid is the pre-19.2 version - can you confirm which Appian version you're currently on?

  • 0
    Certified Lead Developer
    in reply to husaina0001

    Regardless of my questions above, my best guess based on your code as posted is that you need to do something more careful in the SaveInto in your grid.  I haven't tested it but I'm guessing the following revised code sample is close:

    saveInto: {
      local!gridSelection,
      
      /* now, iterate through each property and update only ones that are changed */
      a!forEach(
        local!properties.data,
        if(
          contains(
            local!gridSelection.selected,
            fv!item.property_id
          ),
          /* adjust any currently-selected entries as needed */
          if(
            fv!item.is_active,
            {},
            a!save(
              fv!item.is_active,
              true()
            )
          ),
          /* adjust any not-currently-selected entries as needed */
          if(
            fv!item.is_active,
            a!save(
              fv!item.is_active,
              false()
            )
          )
        )
      )
      
      /* a!save( */
        /* target: ri!userPropertyInfo, */
        /* value: if( */
          /* length( */
            /* local!gridSelection.selected */
          /* ) = 0, */
          /* null, */
          /* a!forEach( */
            /* items: local!gridSelection.selected, */
            /* expression: { */
              /* property_id: fv!item, */
              /* is_active: true(), */
              /* user_property_status: true() */
            /* } */
          /* ) */
        /* ) */
      /* ) */
    }

  • I am sorry, I got consfused. In this page we are not using editable grid but prior to this page we are. In this page we are updating user basic details (first name , last name etc) and associate user with selected properties. And we are using 19.2 version.

  • 0
    Certified Lead Developer
    in reply to husaina0001

    Ok - FYI, i believe in 19.2 your configuration for a!gridField won't work (unless you change the rule name to a!gridField_19r1 to use the deprecated version).  The new version of a!gridField uses somewhat different configuration parameters and uses a!gridColumn() for its columns, and will not work with a!gridTextColumn, a!gridImageColumn or any of the other legacy ones.