Forum Discussion

WillVitale's avatar
WillVitale
Contributor II
19 hours ago
Solved

If Cell Amount is Blank Confirmation Rule

Hello,

I'm writing a confirmation rule that is a specific few cells are blank or if these cells are not 0 that the confirmation rule passes.

I got the 2nd part to work fine, but when the cells are blank I'm having an issue. Can someone help me with my code and what I might be missing or need to change?

Dim EntityName As String = api.Pov.Entity.Name.ToString
Dim WFYear As Integer = api.Time.GetYearFromId(api.Pov.Time.MemberId)

    ' Retrieve amounts for each account
    Dim AccountPPEICSaleAmount As Decimal = api.Data.GetDataCell("Cb#Consol:C#Local:S#Actual:V#Periodic:A#0710:F#PPE_ICSale:O#BeforeAdj:I#None:U1#00:U2#DEF:U3#L_UNA:U4#None:U5#None:U6#None:U7#USGAAP_FORM:U8#None").cellAmount
    Dim AccountADICSaleAmount As Decimal = api.Data.GetDataCell("Cb#Consol:C#Local:S#Actual:V#Periodic:A#0771:F#AD_ICSale:O#BeforeAdj:I#None:U1#00:U2#DEF:U3#L_UNA:U4#None:U5#None:U6#None:U7#USGAAP_FORM:U8#None").cellAmount
    Dim AccountPPEICProceedAmount As Decimal = api.Data.GetDataCell("Cb#Consol:C#Local:S#Actual:V#Periodic:A#0710:F#PPE_ICProceed:O#BeforeAdj:I#None:U1#00:U2#DEF:U3#L_UNA:U4#None:U5#None:U6#None:U7#USGAAP_FORM:U8#None").cellAmount
    
    'Check if data is real data
	If AccountPPEICSaleAmount = "" AndAlso AccountADICSaleAmount = "" AndAlso AccountPPEICProceedAmount = "" Then
		args.ConfirmationRuleArgs.DisplayValue = (AccountPPEICSaleAmount + AccountADICSaleAmount + AccountPPEICProceedAmount)
        Return True
	' Check if all three amounts are non-zero
    ElseIf AccountPPEICSaleAmount <> 0 AndAlso AccountADICSaleAmount <> 0 AndAlso AccountPPEICProceedAmount <> 0 Then
        ' Set the DisplayValue to a meaningful string for reporting purposes
        args.ConfirmationRuleArgs.DisplayValue = (AccountPPEICSaleAmount + AccountADICSaleAmount + AccountPPEICProceedAmount)
        Return True
    Else
        ' Indicate which accounts are zero (optional for debugging)
        args.ConfirmationRuleArgs.DisplayValue = (AccountPPEICSaleAmount + AccountADICSaleAmount + AccountPPEICProceedAmount)
    End If

Thanks,

Will

Admin
Finance
  • MarcusH's avatar
    MarcusH
    12 hours ago

    A DataCell has a number of properties and functions. You are using .CellAmount but there is also .CellStatus.IsNodata so you could try something like this:

        ' Retrieve the data cell for each account
    Dim AccountPPEICSaleCell As DataCell = api.Data.GetDataCell("Cb#Consol:C#Local:S#Actual:V#Periodic:A#0710:F#PPE_ICSale:O#BeforeAdj:I#None:U1#00:U2#DEF:U3#L_UNA:U4#None:U5#None:U6#None:U7#USGAAP_FORM:U8#None")
    Dim AccountADICSaleCell As DataCell = api.Data.GetDataCell("Cb#Consol:C#Local:S#Actual:V#Periodic:A#0771:F#AD_ICSale:O#BeforeAdj:I#None:U1#00:U2#DEF:U3#L_UNA:U4#None:U5#None:U6#None:U7#USGAAP_FORM:U8#None")
    Dim AccountPPEICProceedCell As DataCell = api.Data.GetDataCell("Cb#Consol:C#Local:S#Actual:V#Periodic:A#0710:F#PPE_ICProceed:O#BeforeAdj:I#None:U1#00:U2#DEF:U3#L_UNA:U4#None:U5#None:U6#None:U7#USGAAP_FORM:U8#None")
    
    'Check if data is real data
	If AccountPPEICSaleCell.CellStatus.IsNoData = True AndAlso AccountADICSaleCell.CellStatus.IsNoData = True AndAlso AccountPPEICProceedCell.CellStatus.IsNoData = True Then
		args.ConfirmationRuleArgs.DisplayValue = 0
        Return True
	' Check if all three amounts are non-zero
    ElseIf AccountPPEICSaleCell.CellAmount <> 0 AndAlso AccountADICSaleCell.CellAmount <> 0 AndAlso AccountPPEICProceedCell.CellAmount <> 0 Then
        ' Set the DisplayValue to a meaningful string for reporting purposes
        args.ConfirmationRuleArgs.DisplayValue = (AccountPPEICSaleCell + AccountADICSaleCell + AccountPPEICProceedCell)
        Return True
    Else
        ' Indicate which accounts are zero (optional for debugging)
        args.ConfirmationRuleArgs.DisplayValue = (AccountPPEICSaleCell.CellAmount + AccountADICSaleCell.CellAmount + AccountPPEICProceedCell.CellAmount)
    End If

     

2 Replies

  • MarcusH's avatar
    MarcusH
    Valued Contributor
    12 hours ago

    A DataCell has a number of properties and functions. You are using .CellAmount but there is also .CellStatus.IsNodata so you could try something like this:

        ' Retrieve the data cell for each account
    Dim AccountPPEICSaleCell As DataCell = api.Data.GetDataCell("Cb#Consol:C#Local:S#Actual:V#Periodic:A#0710:F#PPE_ICSale:O#BeforeAdj:I#None:U1#00:U2#DEF:U3#L_UNA:U4#None:U5#None:U6#None:U7#USGAAP_FORM:U8#None")
    Dim AccountADICSaleCell As DataCell = api.Data.GetDataCell("Cb#Consol:C#Local:S#Actual:V#Periodic:A#0771:F#AD_ICSale:O#BeforeAdj:I#None:U1#00:U2#DEF:U3#L_UNA:U4#None:U5#None:U6#None:U7#USGAAP_FORM:U8#None")
    Dim AccountPPEICProceedCell As DataCell = api.Data.GetDataCell("Cb#Consol:C#Local:S#Actual:V#Periodic:A#0710:F#PPE_ICProceed:O#BeforeAdj:I#None:U1#00:U2#DEF:U3#L_UNA:U4#None:U5#None:U6#None:U7#USGAAP_FORM:U8#None")
    
    'Check if data is real data
	If AccountPPEICSaleCell.CellStatus.IsNoData = True AndAlso AccountADICSaleCell.CellStatus.IsNoData = True AndAlso AccountPPEICProceedCell.CellStatus.IsNoData = True Then
		args.ConfirmationRuleArgs.DisplayValue = 0
        Return True
	' Check if all three amounts are non-zero
    ElseIf AccountPPEICSaleCell.CellAmount <> 0 AndAlso AccountADICSaleCell.CellAmount <> 0 AndAlso AccountPPEICProceedCell.CellAmount <> 0 Then
        ' Set the DisplayValue to a meaningful string for reporting purposes
        args.ConfirmationRuleArgs.DisplayValue = (AccountPPEICSaleCell + AccountADICSaleCell + AccountPPEICProceedCell)
        Return True
    Else
        ' Indicate which accounts are zero (optional for debugging)
        args.ConfirmationRuleArgs.DisplayValue = (AccountPPEICSaleCell.CellAmount + AccountADICSaleCell.CellAmount + AccountPPEICProceedCell.CellAmount)
    End If