Field to count linked Products in Custom Module where record status is "Active".

Field to count linked Products in Custom Module where record status is "Active".

So, I'm having issues with this custom function. I'm trying to update a field each day, that counts active Virtual Products.

I have a custom module called Agreement History where records for each service agreement are stored.

In each of these records, products are indicated in lookup fields. Each record has the 3 fields (Product 1, Product 2, Product 3) where products are linked.

So, for instance, there is a product called OFA Silver Virtual Package, and I want the function to count how many Agreement History records with the field Contract Status as Active, have this product linked in them.

There are no Agreement History records where there are 2 of the same product are linked, but they may be in any one of the 3 product fields.

The field in the Products record is where the count should be stored and it is a simple Number field.

My issue is that the function runs and only returns 0 for all products.


void automation.CountVirtualProducts()
{
    // Fetch the active Agreement History records
    agreementHistoryRecords = zoho.crm.getRecords("Agreement_History",1,200);
    
    // Initialize a variable to store the total product count
    totalProductCount = 0;
    
    // Loop through each agreement history record
    for each agreement in agreementHistoryRecords
    {
        // Check if the contract status is 'Active'
        if(agreement.get("Contract_Status") == "Active")
        {
            info "Processing Agreement ID: " + agreement.get("id");
            
            // Check Product 1
            if(agreement.get("Product_1") != null)
            {
                info "Product 1 is linked";
                totalProductCount = totalProductCount + 1;
            }
            else
            {
                info "Product 1 is empty";
            }

            // Check Product 2
            if(agreement.get("Product_2") != null)
            {
                info "Product 2 is linked";
                totalProductCount = totalProductCount + 1;
            }
            else
            {
                info "Product 2 is empty";
            }

            // Check Product 3
            if(agreement.get("Product_3") != null)
            {
                info "Product 3 is linked";
                totalProductCount = totalProductCount + 1;
            }
            else
            {
                info "Product 3 is empty";
            }
        }
    }
    
    // Log the final count
    info "Final Total Product Count: " + totalProductCount;
}


Any help would be appreciated!