Hi ,
I need some calculation logic in appain that gives difference of ration numbers example shown below in the screenshot.
Can someone help me to write the logic for this on priority?
Here actual and threshold values are user inputs based on those inputs variance col data to be shown .
Discussion posts and replies are publicly visible
You can create rule inputs for ratio1 and ratio2.
a!localVariables( local!ratio1: "3:1", local!ratio2: "2:1", local!ratio1Array: split(local!ratio1, ":"), local!ratio2Array: split(local!ratio2, ":"), local!lcm: lcm( local!ratio1Array[2], local!ratio2Array[2] ), concat( ( local!ratio1Array[1] * (local!lcm / local!ratio1Array[2]) ) - ( local!ratio2Array[1] * (local!lcm / local!ratio2Array[2]) ), "/", local!lcm ) )
A more refined code for handling when the number is completely divisible.
a!localVariables( local!ratio1: "3:4", local!ratio2: "5:6", local!ratio1Array: split(local!ratio1, ":"), local!ratio2Array: split(local!ratio2, ":"), local!lcm: lcm( local!ratio1Array[2], local!ratio2Array[2] ), local!numerator: ( local!ratio1Array[1] * (local!lcm / local!ratio1Array[2]) ) - ( local!ratio2Array[1] * (local!lcm / local!ratio2Array[2]) ), if( mod(local!numerator, local!lcm) = 0, local!numerator / local!lcm, concat(local!numerator, "/", local!lcm) ) )
Thanks it really helped.
Cool! If this worked, please verify the answers to close this thread.
I have one more question,The question related to grid. is it possible to
possible to?
JS0001 said:rid. is it possible to
is it possible to show entire grid row of data with these cases? if not is it possible to show for variance field(it a textfield)?
You can use the 'backgroundColor' in gridColumn to change the background color.
Something like this.
a!gridField( data: { { actual: 6, convenant: 3, variance: 3 }, { actual: 12, convenant: 6, variance: 6 } }, columns: { a!gridColumn( label: "Actual", value: fv!row.actual, backgroundColor: a!match( value:fv!row.variance, whenTrue: (fv!row.actual*0.5)<(fv!value*0.5), then: "WARN", whenTrue: fv!value<=0, then: "ERROR", whenTrue: fv!value>0, then: "SUCCESS", default: "NONE" ) ), a!gridColumn( label: "convenant", value: fv!row.convenant, backgroundColor: a!match( value:fv!row.variance, whenTrue: (fv!row.actual*0.5)<(fv!value*0.5), then: "WARN", whenTrue: fv!value<=0, then: "ERROR", whenTrue: fv!value>0, then: "SUCCESS", default: "NONE" ) ), a!gridColumn( label: "variance", value: fv!row.variance, backgroundColor: a!match( value:fv!row.variance, whenTrue: (fv!row.actual*0.5)<(fv!value*0.5), then: "WARN", whenTrue: fv!value<=0, then: "ERROR", whenTrue: fv!value>0, then: "SUCCESS", default: "NONE" ) ) } )
but i have gridRowlayout with add multiple rows and I dont see backgroundcolour in 22.4 version of appian which i am using now,Could you please suggest for this code and it would be more useful.
{ a!gridLayout( label: cons!CR_APP_LAB_FINANCIAL_COVENANTS, labelPosition: "COLLAPSED", spacing: "DENSE", headerCells: { a!forEach( items: { "", cons!CR_APP_TXT_FINANCIAL_COVENANTS_GRID_FIELDS, "" }, expression: if( fv!item = cons!CR_APP_TXT_FINANCIAL_COVENANTS_GRID_FIELDS[9], a!gridLayoutHeaderCell( align: "LEFT", label: fv!item, helpTooltip: cons!CR_APP_SC_PRECEDENT_SUBSEQUENT_LAB_VAL[1] & " / " & cons!CR_APP_SC_PRECEDENT_SUBSEQUENT_LAB_VAL[2] ), a!gridLayoutHeaderCell( align: if( contains( { cons!CR_APP_TXT_FINANCIAL_COVENANTS_GRID_FIELDS[1], cons!CR_APP_TXT_FINANCIAL_COVENANTS_GRID_FIELDS[3], cons!CR_APP_TXT_FINANCIAL_COVENANTS_GRID_FIELDS[4], cons!CR_APP_TXT_FINANCIAL_COVENANTS_GRID_FIELDS[5], cons!CR_APP_TXT_FINANCIAL_COVENANTS_GRID_FIELDS[6], cons!CR_APP_TXT_FINANCIAL_COVENANTS_GRID_FIELDS[7] }, fv!item ), "RIGHT", "LEFT" ), helpTooltip: if( fv!item = cons!CR_APP_TXT_FINANCIAL_COVENANTS_GRID_FIELDS[16], "Compliant Marked by Credit", null ), label: fv!item ) ) ) }, columnConfigs: { a!gridLayoutColumnConfig(width: "ICON"), a!gridLayoutColumnConfig(width: "NARROW"), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: ""), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: ""), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: ""), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: ""), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: ""), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: ""), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 1), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: ""), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: ""), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: ""), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: ""), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: ""), a!gridLayoutColumnConfig(width: "NARROW"), a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: ""), a!gridLayoutColumnConfig(width: "NARROW"), a!gridLayoutColumnConfig(width: "ICON") }, rows: { a!forEach( items: ri!crAppFinCov_cdt, expression: a!gridRowLayout( id: fv!item.id_int, contents: { rule!CR_APP_RT_displayLegacyStatusIcon( rowItem_cdt: fv!item, showWhen_bool: false() ), a!integerField( value: fv!index, readOnly: true, align: "RIGHT" ), a!dropdownField_20r2( required: true, choiceLabels: rule!APN_distinct( touniformstring( index(ri!crRefFinCov_cdt, "finCov_txt", {}) ) ), choiceValues: rule!APN_distinct( touniformstring(index(ri!crRefFinCov_cdt, "id_int", {})) ), value: tostring(index(fv!item, "refFinCovRefId_int", {})), saveInto: { fv!item.refFinCovRefId_int, a!save( fv!item.finCov_txt, displayvalue( tostring(fv!item.refFinCovRefId_int), touniformstring(index(ri!crRefFinCov_cdt, "id_int", {})), index(ri!crRefFinCov_cdt, "finCov_txt", {}), {} ) ) }, placeholderLabel: cons!CR_APP_TXT_PLACEHOLDER_VALUE, disabled: if( fv!item.isPartiallyLocked_int <> 0, true, false ), validationGroup: if( ri!isEditable_bool, cons!CR_APP_TXT_EDIT_FIN_COV, cons!APN_TXT_VALIDATE_AND_SUBMIT ), ), a!textField( value: index(fv!item, "existingThreshold_txt", {}), saveInto: { fv!item.existingThreshold_txt, a!save( fv!item.existingThreshold_txt, rule!CR_APP_SC_formatThresholdValue( displayvalue( fv!item.finCov_txt, ri!crRefFinCov_cdt.finCov_txt, ri!crRefFinCov_cdt.valueType_txt, "" ), fv!item.existingThreshold_txt ) ) }, required: ri!isFinCovReq_bool, validationGroup: if( ri!isEditable_bool, cons!CR_APP_TXT_EDIT_FIN_COV, cons!APN_TXT_VALIDATE_AND_SUBMIT ), disabled: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ), align: "RIGHT", validations: rule!CR_APP_SC_getCovenantsValidations( valueType_txt: displayvalue( fv!item.finCov_txt, ri!crRefFinCov_cdt.finCov_txt, ri!crRefFinCov_cdt.valueType_txt, "" ), inputValue_txt: fv!item.existingThreshold_txt ), placeholder: rule!CR_APP_DSC_displayFinCovPlaceholder( finCovType_txt: displayvalue( fv!item.finCov_txt, ri!crRefFinCov_cdt.finCov_txt, ri!crRefFinCov_cdt.valueType_txt, "" ) ) ), a!textField( value: index(fv!item, "proposedThreshold_txt", {}), saveInto: { fv!item.proposedThreshold_txt, a!save( fv!item.proposedThreshold_txt, rule!CR_APP_SC_formatThresholdValue( displayvalue( fv!item.finCov_txt, ri!crRefFinCov_cdt.finCov_txt, ri!crRefFinCov_cdt.valueType_txt, "" ), fv!item.proposedThreshold_txt ) ) }, required: ri!isFinCovReq_bool, validationGroup: if( ri!isEditable_bool, cons!CR_APP_TXT_EDIT_FIN_COV, cons!APN_TXT_VALIDATE_AND_SUBMIT ), disabled: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ), align: "RIGHT", validations: rule!CR_APP_SC_getCovenantsValidations( valueType_txt: displayvalue( fv!item.finCov_txt, ri!crRefFinCov_cdt.finCov_txt, ri!crRefFinCov_cdt.valueType_txt, "" ), inputValue_txt: fv!item.proposedThreshold_txt ), placeholder: rule!CR_APP_DSC_displayFinCovPlaceholder( finCovType_txt: displayvalue( fv!item.finCov_txt, ri!crRefFinCov_cdt.finCov_txt, ri!crRefFinCov_cdt.valueType_txt, "" ) ) ), if( fv!item.finCov_txt = "Other", a!integerField( value: index(fv!item, "actualThreshold_txt", {}), saveInto: fv!item.actualThreshold_txt, disabled: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ) ), a!textField( value: index(fv!item, "actualThreshold_txt", {}), saveInto: { fv!item.actualThreshold_txt, a!save( fv!item.actualThreshold_txt, rule!CR_APP_SC_formatThresholdValue( displayvalue( fv!item.finCov_txt, ri!crRefFinCov_cdt.finCov_txt, ri!crRefFinCov_cdt.valueType_txt, "" ), fv!item.actualThreshold_txt ) ) }, validationGroup: if( ri!isEditable_bool, cons!CR_APP_TXT_EDIT_FIN_COV, cons!APN_TXT_VALIDATE_AND_SUBMIT ), required: ri!isFinCovReq_bool, disabled: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ), align: "RIGHT", validations: rule!CR_APP_SC_getCovenantsValidations( valueType_txt: displayvalue( fv!item.finCov_txt, ri!crRefFinCov_cdt.finCov_txt, ri!crRefFinCov_cdt.valueType_txt, "" ), inputValue_txt: fv!item.actualThreshold_txt ), placeholder: rule!CR_APP_DSC_displayFinCovPlaceholder( finCovType_txt: displayvalue( fv!item.finCov_txt, ri!crRefFinCov_cdt.finCov_txt, ri!crRefFinCov_cdt.valueType_txt, "" ) ) ) ), if( fv!item.finCov_txt = "Other", a!integerField( value: index(fv!item, "covenentThreshold_txt", {}), saveInto: fv!item.covenentThreshold_txt, readOnly: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ) ), a!textField( value: index(fv!item, "covenentThreshold_txt", {}), saveInto: fv!item.covenentThreshold_txt, readOnly: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ) ) ), a!textField( value: if( fv!item.finCov_txt = "Other", { tointeger(fv!item.actualThreshold_txt) - tointeger(fv!item.covenentThreshold_txt) }, {} ), saveInto: fv!item.varience_txt, disabled: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ) ), a!dateField( value: index(fv!item, "financialDate_dt", {}), saveInto: fv!item.financialDate_dt, required: ri!isFinCovReq_bool, validationGroup: if( ri!isEditable_bool, cons!CR_APP_TXT_EDIT_FIN_COV, cons!APN_TXT_VALIDATE_AND_SUBMIT ), readOnly: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ), align: "LEFT" ), a!dropdownField_20r2( choiceLabels: cons!CR_APP_SC_PRECEDENT_SUBSEQUENT_LAB_VAL, choiceValues: cons!CR_APP_SC_PRECEDENT_SUBSEQUENT_LAB_VAL, value: index(fv!item, "conditionFlag_txt", {}), saveInto: fv!item.conditionFlag_txt, placeholderLabel: cons!CR_APP_TXT_PLACEHOLDER_VALUE, disabled: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ) ), a!dropdownField_20r2( choiceLabels: cons!CR_APP_SC_BESTEFFORTS_COMPULSORY_LAB_VAL, choiceValues: cons!CR_APP_SC_BESTEFFORTS_COMPULSORY_LAB_VAL, value: index(fv!item, "requirementFlag_txt", {}), saveInto: fv!item.requirementFlag_txt, placeholderLabel: cons!CR_APP_TXT_PLACEHOLDER_VALUE, disabled: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ) ), a!checkboxField( value: fv!item.folConventionalFlag_int, saveInto: fv!item.folConventionalFlag_int, choiceLabels: cons!CR_APP_TXT_PROJECT_TYPES_VALUE[1], choiceValues: cons!CR_APP_INT_CHOICE_VALUES[1], choiceLayout: "COMPACT", disabled: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ), required: ri!isFinCovReq_bool, validationGroup: if( ri!isEditable_bool, cons!CR_APP_TXT_EDIT_FIN_COV, cons!APN_TXT_VALIDATE_AND_SUBMIT ), align: "LEFT" ), a!checkboxField( value: fv!item.folIslamicFlag_int, saveInto: fv!item.folIslamicFlag_int, choiceLabels: cons!CR_APP_TXT_PROJECT_TYPES_VALUE[2], choiceValues: cons!CR_APP_INT_CHOICE_VALUES[1], choiceLayout: "COMPACT", disabled: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ), required: ri!isFinCovReq_bool, validationGroup: if( ri!isEditable_bool, cons!CR_APP_TXT_EDIT_FIN_COV, cons!APN_TXT_VALIDATE_AND_SUBMIT ), align: "LEFT" ), a!textField( value: index(fv!item, "status_txt", {}), saveInto: fv!item.status_txt, readOnly: true, align: "LEFT" ), a!dateField( readOnly: rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ), value: index(fv!item, "deferralDate_dt", {}), saveInto: fv!item.deferralDate_dt ), a!textField( value: rule!APN_FN_getDisplayName(userId: index(fv!item, "owner_txt", {})), saveInto: fv!item.owner_txt, readOnly: true, align: "LEFT" ), a!radioButtonField( choiceLayout: "COMPACT", value: index(fv!item, "compliantFlag_txt", {}), saveInto: fv!item.compliantFlag_txt, choiceLabels: cons!CR_APP_TXT_CHOICE_LABELS_COMPLAINT_BREACH, choiceValues: cons!CR_APP_TXT_CHOICE_LABELS_COMPLAINT_BREACH, required: rule!CR_APP_FN_requiredConditionsForCompliantFlag( isRequired_bool: ri!isFinCovReq_bool, role_txt: rule!CR_APP_FN_getUserRole(inputUser_txt: loggedInUser()) ), validationGroup: if( ri!isEditable_bool, cons!CR_APP_TXT_EDIT_FIN_COV, cons!APN_TXT_VALIDATE_AND_SUBMIT ), disabled: rule!CR_APP_FN_conditionsToEnableCompliantFlag( sourceName_txt: cons!CR_APP_TXT_CHILD_SOURCE_TYPES[4], crAppSelectedItem_cdt: fv!item, isEditable_bool: ri!isEditable_bool, role_txt: rule!CR_APP_FN_getUserRole(inputUser_txt: loggedInUser()) ), ), a!richTextDisplayField( value: a!richTextIcon( showWhen: if( rule!APN_isBlank(ri!isEditable_bool), false, ri!isEditable_bool ), icon: "close", altText: "delete " & fv!index, caption: if( or( fv!item.isPartiallyLocked_int = 1, fv!item.isPartiallyLocked_int = 0 ), "Remove", "" ), link: a!dynamicLink( showWhen: if( rule!CR_APP_FN_freezeRecords( crAppSelectedItem_cdt: fv!item, isDisplayed_bool: ri!isEditable_bool ), false(), or( fv!item.isPartiallyLocked_int = 1, fv!item.isPartiallyLocked_int = 0 ) ), value: fv!index, saveInto: { if( rule!APN_isBlank(fv!item.id_int), {}, { a!save( ri!crAppFinCovDeleted_cdt, append(ri!crAppFinCovDeleted_cdt, fv!item) ) } ), a!save( ri!crAppFinCov_cdt, remove(ri!crAppFinCov_cdt, fv!index) ) } ), linkStyle: "STANDALONE", color: if( or( fv!item.isPartiallyLocked_int = 1, fv!item.isPartiallyLocked_int = 0 ), "NEGATIVE", "SECONDARY" ) ) ) } ) ) }, addRowLink: a!dynamicLink( showWhen: if( rule!APN_isBlank(ri!isEditable_bool), false, ri!isEditable_bool ), label: cons!CR_APP_TXT_ADD_FINANCIAL_COVENANTS, value: { addedOn_dt: now(), addedBy_txt: loggedInUser(), addedByRole_txt: rule!CR_FN_getUserRole(inputUser_txt: loggedInUser()), requestId_int: cons!CR_APP_DEFAULT_REQUEST_ID, cif_int: ri!cif_int, rowStatus_txt: cons!CR_APP_TXT_AUDIT_STATUS[1], folIslamicFlag_int: 1, folConventionalFlag_int: 1, isLocked_int: 0, isPartiallyLocked_int: 0, legacyStatus_txt: cons!CR_APP_TXT_AUDIT_STATUS[1], detailsFlag_int: 0, compliantFlag_txt: cons!CR_APP_TXT_CHOICE_LABELS_COMPLAINT_BREACH[2], conditionFlag_txt: cons!CR_APP_SC_PRECEDENT_SUBSEQUENT_LAB_VAL[1], requirementFlag_txt: cons!CR_APP_SC_BESTEFFORTS_COMPULSORY_LAB_VAL[2] }, saveInto: a!save( ri!crAppFinCov_cdt, append(ri!crAppFinCov_cdt, save!value) ) ), selectable: ri!isSelectable_bool, selectionStyle: "ROW_HIGHLIGHT", selectionValue: ri!selectedId_int, selectionSaveInto: { a!save(ri!childButtonAction_txt, null), /*Variable to set details section readonly , applicable for individual sections */ a!save(ri!isDetailsReadOnly_bool, true), a!save( ri!selectedId_int, index(reverse(save!value), 1, {}) ), a!save( ri!crAppFinCovSelected_cdt, index( ri!crAppFinCov_cdt, wherecontains( ri!selectedId_int, ri!crAppFinCov_cdt.id_int ), {} ) ), a!save( ri!crRefFinCovDetails_cdt, rule!CR_APP_QE_getRefFinCovDetailsByFilters( refFinCovRefId_int: index( ri!crAppFinCov_cdt, "refFinCovRefId_int", {} ) ) ), a!save(ri!crRefFinCovDetailsSelected_cdt, {}), a!save(ri!crAppFinCovDetailsSelected_cdt, {}), a!save( ri!crRefFinCovDetailsSelected_cdt, index( ri!crRefFinCovDetails_cdt, wherecontains( touniformstring( ri!crAppFinCovSelected_cdt.refFinCovRefId_int ), touniformstring( ri!crRefFinCovDetails_cdt.refFinCovRefId_int ) ), {} ) ), a!save( ri!crAppFinCovDetailsSelected_cdt, index( ri!crAppFinCovDetails_cdt, wherecontains( touniformstring(ri!crAppFinCovSelected_cdt.id_int), touniformstring( ri!crAppFinCovDetails_cdt.appFinCovRowId_int ) ), repeat( count(ri!crRefFinCovDetailsSelected_cdt), 'type!{urn:com:appian:types:NBF}CR_APP_FinCovDetails'() ) ) ) }, selectionDisabled: or( ri!isEditable_bool, ri!buttonAction_txt = cons!CR_APP_TXT_EDIT_FIN_COV_DETAILS ), validations: {}, shadeAlternateRows: true ) }
You can add richTextDisplayFields in your gridRowLayout and then add color to it using the same logic.
I dont see parameter called background colour in 22.4 version of appian and also this is my code could you suggest for this please if possible.
Now this image has positive and negativa value for variance column,So variance has to be colured green for positive value and red for negative value.Lets consider variance col is richtextdisplayfield