How to remove duplicate items

Do you have a few more details? Where is the data coming from? What do you mean with "remove"? What did you try?

Hi stefan ,

this is my code, as per above post , If i am taking any action in any contrct no,  only that should be visible

Not both as per pic

a!localVariables(
local!IMcaseValidation: false(),
local!selectedContractsData,
local!invoiceReviewData: cast(
typeof(
{
'type!{urn:com:appian:types}CNAM_invoiceReview'()
}
),
rule!CNAM_InvoiceReviewData(
businessPartnerNumber: ri!businessPartnerNumber
)
),
local!selectedIndexes,
a!formLayout(
label: concat(
"Invoice Review - ",
ri!businessPartnerName,
" - BP# ",
ri!businessPartnerNumber
),
contents: {
a!gridField(
labelPosition: "COLLAPSED",
data: local!invoiceReviewData,
columns: {
a!gridColumn(
label: "Contract #",
sortField: "CNTRCT_NO",
value: index(
fv!row,
"CNTRCT_NO",
null
),
align: "END"
),
a!gridColumn(
label: "SAP Balance",
sortField: "TOTAL_DUE_AMT",
value: rule!CNAM_formatAmount(
value: index(
fv!row,
"TOTAL_DUE_AMT",
null
)
),
align: "END"
),
a!gridColumn(
label: "WTS/CDH Invoice Amount",
sortField: "INVOICE_AMT",
value: index(
fv!row,
"INVOICE_AMT",
null
),
align: "END"
),
a!gridColumn(
label: "WTS/CDH Invoice Date",
sortField: "WTS_INVC_DT",
value: index(
fv!row,
"WTS_INVC_DT",
null
),
align: "END"
),
a!gridColumn(
label: "Existing Case Category",
sortField: "CASE_CATEGORY",
value: index(
fv!row,
"CASE_CATEGORY",
null
)
),
a!gridColumn(
label: "Existing Case #",
sortField: "CASE_ID",
value: index(
fv!row,
"CASE_ID",
null
),
align: "END"
),
a!gridColumn(
label: "Assigned To",
sortField: "ASSIGNEE_NM",
value: index(
fv!row,
"ASSIGNEE_NM",
null
)
),
a!gridColumn(
label: "Action Taken",
sortField: "IR_STATUS",
value: displayvalue(
index(
fv!row,
"IR_STATUS",
null
),
cons!CNAM_IR_CLASSIFICATION_VALUES,
cons!CNAM_IR_CLASSIFICATION_LABELS,
""
)
)
},
selectionRequired: true(),
selectionRequiredMessage: "Please select at-least one contract to classify",
selectable: true(),
selectionValue: local!selectedIndexes,
selectionSaveInto: {
local!selectedIndexes,
a!save(
ri!selectedContracts,
index(
local!invoiceReviewData,
local!selectedIndexes,
"CNTRCT_NO",
null
)
),
a!save(
local!selectedContractsData,
index(
local!invoiceReviewData,
local!selectedIndexes,
null
)
)
}
),
rule!CNAM_emptyTextField(),
if(
rule!APN_isEmpty(
ri!selectedContracts
),
{},
a!radioButtonField(
labelPosition: "COLLAPSED",
choiceLabels: {
cons!CNAM_IR_CLASSIFICATION_LABELS
},
choiceValues: {
cons!CNAM_IR_CLASSIFICATION_VALUES
},
required: true(),
value: ri!actionTaken,
saveInto: {
ri!actionTaken,
a!save(
local!IMcaseValidation,
if(
tostring(
ri!actionTaken
) = cons!CNAM_IR_CLASSIFICATION_VALUES[2],
contains(
toboolean(
rule!CNAM_invoiceReviewValidations(
invoiceReviewData: local!selectedContractsData
)
),
false()
),
false()
)
)
}
)
),
rule!CNAM_emptyTextField(),
a!columnsLayout(
columns: {
a!columnLayout(
contents: {
a!pickerFieldUsers(
label: "Assignee",
groupFilter: cons!CNAM_ALL_GROUPS_CONSTANT[1],
value: ri!newAssignee,
saveInto: ri!newAssignee,
maxSelections: 1,
required: true(),
showWhen: if(
tostring(
ri!actionTaken
) = cons!CNAM_IR_CLASSIFICATION_VALUES[3],
false(),
true()
)
)
}
),
rule!CNAM_emptyColumnLayout(),
rule!CNAM_emptyColumnLayout()
}
)
},
validations: a!validationMessage(
message: "Few contracts selected are not valid for Invoice Mismatch case creation, do you still want to proceed?",
showWhen: and(
tostring(
ri!actionTaken
) = cons!CNAM_IR_CLASSIFICATION_VALUES[2],
toboolean(
local!IMcaseValidation
) = true()
)
),
buttons: a!buttonLayout(
primaryButtons: {
a!buttonWidget(
label: "Classify Anyway",
value: cons!CNAM_SUBMIT_BUTTON,
saveInto: ri!buttonAction,
submit: true(),
validate: false(),
style: "PRIMARY",
showWhen: toboolean(
local!IMcaseValidation
) = true()
),
a!buttonWidget(
label: "Classify",
value: cons!CNAM_SUBMIT_BUTTON,
saveInto: ri!buttonAction,
submit: true(),
validate: true(),
style: "PRIMARY",
disabled: toboolean(
local!IMcaseValidation
) = true()
)
},
secondaryButtons: {
a!buttonWidget(
label: cons!CNAM_CANCEL_BUTTON,
value: cons!CNAM_CANCEL_BUTTON,
saveInto: ri!buttonAction,
submit: true(),
validate: false()
)
}
)
)
)

It seems like the expression "CNAM_InvoiceReviewData" is the source. Is this a DB query? If yes, modify it to only return unique values. Is this querying a DB view? Modify that to only return unique values.

yes, it is db query calling dynamic sql, 

can you help in this query

a!localVariables(
local!dynamicSQL: concat(
"SELECT * FROM
(SELECT DISTINCT COM.CNTRCT_NO, COM.TOTAL_DUE_AMT, COM.INVOICE_AMT,
CASE when CM.CASE_STATUS in ('c','cc','rc','urc') then null
WHEN CM.CASE_CATEGORY = 'ARS' THEN 'Past Due-Audit Retro'
WHEN CM.CASE_CATEGORY = 'IM' THEN 'Invoice Mismatch'
else CM.CATEGORY_VAL END AS CASE_CATEGORY,
CASE when CM.CASE_STATUS in ('c','cc','rc','urc') then null else COM.IR_STATUS
END IR_STATUS,
COM.WTS_INVC_DT, COM.ENTITY, COM.DNL, COM.EXP_DT, COM.SGMNT, COM.AMNT_DESCRPNCY_FLG,
case when CM.CASE_STATUS not in ('c','cc','rc','urc') then CM.CASE_ID else null end CASE_ID,
CASE when CM.CASE_STATUS in ('c','cc','rc','urc') then null else (CU.FIRST_NAME+' '+CU.LAST_NAME) END AS ASSIGNEE_NM,
ROW_NUMBER() OVER (PARTITION BY CM.CNTRCT_NO ORDER BY CM.CASE_ID DESC) AS RowNumber
FROM ",
rule!CNAM_getDBenvironment(),
".",
cons!CNAM_DB_SCHEMA,
".[CNAM_CONTRACT_MSTR] COM WITH (NOLOCK)
LEFT OUTER JOIN ",
rule!CNAM_getDBenvironment(),
".",
cons!CNAM_DB_SCHEMA,
".[CNAM_CASE_CNTRCT_PLCY_VW] CM WITH (NOLOCK) ON COM.CNTRCT_NO = CM.CNTRCT_NO
LEFT OUTER JOIN ",
rule!CNAM_getDBenvironment(),
".",
cons!CNAM_DB_SCHEMA,
".[CNAM_CASE_ASSIGN] CA WITH (NOLOCK) ON CM.CASE_ID = CA.CASE_ID
LEFT OUTER JOIN ",
rule!CNAM_getDBenvironment(),
".",
cons!CNAM_DB_SCHEMA,
".CNAM_USER AS CU WITH (NOLOCK) ON CA.ASSIGNED_TO = CU.LAN_ID
WHERE COM.BPARTNER_NO = '",
ri!businessPartnerNumber,
"'
AND NOT (COM.TOTAL_DUE_AMT = 0 AND COM.INVOICE_AMT = 0)) A
WHERE (RowNumber = 1 OR CASE_ID IS NULL)"
),
if(
rule!APN_isBlank(ri!businessPartnerNumber),
null,
index(
rule!CNAM_executeStoredProcedure(dynamicSQL: local!dynamicSQL),
1,
null
)
)
)

OK. Wild approach ... Sorry, I am not able to help you with this from remote.

May I ask why you are doing this? In Appian, it is a best practice to not do any kind of SQL programming. This might introduce the risk of SQL injection attacks.

Ho okay thanks,

Our seniors insisted to do thats it 

Are your seniors experienced Appian architects?

they have experience of 3 years, i am not sure

Yea, my recommendation would also be to accomplish this within Appian utilizing a!queryEntity(), and potentially a view on the DB side.  I would be interested to hear the Seniors use case for avoiding OOTB Appian functions by using this method. 

Do you have direct access to the DB?  Since you are currently utilizing this convoluted work-around of a stored procedure to execute dynamic SQL, you can take the SQL that gets generated and run it against the DB directly to tune it there.

Otherwise as mentioned here you will need to adjust your JOINs or WHERE clauses to remove values where those specific fields are blank.

Yea, my recommendation would also be to accomplish this within Appian utilizing a!queryEntity(), and potentially a view on the DB side.  I would be interested to hear the Seniors use case for avoiding OOTB Appian functions by using this method. 

Do you have direct access to the DB?  Since you are currently utilizing this convoluted work-around of a stored procedure to execute dynamic SQL, you can take the SQL that gets generated and run it against the DB directly to tune it there.

Otherwise as mentioned here you will need to adjust your JOINs or WHERE clauses to remove values where those specific fields are blank.