Find Null Values in text doc from template (JSON payload)

We have a nightly process that aggregates transactions and sends a JSON payload to an external system

Problem:

     There are times when the payload contains a null value, shown below in RED. In most cases, there was transaction data in the table, but did not populate into the payload

     {"PoolID":"62VP-00xxxx","customerID":"178xxxxx","customerName":"East Central ","itemID":"62VP-00xxxx-AUG-2020","transactionID":513906,"chargeLines":[]}   

 

Requirements:

     1. Within JSON payload, we would like to find "chargeLines":[]  

     2. For each chargeLine [], return "PoolID":"62VP-00xxxx" and "transactionID":513333 

     3. Send an email that contains PoolID's / transactionID's

There are probably other ways of accomplishing the requirements, but this is a start. I would like to understand how to identify these null values and return useful information. Your input would be greatly appreciated.

  Discussion posts and replies are publicly visible

  • You should be able to use a cleaning rule such as below.  This example would handle multiple values to search for, null, "[]" etc.  Essentially ignores the good rows and builds a list of the empty rows (dictionary) for exporting via email, etc.  local!data would switch to your input combined with a!fromJSON().

    a!localVariables(
      local!emptyValuesToSearch: {null,"[]"},
      local!data: {
        {PoolID:"62VP-00xxxx",customerID:"178xxxxx",customerName:"East Central ",itemID:"62VP-00xxxx-AUG-2020",transactionID:513906,chargeLines:"[]"},
        {PoolID:"72VP-00xxxx",customerID:"278xxxxx",customerName:"Central ",itemID:"72VP-00xxxx-AUG-2020",transactionID:513907,chargeLines:"abcd"},
        {PoolID:"82VP-00xxxx",customerID:"378xxxxx",customerName:"East ",itemID:"82VP-00xxxx-AUG-2020",transactionID:513908,chargeLines:null}
      },
      
      reject(
        fn!isnull,
        a!forEach(
          items: local!data,
          expression: {
            if(
              contains(local!emptyValuesToSearch,tostring(property(fv!item,"chargeLines"))),
              {
                PoolID: property(fv!item,"PoolID",null),
                transactionID: property(fv!item,"transactionID",null)
              },
              null
            )
          }
        )
      )
      
      /* -> Send in an email */
    )

  • Thank you Chris! Would I use a script node for this, or some other node within the process?

  • Best case you would create this as a rule with local!data being an input parameter vs a local variable.  The rule could be called from a Script task -> Save into a PV, or you could call it as an Input variable on the Data tab of your Send Email node, then utilizing the ac! value of the variable vs the pv! value which it would be if you saved it with a proceeding script task.  

    Feel free to post additional screen shots, etc, here if you have difficulty with the config. 

  • Can the local!data input be the actual json doc, or does it need to be something else that can be evaluated in flight?

  • If the JSON is stored within a document, you would need to parse out the contents prior to the rule call as AFAIK there are no SAIL capabilities to parse text files.  However there is a plugin that might be used in this case, to retrieve the JSON values from the document:

    Text File Reader

    Note I do not have any experience with that plugin, don't believe we've had this use case internally.

    Otherwise, I'm wondering if there could be extra processing included in the mechanism that creates the JSON payload - such as a validation step prior to writing it to the document.  Can you expand on how your process creates the JSON payload?  Directly into a text file?  Or is there an additional rule being utilized where this new mechanism could be injected, etc?

  • The nightly process with the text doc from template is populating the json template with the following below: 

    I will re-iterate that the transactions are showing in the table, but somewhere during the creation of the json, these values end up blank, and the json is sent to an external system. 

    with(
    local!pendingProcessingTotalCount: rule!CBP_getStagingBillingTransaction(
    processingStatus: cons!CBP_STAGING_PROCESSING_STATUS_P,
    pagingInfo: a!pagingInfo(1, 0),
    isReturnDatasubset: true
    ).totalCount,
    local!pendingProcessingTransactions: a!flatten(
    a!forEach(
    items: enumerate(
    roundup(local!pendingProcessingTotalCount/1000, 0)
    ),
    expression: rule!CBP_getStagingBillingTransaction(
    pagingInfo: a!pagingInfo(
    startIndex: 1 + (fv!item*1000),
    batchSize: 1000
    ),
    processingStatus: cons!CBP_STAGING_PROCESSING_STATUS_P
    )
    )
    ),
    local!chargeLineTotalCount: rule!CBP_getViewChargeLineProcessingStatus(
    processingStatus: cons!CBP_STAGING_PROCESSING_STATUS_P,
    pagingInfo: a!pagingInfo(1, 0),
    isReturnDatasubset: true
    ).totalCount,
    local!pendingProcessingChargeLines: a!flatten(
    a!forEach(
    items: enumerate(
    roundup(local!chargeLineTotalCount/1000,0)
    ),
    expression: rule!CBP_getViewChargeLineProcessingStatus(
    processingStatus: cons!CBP_STAGING_PROCESSING_STATUS_P,
    pagingInfo: a!pagingInfo(
    startIndex: 1 + (fv!item*1000),
    batchSize: 1000
    )
    )
    )
    ),
    if(
    rule!APN_isEmpty(local!pendingProcessingTransactions), {"[]"} ,
    a!toJson(
    {
    {
    a!forEach(
    items: local!pendingProcessingTransactions,
    expression: {
    vanPoolID: fv!item.vanpool_id,
    customerID: fv!item.customer_id,
    customerName: fv!item.customer_name,
    itemID: fv!item.item_id,
    unitNumber: fv!item.unit_number,
    groupBranch: fv!item.gpbr,
    deptID: fv!item.department_id,
    chargeYear: fv!item.charge_year,
    chargeMonth: fv!item.charge_month,
    badDebtDate: rule!GLB_convertDateToInternationalFormat(fv!item.bad_debt_date),
    coordinatorFirstName: fv!item.coordinator_first_name,
    coordinatorLastName: fv!item.coordinator_last_name,
    transactionID: fv!item.staging_billing_transaction_id,
    isAccountSold: fv!item.is_account_sold,
    chargeLines: {
    a!forEach(
    items: index(
    local!pendingProcessingChargeLines,
    wherecontains(
    fv!item.staging_billing_transaction_id,
    index(local!pendingProcessingChargeLines, "staging_billing_transaction_id", null)
    ),
    null
    ),
    expression: {
    chargeType: fv!item.charge_type,
    taxType: fv!item.tax_type,
    amount: fv!item.amount,
    description: fv!item.description,
    account: fv!item.tax_account_id,
    paymentType: fv!item.payment_type,
    paymentName: fv!item.payment_name,
    invoiceDate :rule!GLB_convertDateToInternationalFormat(fv!item.invoice_date),
    chargeLineID: fv!item.staging_billing_charge_line_id,
    primaryEntityID: fv!item.primary_entity_id,
    secondaryEntityID: fv!item.secondary_entity_id,
    chargeLineTimestamp: rule!GLB_convertDateTimeFormatwithTimeZone(fv!item.charge_line_timestamp)
    }
    )
    }

    }
    )
    }
    }
    )
    )
    )

  • What I would do, if this is run directly in the text doc from template node, is move it to a prior script task and save the json value here into a process variable, say pv!jsonOutput.  That process variable can then be used in the text doc from template node, and you can also send the process variable into the rule I suggested earlier with a!fromJson(pv!jsonOutput).  This will create a second variable containing only the blank ([]) rows to use in a follow-up email node and/or debugging, if that makes sense.  

    1) Script Task, create initial json -> pv!jsonOutput
    2) Script Task, use a!fromJson(pv!jsonOutput) input to new rule suggested here -> pv!jsonBlanks
    3) Process text doc to template and send primary output with pv!jsonOutput
    4) Email and/or log pv!jsonBlanks