Readonly Grid

Do you want to do a total of all rows available in table or page wise total?

i think you can still have local variable that will be just sum of index of property. Try this Sum(index(yourecordtype, recordtype.fileds.fieldname)).

I would avoid query db again as this may have negative impact on page performance.

Hi Rishabh Sethia ,
Amount column is there in grid, I want to do sum of amounts which are present in my grid 
and display the total amount field in rich text field.

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?

Hi Furman ,
I have tried but the data is coming in grid by using Record data,
so I need to take sum from amount which is in grid

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.

Could you please share the code where you have tried to use KPI and the output

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"
)
)
}
)

laxmipavanik0001 said:

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.

According to whom?