06-07-2023 11:46 AM
Hello,
I'm trying to create a confirmation rule that looks if these two accounts have a difference of 10k USD or more and if so it looks to see if it has a comment (annotation) made by our user and if it doesn't it will fail. So far I got the first part to see if the value is over 10k, but checking it to see if there's a comment also is confusing me.
args.ConfirmationRuleArgs.DisplayValue = math.Round(api.Data.GetDataCell("A#Bad_Debt_Comp:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD").cellAmount, 2)
If System.Math.Abs(args.ConfirmationRuleArgs.DisplayValue) > 10000 Then
args.ConfirmationRuleArgs.Info1 = "Bad Debt USD = " & String.Format("{0:#,##0}",math.Round(api.Data.GetDataCell("A#0319:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD").cellAmount, 0))
args.ConfirmationRuleArgs.Info2 = "CCM Bad Debt USD = " & String.Format("{0:#,##0}",math.Round(api.Data.GetDataCell("A#CCMBadDebt:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD.cellAmount, 0))
'args.ConfirmationRuleArgs.Info3 = "Out of BALANCE Check = " & String.Format("{0:#,##0}",math.Round(api.Data.GetDataCell("A#BALANCE:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD").cellAmount, 0))
Return True
Else
args.ConfirmationRuleArgs.Info1 = "Bad Debt USD = " & String.Format("{0:#,##0}",math.Round(api.Data.GetDataCell("A#0319:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD").cellAmount, 0))
args.ConfirmationRuleArgs.Info2 = "CCM Bad Debt USD = " & String.Format("{0:#,##0}",math.Round(api.Data.GetDataCell("A#CCMBadDebt:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD).cellAmount, 0))
'args.ConfirmationRuleArgs.Info3 = "Out of BALANCE Check = " & String.Format("{0:#,##0}",math.Round(api.Data.GetDataCell("A#BALANCE:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#LOAD:F#TopFlow:O#Top:I#Top:C#USD").cellAmount, 0))
Return False
End If
Would I just add another add an another section and say:
args.ConfirmationRuleArgs.DisplayValue = api.Data.GetDataCell("A#0319:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD:V#Annotation").cellAmount
If System.Math.Abs(args.ConfirmationRuleArgs.DisplayValue) = " " Then
I'm a bit new to writing confirmation rules, so this is a bit out of my wheel house. I did download a Confirmation Rules PPT from a 2016 OS Conference which was helpful, but still stuck.
Thanks
Will
Solved! Go to Solution.
06-07-2023 04:02 PM
Hi @WillVitale ,
This will return the cell text Annotation - update with your respective POV:
stringAnnot = api.Data.GetDataCellEx("Cb#GolfStream:E#" + WFEntity + ":C#USD:V#Annotation:A#Account:F#Flow:O#Forms:I#None:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None").DataCellAnnotation
You can then do
If ResultCellEx.DataCellAnnotation.Length < 1 Then
06-07-2023 04:02 PM
Hi @WillVitale ,
This will return the cell text Annotation - update with your respective POV:
stringAnnot = api.Data.GetDataCellEx("Cb#GolfStream:E#" + WFEntity + ":C#USD:V#Annotation:A#Account:F#Flow:O#Forms:I#None:U1#None:U2#None:U3#None:U4#None:U5#None:U6#None:U7#None:U8#None").DataCellAnnotation
You can then do
If ResultCellEx.DataCellAnnotation.Length < 1 Then
06-07-2023 04:40 PM
Just to make sure. Can I added to the original code I have above? I need it be both if the amount is greater than 10k usd and has no annotation to cause an error.
06-07-2023 04:49 PM
I would re-write it so you have a set of condition statements that will advise the user on the range and the Annotation status. You can combine the condition statements it would be something like this:
If (System.Math.Abs(args.ConfirmationRuleArgs.DisplayValue) > 10000) AND (ResultCellEx.DataCellAnnotation.Length < 1) Then
'Range greater than 10,000 And No Annotation entered
06-07-2023 05:28 PM
So something like this? Sorry, very new to OS, sorry for asking lots of questions.
args.ConfirmationRuleArgs.DisplayValue = math.Round(api.Data.GetDataCell("A#Bad_Debt_Comp:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD").cellAmount, 2)
If System.Math.Abs(args.ConfirmationRuleArgs.DisplayValue) > 10000 AND stringAnnot = api.Data.GetDataCellEx("E#" + WFEntity + ":C#USD:V#Annotation:A#0319:F#Flow:O#Top:I#Top:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:U8#Top8").DataCellAnnotation(ResultCellEx.DataCellAnnotation.Length < 1) Then
'Range greater than 10,000 And No Annotation entered
args.ConfirmationRuleArgs.Info1 = "Bad Debt USD = " & String.Format("{0:#,##0}",math.Round(api.Data.GetDataCell("A#0319:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD").cellAmount, 0))
args.ConfirmationRuleArgs.Info2 = "CCM Bad Debt USD = " & String.Format("{0:#,##0}",math.Round(api.Data.GetDataCell("A#CCMBadDebt:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD.cellAmount, 0))
'args.ConfirmationRuleArgs.Info3 = "Out of BALANCE Check = " & String.Format("{0:#,##0}",math.Round(api.Data.GetDataCell("A#BALANCE:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD").cellAmount, 0))
Return True
Else
args.ConfirmationRuleArgs.Info1 = "Bad Debt USD = " & String.Format("{0:#,##0}",math.Round(api.Data.GetDataCell("A#0319:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD").cellAmount, 0))
args.ConfirmationRuleArgs.Info2 = "CCM Bad Debt USD = " & String.Format("{0:#,##0}",math.Round(api.Data.GetDataCell("A#CCMBadDebt:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#USGAAP:F#TopFlow:O#Top:I#Top:C#USD).cellAmount, 0))
'args.ConfirmationRuleArgs.Info3 = "Out of BALANCE Check = " & String.Format("{0:#,##0}",math.Round(api.Data.GetDataCell("A#BALANCE:U1#Top1:U2#Top2:U3#Top3:U4#Top4:U5#Top5:U6#Top6:U7#LOAD:F#TopFlow:O#Top:I#Top:C#USD").cellAmount, 0))
Return False
End If
06-07-2023 05:40 PM
Take a step back and organize your variables and related commentary collection into variables:
Dim Bad_Debt_Amt as decimal = .CellAmount()
Dim Bad_Debt_Comment as String = .DataCellAnnotation()
Dim CCMBad_Debt_Amt as decimal = .CellAmount()
Dim CCMBad_Debt_Comment as String = .DataCellAnnotation()
Get all of your variables you need defined and the values (with appropriate POV). That will simplify the condition statements and your Info1-4 messaging as appropriate. The Administrator that will be responsible for support and maintenance, who comes into the application after you leave will be thankful! What you have going is hard to read and hard to follow.
You are on the right path!
06-07-2023 04:17 PM
Thanks @MikeG for the help!