function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Anupama@28Anupama@28 

Count active entitlements per account

Hi,
We have a requirement to count the number of active entitlements under an account.
An entitlement is considered as acitve if "Status = Active AND  EndDate >= Today() and Support level (Text formula field) = 'GOLD or SILVER or PLATINUM' "

we have tried with the below ...
1: Created a rollup summary field on 'Account' to count the active entitlements which meet above criteria , but the problem here is we cannot filter the entitlements using the field status and support level since formula using product object

2:  > Created a custom checkbox field 'isActiveEntitlement' on entitlement object so that we can create a rollup summary field on account to count entitlements with isActiveEntitlement = true
> Created a trigger to set if entitlement is active or not. below is Sample code of trigger

if(Status = Active && EndDate >= Today() AND (Support level = GOLD / SILVER / PLATINUM))
{
      isActiveEntitlement = True;
}
else
{
     isActiveEntitlement = False;
}
Update Entitlement records;

The problem here is if I edit and change the endDate of entitlement manually, the status becomes expired and the trigger is firing and everything is working good but if the date is expired automatically then state is setting to expired but trigger is not firing, isActiveEntitlement is not setting to false hence count is not getting updated on account

3: Created a process builder with same logic as above trigger , this also works good if the entitlement is edited manually and not for automated / backend process.
Please help me to fix the issue or suggest any other ways to count active entitlements.

Thanks,
Anupama




 
LBKLBK
Hi Anupama,

Your trigger will fire only if there is a DML change in the data.

What you need is a formula to set isActiveEntitlement and a rollup summary on Account (I assume Account - Entitlement is a Master-Detail relationship).

This would be your formula to fill isActiveEntitlement. 
IF(AND(Status = Active, EndDate >= Today(),  OR(Support_Level = 'GOLD', Support_Level = 'SILVER', Support_Level = 'PLATINUM')),True,False)
Since the formula and Roll up Summary field are dynamically calculated, this will work even when the EndDate automatically expires.

If the Account - Entitlement relationship is lookup, Rollup summary field has to be updated using a Trigger as well as a schedule that will run on a daily basis at midnight (probably couple of seconds after midnight).

Hope this helps.
 
Anupama@28Anupama@28
Hi LBK,

Thanks for the quick response.
We have tried with formula as well but no luck because  STATUS field is not showing up to create the formula and if I skip STATUS field and use 'EndDate >= Today()' the formula field 'isAcitveEntitlement' is not showing for filtering records in rollup summary.

Thanks,
Anupama
LBKLBK
What type of field is the Status?

Can you provide me the API Names and Data Type of all these fields, so that we can try to rewrite the formula?

Is the relationship between Account and Entitlement a Master-Detail?
Anupama@28Anupama@28
Hi,

Yes, the relationship between Account and Entitlement is a Master-Detail.
Below are the API names and DataTypes of used Entitlement fields.

1: FIELD NAME : Status ; DATA TYPE : PickList - STANDARD FIELD

Statu Field

2: FIELD NAME: EndDate ; DATA TYPE: Date - STANDARD FIELD
EndDate Field

3: (CUSTOM FORMULA FIELD), FIELD NAME : SupportLevel__c, DATATYPE : Formula field with return type Text
FORMULA : Product__r.Support_Level__c
> Product__c is a custom field on Entitlement lookup to Product
> Product has a custom picklist field called 'Support_Level__c' with values 'Gold, Silver, Platinum,Trial, community, ......'

4: (OPTIONAL FIELD), FIELD NAME : StatusIndicator, DATA TYPE : Text(64)
User-added image

NOTE: The 3 standard field on entitlement 'EndDate, Stauts, StatusIndicator' are linked together i.e. if Date expires then status is set to 'Expired' and StatusIndicator is set to 'Cross mark' automatically as shown in the below picture hence using any one field among these 3 will work for us.

ACTIVE ENTITLEMENT:

User-added image 
INACTIVE ENTITLEMENT:
User-added image
 
LBKLBK
Hi Anupama,

Try this formula. 
IF(AND(ISPICKVAL(Status, 'Active'), EndDate >= Today(),  OR(Product__r.Support_Level__c = 'GOLD', Product__r.Support_Level__c = 'SILVER', Product__r.Support_Level__c = 'PLATINUM')),True,False)
I guess the status field is in Entitlement object.
Anupama@28Anupama@28
Hi,

first of all thanks for your quick replies.

Let me list out the limitations.

Limitations on account rollup summary field
> Status field is not visible in filter criteria (but we can just use EndDate and status is not required)
> Any kind of formula fields on Entitlements are not visible to setup filter criteria

Limitations of entitlement
> Status field is not available anywhere even to setup the formula also, if I use (ISPICKVAL(Status, 'Active')) then it throwing error field status is not available
> If we just use formula with endDate and support level the formula field is not visible on account rollup summary filtering





 
LBKLBK
Hi Anupama,

I guess the Rollup Summary is not going to work for you.

Can you try this trigger instead?
  
trigger UpdateEntitlementCount on Entitlement__c (after insert, after update, after delete) {
    Set<Id> setEntitlementIDs = new Set<Id>();

    if (!Trigger.isDelete) {
        for (Entitlement__c objEntitlement : Trigger.new) {
            setEntitlementIDs.add(objEntitlement.Account__r.Id);
        }
    }
	else{
        for (Entitlement__c objEntitlement : Trigger.old) {
            setEntitlementIDs.add(objEntitlement.Account__r.Id);
        }
    }
	
    if (!setEntitlementIDs.isEmpty()) {
        Map<Id, Account> mapAccountsToUpdate = new Map<Id, Account>();
		
		for (Entitlement__c objEntitlement : [SELECT Account__r.Id, Id FROM Entitlement__c 
								WHERE Status = 'Active' AND Product__r.Support_Level__c IN ('PLATINUM', 'GOLD', 'SILVER') AND EndDate >= TODAY AND Account__r.Id IN :setEntitlementIDs]) {
            Account objAccount;
			if(mapAccountsToUpdate.containsKey(objEntitlement.Account__r.Id)){
				objAccount = mapAccountsToUpdate.get(objEntitlement.Account__r.Id);
			}
			else{
				objAccount = new Account(Id = objEntitlement.Account__r.Id, Entitlement_Count__c = 0);
			}
			objAccount.Entitlement_Count__c = (Integer)objAccount.Entitlement_Count__c + 1;
			mapAccountsToUpdate.put(objEntitlement.Account__r.Id, objAccount);
        }

        if (!mapAccountsToUpdate.isEmpty()) {
            update mapAccountsToUpdate.values();
        }
    }
}
Please verify the object names and field names once before saving this trigger.

Let me know if this helps you.
Anupama@28Anupama@28
Hi LBK,

As I have mentioned earlier, If the entitlement is getting expired automatically then it is not considering as field update hence trigger itself is not firing.

e.g. 1: If EndDate is 26-APR-2017 and if I manually go and edit the entitlement , change the EndDate to 25-Apr-2017 then trigger fires and field 'isActiveEntitlement' is setting to FALSE/UNCHECKED  and from here everything works good
2: If the EndDate is 26-APR-2017 and entitlement automatically expires today at 12 then trigger itself is not firing .

May be with scheduled apex job at midnight to update 'isActiveEntitlement' works.

-Anupama
LBKLBK
Yes Anupama. You are right. Keep this trigger and use a schedule to update the isActiveEntitlement to false at midnight.
Anupama@28Anupama@28
ok, Thanks :)

 
LBKLBK
I guess you will have to set Status to Expired, in the schedule after midnight. And, the trigger will take care of the rest. Let me know how it goes.
Thomas Shelby 26Thomas Shelby 26
Gold is a valuable metal utilized as a place of refuge, its cost is thusly recorded to the economy and if you want to buy it visit this site:  https://achatdor.ch/achat-etain-vente-geneve-nyon-fribourg/