Readonly Grid

laxmipavanik0001

Certified Senior Developer

4 months ago

I have a read-only grid that displays data retrieved using the record data function. The data appears as expected. However, I need to calculate the Total Amount based on the "Amount" column in the grid, and this total should be displayed outside the grid in another card layout.

The issue is that using the record data function, I'm unable to access the data in a separate local variable to calculate the Total Amount. Although I can use the query record type function to obtain the data and calculate the total amount, this approach is not considered best practice.

How can I calculate the Total Amount while using the record data function?

Please Give me any Solution for this

Discussion posts and replies are publicly visible

Top Replies

Konduru Chaitanya 4 months ago in reply to laxmipavanik0001 +1
Certified Senior Developer

Do you have any requirement of showing this in a card specifically or is it possible to use the a!kpiField() provided the record data and the measure?
laxmipavanik0001 4 months ago in reply to Konduru Chaitanya +1
Certified Senior Developer

Hi Konduru Chaitanya , It works fine when I use the Kpifield() function, but I have to write nested if statements in the field parameter, which is not producing the expected results.

0 laxmipavanik0001
Certified Senior Developer
4 months ago in reply to Mike Schmitt

Hi Mike Schmitt ,
If we have a large amount of data in record type (over 5000 rows), querying the record type will fetch all the data into the interface, which will impact performance.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Rishabh Sethia
Certified Associate Developer
4 months ago in reply to laxmipavanik0001

In your case, the custom grid seems to be the only option to use the same data reference at multiple places.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

0 laxmipavanik0001

Certified Senior Developer

4 months ago in reply to laxmipavanik0001

Please find below code,

a!localVariables(
local!year,

local!selectedCategory,
local!requestBudgetLinelist: if(
a!isNullOrEmpty(local!selectedCategory),
{},
rule!EPR_QR_RequestBudgetLine_get(
fields: {
'recordType!{f66f4f62-183f-4e87-8731-b20bf5be78a3}EPR Request Budget Line.fields.{37a05598-b3b8-46ed-a761-0fcc5e3e4807}budgetCategoryId',
'recordType!{f66f4f62-183f-4e87-8731-b20bf5be78a3}EPR Request Budget Line.fields.{28838483-16bf-46de-a666-6aa54709fb29}requestBudgetLineId',
'recordType!{f66f4f62-183f-4e87-8731-b20bf5be78a3}EPR Request Budget Line.fields.{f0a15775-3c4d-4f30-ab8e-382dc0da7af4}name'
},
budgetCategoryId: local!selectedCategory
)
),
local!selectedSubCategory,
local!selectedCountry,
local!branchList: rule!EPR_QR_Branch_get(
fields: {
'recordType!{9097fb9b-efab-4780-b83a-4109e13adddb}EPR Branch.fields.{dd815073-9331-407e-a59d-837e1ebaeaf5}branchId',
'recordType!{9097fb9b-efab-4780-b83a-4109e13adddb}EPR Branch.fields.{25ae227f-6ad7-4365-824a-9eb5b17c23c2}branchName'
},
countryIdList: a!defaultValue(local!selectedCountry, - 1)
),
local!selectedBranch,
local!arcode,
local!selectedCurrency,
local!selectedFrom,
local!selectedTo,



local!requestData: a!recordData(

recordType: 'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request',
filters: a!queryLogicalExpression(

operator: "AND",
filters: {
a!queryFilter(
field: 'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{8de859a5-8eb3-4c2c-a310-bef11ba5c795}isActive',
operator: "=",
value: true()
),
a!queryFilter(
field: 'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{f54f4838-25c6-4095-86d3-c19504dc8c57}createdDate',
operator: "between",
value: {
datetime(
year(local!selectedFrom),
month(local!selectedFrom),
day(local!selectedFrom - 1),
- 5,
- 30,
0
),
datetime(
year(local!selectedTo),
month(local!selectedTo),
day(local!selectedTo),
- 5,
- 31,
0
)
},
applyWhen: and(
a!isNotNullOrEmpty(local!selectedFrom),
a!isNotNullOrEmpty(local!selectedTo)
)
),
/*a!queryFilter(*/
/*field: 'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{f54f4838-25c6-4095-86d3-c19504dc8c57}createdDate',*/
/*operator: "in",*/
/*value:local!data['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{f54f4838-25c6-4095-86d3-c19504dc8c57}createdDate'],*/
/*applyWhen: a!isNotNullOrEmpty(local!year)*/
/*),*/
a!queryFilter(
field: 'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.relationships.{e9a52fd3-6feb-44a0-a536-1042805c8e6b}requestBudgetLine.fields.{37a05598-b3b8-46ed-a761-0fcc5e3e4807}budgetCategoryId',
operator: "=",
value: local!selectedCategory
),
a!queryFilter(
field: 'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.relationships.{e9a52fd3-6feb-44a0-a536-1042805c8e6b}requestBudgetLine.fields.{28838483-16bf-46de-a666-6aa54709fb29}requestBudgetLineId',
operator: "=",
value: local!selectedSubCategory
),
a!queryFilter(
field: 'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.relationships.{582d839c-dc17-4d04-a5ec-5786e2bd4538}country.fields.{d9a40a5f-a42d-4539-8e0f-903514acd825}countryId',
operator: "=",
value: local!selectedCountry
),
a!queryFilter(
field: 'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.relationships.{66ddee70-135a-4dd4-b7c7-bfb941ae75ab}branch.fields.{dd815073-9331-407e-a59d-837e1ebaeaf5}branchId',
operator: "=",
value: local!selectedBranch
),
a!queryFilter(
field: 'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{de2b9ddd-2f59-4cf1-86a9-e7feff435481}requestId',
operator: "=",
value: local!arcode[1],
applyWhen: a!isNotNullOrEmpty(local!arcode)
),

},
ignoreFiltersWithEmptyValues: true()
),

),

{
a!cardLayout(
contents: {
a!columnsLayout(
columns: {
a!columnLayout(
contents: {
a!textField(
label: "Year",
value: local!year,
saveInto: { local!year },
validations: {
if(len(local!year)>4,"It should allow only 4 digits",{})
}
),

}
),
a!columnLayout(
contents: {
a!dateField(
label: "Date From",
labelPosition: "ABOVE",
value: local!selectedFrom,
saveInto: { local!selectedFrom },
validations: {}
)
}
),
a!columnLayout(
contents: {
a!dateField(
label: "Date To",
labelPosition: "ABOVE",
value: local!selectedTo,
saveInto: { local!selectedTo },
validations: {
if(
or(
a!isNullOrEmpty(local!selectedFrom),
a!isNullOrEmpty(local!selectedTo)
),
{},
if(
local!selectedFrom <= local!selectedTo,
{},
"Please Enter Valid date"
)
)
}
)
}
)
}
),
a!columnsLayout(
columns: {
a!columnLayout(
contents: {
a!pickerFieldRecords(
label: "AR Code",
recordType: 'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request',
value: local!arcode,
saveInto: { local!arcode }
)
}
),
a!columnLayout(
contents: {
a!dropdownField(
choiceLabels: 'recordType!{3b7959c6-f9e0-4ce0-a2ea-de3598487622}EPR Country.fields.{d9b15464-7fb0-435b-845f-4134d856ea68}countryName',
choiceValues: 'recordType!{3b7959c6-f9e0-4ce0-a2ea-de3598487622}EPR Country.fields.{d9a40a5f-a42d-4539-8e0f-903514acd825}countryId',
label: "Country",
labelPosition: "ABOVE",
placeholder: "--- Select a Value ---",
value: local!selectedCountry,
saveInto: {
local!selectedCountry,
a!save(local!selectedBranch, null())
},
searchDisplay: "AUTO",
validations: {},
data: 'recordType!{3b7959c6-f9e0-4ce0-a2ea-de3598487622}EPR Country'
)
}
),
a!columnLayout(
contents: {
a!dropdownField(
choiceLabels: 'recordType!{e4963960-42b8-4145-8739-4cd348862bc0}EPR Budget Category.fields.{b4305307-abe1-43cd-a9a3-3df0ca27f440}category',
choiceValues: 'recordType!{e4963960-42b8-4145-8739-4cd348862bc0}EPR Budget Category.fields.{293e54f5-0758-46bd-bdde-564a97480a91}id',
label: "Category",
labelPosition: "ABOVE",
placeholder: "--- Select a Category ---",
value: local!selectedCategory,
saveInto: {
local!selectedCategory,
a!save(local!selectedSubCategory, null())
},
searchDisplay: "AUTO",
validations: {},
data: 'recordType!{e4963960-42b8-4145-8739-4cd348862bc0}EPR Budget Category'
)
}
)
}
),
a!columnsLayout(
columns: {
a!columnLayout(
contents: {
a!dropdownField(
choiceLabels: { "LOCAL", "USD" },
choiceValues: { "LOCAL", "USD" },
label: "Currency",
placeholder: "---Select local currency---",
value: local!selectedCurrency,
saveInto: { local!selectedCurrency }
)
}
),
a!columnLayout(
contents: {
a!dropdownField(
choiceLabels: local!branchList['recordType!{9097fb9b-efab-4780-b83a-4109e13adddb}EPR Branch.fields.{25ae227f-6ad7-4365-824a-9eb5b17c23c2}branchName'],
choiceValues: local!branchList['recordType!{9097fb9b-efab-4780-b83a-4109e13adddb}EPR Branch.fields.{dd815073-9331-407e-a59d-837e1ebaeaf5}branchId'],
label: "Branch",
labelPosition: "ABOVE",
placeholder: "--- Select a Value ---",
value: local!selectedBranch,
saveInto: { local!selectedBranch },
searchDisplay: "AUTO",
showWhen: a!isNotNullOrEmpty(local!selectedCountry),
validations: {},

)
}
),
a!columnLayout(
contents: {
a!dropdownField(
choiceLabels: local!requestBudgetLinelist['recordType!{f66f4f62-183f-4e87-8731-b20bf5be78a3}EPR Request Budget Line.fields.{f0a15775-3c4d-4f30-ab8e-382dc0da7af4}name'],
choiceValues: local!requestBudgetLinelist['recordType!{f66f4f62-183f-4e87-8731-b20bf5be78a3}EPR Request Budget Line.fields.{28838483-16bf-46de-a666-6aa54709fb29}requestBudgetLineId'],
label: "Sub Category",
labelPosition: "ABOVE",
placeholder: "--- Select a Sub Category ---",
value: local!selectedSubCategory,
saveInto: { local!selectedSubCategory },
searchDisplay: "AUTO",
showWhen: if(
a!isNullOrEmpty(local!selectedCategory),
false(),
true()
),
validations: {}
)
}
)
}
)
},
height: "AUTO",
style: "TRANSPARENT",
marginBelow: "STANDARD",
showBorder: false
),
a!cardLayout(
contents: {
a!gridField(
data: local!requestData,

columns: {
a!gridColumn(
label: "Year",
value: year(
fv!row['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{f54f4838-25c6-4095-86d3-c19504dc8c57}createdDate']
)
),
a!gridColumn(
label: "AR Code",
value: fv!row['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{d5a2fc06-d4aa-4dbc-b367-a94033d73740}requestNum']
),
a!gridColumn(
label: "Country",
value:fv!row['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.relationships.{582d839c-dc17-4d04-a5ec-5786e2bd4538}country.fields.{d9b15464-7fb0-435b-845f-4134d856ea68}countryName']
),
a!gridColumn(
label: "Branch",
value: fv!row['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.relationships.{66ddee70-135a-4dd4-b7c7-bfb941ae75ab}branch.fields.{25ae227f-6ad7-4365-824a-9eb5b17c23c2}branchName']
),
a!gridColumn(
label: "Category",
value: fv!row['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.relationships.{e9a52fd3-6feb-44a0-a536-1042805c8e6b}requestBudgetLine.relationships.{f197b60f-be19-48ca-83fc-7685c2183760}budgetCategory.fields.{b4305307-abe1-43cd-a9a3-3df0ca27f440}category']

),
a!gridColumn(
label: "Sub Category",
value: fv!row['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.relationships.{e9a52fd3-6feb-44a0-a536-1042805c8e6b}requestBudgetLine.fields.{f0a15775-3c4d-4f30-ab8e-382dc0da7af4}name']


),
a!gridColumn(
label: "Subject",
value: fv!row['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{17648ffb-78ef-45bb-8e34-b8a998a67042}subject']
),
a!gridColumn(
label: "Amount",
value: if(
a!isNullOrEmpty(fv!row['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{042ab0e7-d6bd-4989-9b3d-83cfddd7f912}requestAmount']),
"",
if(
a!isNullOrEmpty(local!selectedCurrency),
{
fv!row['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{042ab0e7-d6bd-4989-9b3d-83cfddd7f912}requestAmount'],
fv!row['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{158b8d02-bea6-42fa-af6c-3a8805755ba0}requestAmountUsd']
},
if(
local!selectedCurrency = "LOCAL",
concat(
rule!EPR_CMPT_ConcatCurrencyFromCountryId(countryId: local!selectedCountry),
" ",
fv!row['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{042ab0e7-d6bd-4989-9b3d-83cfddd7f912}requestAmount']
),
concat(
rule!EPR_CMPT_ConcatCurrencyFromCountryId(countryId: local!selectedCountry),
" ",
fv!row['recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{158b8d02-bea6-42fa-af6c-3a8805755ba0}requestAmountUsd']
)
)
)
)
)
},

)
},
showBorder: false
),
a!columnsLayout(
columns: {
a!columnLayout(contents: {}),
a!columnLayout(contents: {}),
a!columnLayout(
contents: {
a!cardLayout(
contents: {
a!kpiField(
data: local!requestData,
primaryMeasure: a!measure(
function: "SUM",
field: if(local!selectedCurrency = "LOCAL",
'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{042ab0e7-d6bd-4989-9b3d-83cfddd7f912}requestAmount',
if(local!selectedCurrency = "USD",
'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{158b8d02-bea6-42fa-af6c-3a8805755ba0}requestAmountUsd',
{})
),
filters: {
a!queryFilter(
field: 'recordType!{3452d15a-2c70-4fd8-af3b-9faa21536750}EPR Request.fields.{8de859a5-8eb3-4c2c-a310-bef11ba5c795}isActive',
operator: "=",
value: true()
)
}
)
)



},
height: "AUTO",
style: "TRANSPARENT",
marginBelow: "STANDARD",
showBorder: false
)
}
)
}
),
a!buttonLayout(
primaryButtons: a!buttonWidget(
label: "Exit",
submit: true(),
style: "SOLID"
)
)
}
)

0 Mike Schmitt 4 months ago in reply to laxmipavanik0001

laxmipavanik0001 said:
querying the record type will fetch all the data into the interface

But if you're only wanting the column sum for the first page, then you would only be querying the current page of data. And if you want some calculation of a sum of the entire (5000+ row) data set, then the "current page" thing is irrelevant anyway and you should just be using a query with Aggregation parameters added in order to perform that calculation.

In no case would there be a need to query all 5000 rows of data into any interface.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Reject Answer

Cancel