Fail to sort data

skumar over 10 years ago

Hi All,

We're facing while using Sail Grid. Please find problem explained below.Any help is much appreciated.

We're fetching the data using query record and that data is displayed in a Grid component.

Now when we try to sort the grid data by clicking on a column header then it fails to sort the data for that column and it remains sorted on the default sort info that we provided at the time of fetching data from query record.

For your reference please find sample code below. Many Thanks in advance.

=load(
local!pagingInfo: topaginginfo(1, 10),
with(
local!data: =queryrecord(
recordType: cons!COMPANY_AUDIT_LOG,
query: 'type!{www.appian.com/.../2009}Query'(
'selection|aggregation': 'type!{www.appian.com/.../2009}Aggregation'(
columns: {
'type!{www.appian.com/.../2009}AggregationColumn'(
field: "full_name",
alias: "label",
visible: true,
...

Discussion posts and replies are publicly visible

0 skumar over 10 years ago

... isGrouping: true
),

'type!{www.appian.com/.../2009}AggregationColumn'(
field: "no_of_company_count",
alias: "data",
visible: true,
aggregationFunction: "SUM"
)
}
),
           pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: -1,
sort: a!sortInfo(
field: "label",
ascending: true
)

)
)
),
                    a!formLayoutColumns(
                              instructions: "In this report we can view the # of companies created by the Analyst(s) during a Year / Month / Week in tabular as well as graphical view",
                              columns:{
                                        a!columnLayout(
                                                  contents: {
                                                            a!sectionLayout(
                                                                      firstColumnContents: {
                                                                                load(
                                                                                          with(
                                                                                                    local!pagingInfo: topaginginfo(1,-1),
                                                                                                    'type!{www.appian.com/.../2009}Grid'(
                                                                                                              totalCount: if(local!data.totalCount>0,local!data.totalCount,0),...
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 skumar over 10 years ago

...
                                                                                                              instructions: "Test",
                                                                                                              columns: {
                                                                                                                        if(local!data.totalCount>0,
                                                                                                                                  {
                                                                                                                                            'type!{www.appian.com/.../2009}GridTextColumn'(
                                                                                                                                                      label: "Analyst",
                                                                                                                                                      data: if(local!data.totalCount>0,index(local!data.data, "label", {}),{}),
                                                                                                                                                      field: "label"
                                                                                                                                            ),
                                                                                                                                            'type!{www.appian.com/.../2009}GridTextColumn'(
                                                                                                                                                      label: "# of Companies Created",
                                                                                                                                                      data: if(local!data.totalCount>0,index(local!data.data, "data", {}),{}),
                                                                                                                                                      field: "data"
                                                                                                                                            )
                                                                                                                                  },
                                                                                                                                  {
                                                                                                                                            'type!{www.appian.com/.../2009}GridTextColumn'(
                                                                                                                                            label: "Analyst",
                                                                                                                                            data: 0,
                                                                                                                                            field: "label"
                                                                                                                                            ),
                                                                                                                                            'type!{www.appian.com/.../2009}GridTextColumn'(
                                                                                                                                            label: "# of Companies Created",
                                                                                                                                            data: 0,
                                                                                                                                            field: ...
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 skumar over 10 years ago

... "data"
                                                                                                                                            )
                                                                                                                                  }
                                                                                                                        )
                                                                                                              },
                                                                                                              value: local!pagingInfo,
                                                                                                              saveInto: local!pagingInfo
                                                                                                    )
                                                                                          )
                                                                                )
                                                                      }
                                                            )
                                                  }
                                        )
                              }
                    )
          )
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sikhivahans over 10 years ago

Hi skumar, plz find the code below:
load(
local!pagingInfo: topaginginfo(1, 10),
with(
local!data: =queryrecord(
recordType: cons!COMPANY_AUDIT_LOG,
query: 'type!{www.appian.com/.../2009}Query'(
'selection|aggregation': 'type!{www.appian.com/.../2009}Aggregation'(
columns: {
'type!{www.appian.com/.../2009}AggregationColumn'(
field: "full_name",
alias: "label",
visible: true,
                              isGrouping: true
),

'type!{www.appian.com/.../2009}AggregationColumn'(
field: "no_of_company_count",
alias: "data",
visible: true,
aggregationFunction: "SUM"
)
}
),
           local!pagingInfo
)
),
                    a!formLayoutColumns(
                              instructions: "In this report we can view the # of companies created by the Analyst(s) during a Year / Month / Week in tabular as well as graphical view",
                              columns:{
                                        a!columnLayout(
                                                  contents: {
                                                            a!sectionLayout(
                                                                      firstColumnContents: {
                                                                                load(
                                                                                          with(
                                                                                                    local!pagingInfo: topaginginfo(1,-1),
                                                                                                    'type!{www.appian.com/.../2009}Grid'(
                                                                                                              totalCount: if(local!data.totalCount>0,local!data.totalCount,0),
                                                                                                              instructions: "Test",
                                                                                                              columns: {
                                                                                                                        if(local!data.totalCount>0,
                                                                                                                                  {
                                                                                                                                            'type!{www.appian.com/.../2009}GridTextColumn'(
                                                                                                                                                      label: "Analyst",
                                                                                                                                                      data: if(local!data.totalCount>0,index(local!data.data, "label", {}),{}),
                                                                                                                                                      field: "label"
                                                                                                                                            ),
                                                                                                                                            'type!{www.appian.com/.../2009}GridTextColumn'(
                                                                                                                                                      label: "# of Companies Created",
                                                                                                                                                      data: if(local!data.totalCount>0,index(local!data.data, "data", {}),{}),
                                                                                                                                                      field: "data"
                                                                                                                                            )
                                                                                                                                  },
                                                                                                                                  {
                                                                                                                                            'type!{www.appian.com/.../2009}GridTextColumn'(
                                                                                                                                            label: "Analyst",
                                                                                                                                            data: 0,
                                                                                                                                            field: "label"
                                                                                                                                            ),
                                                                                                                                            'type!{www.appian.com/.../2009}GridTextColumn'(
                                                                                                                                            label: "# of Companies Created",
                                                                                                                                            data: 0,
                                                                                                                                            field: "data"
                                                                                                                                            )
                                                                                                                                  }
                                                                                                                        )
                                                                                                              },
                                                                                                              value: local!pagingInfo,
                                                                                                              saveInto: local!pagingInfo
                                                                                                    )
                                                                                          )
                                                                                )
                                                                      }
                                                            )
                                                  }
                                        )
                              }
                    )
          )
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sikhivahans over 10 years ago

Also make a small change at the place you are defining the grid i.e. you are defining the local!pagingInfo again inside the with() again which is not necessary here, because you have already defined it. Also there is no need to include load() and with() once again inside the section layout. You can directly configure the grid here. As you are using the value: and saveInto: mechanisms, your local!pagingInfo will be saved with the new values and the expressions inside the with() will be evaluated with these new values. One more suggestion is to make use of Appian common objects which makes your code look clean and easier to debug.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sikhivahans over 10 years ago

Made some more changes and here is the final code:
load(
local!pagingInfo: topaginginfo(1, 10),
local!querydata:queryrecord(
recordType: cons!COMPANY_AUDIT_LOG,
query: 'type!{www.appian.com/.../2009}Query'(
'selection|aggregation': 'type!{www.appian.com/.../2009}Aggregation'(
columns: {
'type!{www.appian.com/.../2009}AggregationColumn'(
field: "full_name",
alias: "label",
visible: true,
                              isGrouping: true
),

'type!{www.appian.com/.../2009}AggregationColumn'(
field: "no_of_company_count",
alias: "data",
visible: true,
aggregationFunction: "SUM"
)
}
),
           pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: -1,
sort: a!sortInfo(
field: "label",
ascending: true
)

)
)
),
with(
local!data:todatasubset(local!querydata.data,local!pagingInfo),
                    a!formLayoutColumns(
                              instructions: "In this report we can view the # of companies created by the Analyst(s) during a Year / Month / Week in tabular as well as graphical view",
                              columns:{
                                        a!columnLayout(
                                                  contents: {
                                                            a!sectionLayout(
                                                                      firstColumnContents: {
                                                                                                    'type!{www.appian.com/.../2009}Grid'(
                                                                                                              totalCount: if(local!data.totalCount>0,local!data.totalCount,0),
                                                                                                              instructions: "Test",
                                                                                                              columns: {
                                                                                                                        if(local!data.totalCount>0,
                                                                                                                                  {
                                                                                                                                            'type!{www.appian.com/.../2009}GridTextColumn'(
                                                                                                                                                      label: "Analyst",
                                                                                                                                                      data: if(local!data.totalCount>0,index(local!data.data, "label", {}),{}),
                                                                                                                                                      field: "label"
                                                                                                                                            ),
                                                                                                                                            'type!{www.appian.com/.../2009}GridTextColumn'(
                                                                                                                                                      label: "# of Companies Created",
                                                                                                                                                      data: if(local!data.totalCount>0,index(local!data.data, "data", {}),{}),
                                                                                                                                                      field: "data"
                                                                                                                                            )
                                                                                                                                  },
                                                                                                                                  {
                                                                                                                                            'type!{www.appian.com/.../2009}GridTextColumn'(
                                                                                                                                            label: "Analyst",
                                                                                                                                            data: 0,
                                                                                                                                            field: "label"
                                                                                                                                            ),
                                                                                                                                            'type!{www.appian.com/.../2009}GridTextColumn'(
                                                                                                                                            label: "# of Companies Created",
                                                                                                                                            data: 0,
                                                                                                                                            field: "data"
                                                                                                                                            )
                                                                                                                                  }
                                                                                                                        )
                                                                                                              },
                                                                                                              value: local!pagingInfo,
                                                                                                              saveInto: local!pagingInfo
                                                                                                    )
                                                                      }
                                                            )
                                                  }
                                        )
                              }
                    )
          )
)

Let me know if you have any follow up questions
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 skumar over 10 years ago

Thanks for your suggestion but still we're facing problem.
Code which was provided by you gives following Exception: "Expression evaluation error at function 'todatasubset' [line 35]: Error evaluating function 'todatasubset' : java.lang.IllegalArgumentException: The given data type is not a record type: Dictionary (id=94)"
Can you provide us more suggestion, they will help us a lot.

Many Thanks in advance.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 anjannat over 10 years ago

Hi skumar, please find the code below. Its tested with our sample record.
load(
local!pagingInfo: topaginginfo(1,10),
local!querydata:queryrecord(
recordType: cons!COMPANY_AUDIT_LOG,
          query: 'type!{www.appian.com/.../2009}Query'(
          'selection|aggregation': 'type!{www.appian.com/.../2009}Aggregation'(
          columns: {
          'type!{www.appian.com/.../2009}AggregationColumn'(
          field: "full_name",
          alias: "label",
          visible: true,
          isGrouping: true
          ),
          'type!{www.appian.com/.../2009}AggregationColumn'(
          field: "no_of_company_count",
          alias: "data",
          visible: true,
          aggregationFunction: "SUM"
          )
          }
          ),
          pagingInfo:
          a!pagingInfo(
          startIndex: 1,
          batchSize: -1,
          sort: a!sortInfo(
          field: "label",
          ascending: true
          )
          )
          )
          ),
          with(
          local!data:todatasubset(local!querydata.data,local!pagingInfo),
a!formLayoutColumns(
          instructions: "In this report we can view the # of companies created by the Analyst(s) during a Year / Month / Week in tabular as well as graphical view",
          columns:{
          a!columnLayout(
          contents: {
          a!sectionLayout(
          firstColumnContents: {
          'type!{www.appian.com/.../2009}Grid'(
          totalCount:local!data.totalCount,
          instructions: "Test",
          columns: {
          'type!{www.appian.com/.../2009}GridTextColumn'(
          label: "Analyst",
          data: index(local!data.data, "label",0),
          field: "label"
          ),
          'type!{www.appian.com/.../2009}GridTextColumn'(
          label: "# of Companies Created",
          data: index(local!data.data, "data",0),
          field: "data"
          )
          },
          value: local!pagingInfo,
          saveInto: local!pagingInfo
          )
          }
          )
          }
          )
          }
          )
          )
          )
One more suggestion is to make use of common objects which makes your code look clean and easier to debug.
Also please make a note that there is no need to handle the null data based on the totalCount using if condition. Go through the index() function which handles both empty and non-empty dataset. Let me know if you have any follow-up questions
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 skumar over 10 years ago

Hi anjannat,

Many thanks for your suggestion.

We tried the workaround suggested by you, but still we're facing the same problem i.e. "Expression evaluation error at function 'todatasubset' [line 35]: Error evaluating function 'todatasubset' : java.lang.IllegalArgumentException: The given data type is not a record type: Dictionary (id=94)". It seems that there is some problem with sortinfo that we are providing in our expression. Can you please provide some more suggestions.

Many thanks in advance.
Sandeep
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sikhivahans over 10 years ago

Hi skumar, the code which was posted by anjannat was actually tested with our sample record and it worked perfectly. I am able to replicate the error which you are seeing right now by wantedly doing the errors. So could you finally do one thing? Post the code which you are using now and also let me know on which field you want to sort by default.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel