0

I've been stuck on this for far too long. I've used every resource I could find.

myVariable_current.OrderItem__c.OLI_Final_Amount_3__c because it hasn't been set or assigned

I have a PB that fires when an entitlement is updated with no criteria. The pb updates a field on the entitlement based on a formula (can't do as a formula field as it references its self).

The error first started appearing when using db amp to run some ETL. This error is not reproducible while using the ui for any of the records that failed to update via db amp. Manually applying the changes works.

I had stopped caring about the db amp failures as in all situations the value that would be calculated was 0 which is the value we wanted.

Now I'm seeing this error in a test class and I cannot for the life of me figure it out.

This is the formula:

IF(
[Entitlement].SuppressFromEntitlementReport__c,
0,
IF(
    AND(
        [Entitlement].OrderType__c='Extension',
        NOT(ISBLANK([Entitlement].RenewedFrom__c))
    ),
    IF(
        AND(
            [Entitlement].RenewedFrom__c.OrderType__c='Extension',
            NOT(ISBLANK([Entitlement].RenewedFrom__c.RenewedFrom__c))
        ),
        IF(
            [Entitlement].RenewedFrom__c.RenewedFrom__c.OrderType__c='Extension',
            0,
            [Entitlement].RenewedFrom__c.RenewedFrom__c.AnnualAmount__c
        ),
        [Entitlement].RenewedFrom__c.AnnualAmount__c
    ),
    IF (
        AND(
            NOT(ISBLANK([Entitlement].OrderItem__c)),
            NOT(ISBLANK([Entitlement].OrderItem__c.OLI_Final_Amount_3__c)),
            [Entitlement].OrderItem__c.OLI_Final_Amount_3__c != 0
        ),
        IF (
            AND(
                [Entitlement].TermInDays__c > 363,
                [Entitlement].TermInDays__c < 366
            ),
            IF(
                [Entitlement].OrderItem__c.Quantity__c = [Entitlement].OrderItem__c.EntitlementQuantity__c, 
                IF(
                    [Entitlement].OrderItem__c.Quantity__c = [Entitlement].Quantity__c, 
                    [Entitlement].OrderItem__c.OLI_Final_Amount_3__c, 
                    [Entitlement].OrderItem__c.OLI_Final_Amount_3__c / [Entitlement].OrderItem__c.EntitlementQuantity__c * [Entitlement].Quantity__c 
                ), 
                [Entitlement].OrderItem__c.OLI_Final_Amount_3__c / [Entitlement].OrderItem__c.EntitlementQuantity__c * [Entitlement].Quantity__c 
            ), 
            IF(
                [Entitlement].OrderItem__c.Quantity__c = [Entitlement].OrderItem__c.EntitlementQuantity__c, 
                IF(
                    [Entitlement].OrderItem__c.Quantity__c = [Entitlement].Quantity__c, 
                    [Entitlement].OrderItem__c.OLI_Final_Amount_3__c, 
                    [Entitlement].OrderItem__c.OLI_Final_Amount_3__c / [Entitlement].OrderItem__c.EntitlementQuantity__c * [Entitlement].Quantity__c 
                ) / (
                    IF(
                        [Entitlement].TermInDays__c=0,
                        1,
                        [Entitlement].TermInDays__c
                    )
                ) * 364, 
                (
                    (
                        [Entitlement].OrderItem__c.OLI_Final_Amount_3__c / [Entitlement].OrderItem__c.EntitlementQuantity__c * [Entitlement].Quantity__c
                    ) / (
                        IF(
                            [Entitlement].TermInDays__c=0,
                            1,
                            [Entitlement].TermInDays__c
                        )
                    )
                ) * 364 
            ) 
        ),
        0
    )
)
)

Notice I run several null checks in the correct order in accordance with SF documentation.

I added this formula as a criteria to no avail either:

OR(
    [Entitlement].OrderType__c='Extension',
    AND(
        NOT(ISBLANK([Entitlement].OrderItem__c)),
        NOT(ISBLANK([Entitlement].OrderItem__c.OLI_Final_Amount_3__c)),
        [Entitlement].OrderItem__c.OLI_Final_Amount_3__c != 0
    )
)

EDIT:

In attempting Croredy's advice, I stripped the formula all the way down to:

IF (
    AND(
        NOT(ISBLANK([Entitlement].OrderItem__c)),
        NOT(ISBLANK([Entitlement].OrderItem__c.OLI_Final_Amount_3__c))
    ),
    1,
    0
)

This is the smallest formula that doesn't work. Removing the line referencing OLI_Final_Amount_3__c resolves the issue.

Note: OLI_Final_Amount_3__c is a very simple formula field. I updated it to look like

BLANKVALUE(IF( 
OR(Product__r.Product_Master__r.Summary_Level_Product_Name__c = "VMS", 
CONTAINS(Product__r.Name , "UVM"), 
CONTAINS(Product__r.Name , "651"), 
CONTAINS(Product__r.Name , "1505"), 
CONTAINS(Product__r.Name , "200")), 

VAR_VAD_Subtotal__c , Discounted_Subtotal__c ),0)

to ensure that it always has a non-null value but the issue persists.

It seems like I'm doing exactly what the documentation says to do, and it's not working.

To reiterate, this issue only seems to appear when updating via DB Amp and only one of my unit tests. I cannot reproduce it manually.

EDIT:

Another apparently pointless effort:

IF (
 NOT(ISBLANK([Entitlement].OrderItem__c)),
 IF (
   NOT(ISBLANK([Entitlement].OrderItem__c.OLI_Final_Amount_3__c)),
   1,
   0
 ),
 0
)

This fails as well.

  • 2
    If it were me; I'd create a new debugging formula with far fewer complexities in the above and try and reduce the problem space down to something simpler to analyze – cropredy Oct 18 '17 at 19:04
  • @cropredy Updated my answer – gNerb Oct 18 '17 at 21:32
  • OK - two possibilities come to mind (long shots) [1] - testmethod and dbamp are using a version lower than v41 (current version). Try updating the testmethod version; [2] the formula field for Oli_Final_Amount_3__c is actually returning #Error - this might be masked in the standard UI. Create a real record matching the testmethod and display the value of Oli_Final_Amount_3__c in a report (where #Error will appear) – cropredy Nov 3 '17 at 23:10
  • Thanks, I updated the version on the test and the trigger to no avail. I took a few records from the failed db amp updates and put them in a report, they all show 0.00 as the value for the oli_final_amount_3__c field. I removed the "blankvalue(...,0)" from the oli_final_amount_3__c field as well and the value is still 0.00 with no #Error. – gNerb Nov 6 '17 at 16:21
  • 2
    I believe the reason is hiding inside of your OLI_Final_Amount_3__c formula, could you please change it to something really simple and display the actual formula so we could try to reproduce it and help you? Also could you please provide the smallest test method code with reproduces the issue? – Patlatus Nov 6 '17 at 19:48
1
+100

I believe the reason is hiding inside of your OLI_Final_Amount_3__c formula. Bad data may also be the reason.

Check to make sure all of your records have a populated product__c field. Additionally, check all of the associated products to make sure the Product_Master__c field is also populated as all of these relationships are referenced within your OLI_Final_Amount_3__c formula.

Also you may try to update you formula as well.

Try to convert your formula to something like

BLANKVALUE(IF( 
AND(NOT(ISBLANK(Product__c)),
OR(  (AND(NOT(ISBLANK(Product__c)),NOT(ISBLANK(Product__r.Product_Master__c)),NOT(ISBLANK(Product__r.Product_Master__r.Summary_Level_Product_Name__c)),
(Product__r.Product_Master__r.Summary_Level_Product_Name__c = "VMS")), 
CONTAINS(Product__r.Name , "UVM"), 
CONTAINS(Product__r.Name , "651"), 
CONTAINS(Product__r.Name , "1505"), 
CONTAINS(Product__r.Name , "200"))), 

VAR_VAD_Subtotal__c , Discounted_Subtotal__c ),0)

I hope this change would help you.

  • I think the key takeaway here is that the error PB used which usually indicates an issue with a relationship within the formula within the PB, actually pointed to a relationship referenced within a formula referenced within the formula in the PB. Hope that's not too confusing. – gNerb Nov 6 '17 at 21:02
  • Yes, so if you update the formula like I have provided in my updated answer, I hope the issue with PB ETL tools will disappear also. The reason why you haven't seen this issue constantly with manual edit is because you were updating valid records, while ETL tools and Unit test may deal with invalid data records. – Patlatus Nov 6 '17 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.