How to remove values in this grid?
Discussion posts and replies are publicly visible
Do you have a few more details? Where is the data coming from? What do you mean with "remove"? What did you try?
HiNikhila U Use union() function from where you are sourcing your data in the grid. Union will remove duplicates.use it like this - union (local!data,local!data)
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() ) } ) ))
Hi Tanvir,
tried union but its not working
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_STATUSEND 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 RowNumberFROM ", 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_NOLEFT OUTER JOIN ", rule!CNAM_getDBenvironment(), ".", cons!CNAM_DB_SCHEMA, ".[CNAM_CASE_ASSIGN] CA WITH (NOLOCK) ON CM.CASE_ID = CA.CASE_IDLEFT OUTER JOIN ", rule!CNAM_getDBenvironment(), ".", cons!CNAM_DB_SCHEMA, ".CNAM_USER AS CU WITH (NOLOCK) ON CA.ASSIGNED_TO = CU.LAN_IDWHERE COM.BPARTNER_NO = '", ri!businessPartnerNumber, "' AND NOT (COM.TOTAL_DUE_AMT = 0 AND COM.INVOICE_AMT = 0)) AWHERE (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