Dynamics Community Forum Thread Details

Calculate Percentage with different entity fields

Hi Chaitu,

The first thing I want to point is that its hard to store a value in the same field you are retrieving a value from. I would definitely suggest created another field alongside field C to ensure no looping effects are experienced with whichever solution you undertake. (i.e. trigger an OnChange event on field C, which will trigger again after the calculation is triggered, and again and again etc.)

That being said, as you've stated, calculated fields only work on off the data that is stored on the entity itself, it will not be able to pull data from associated records. The best solution is the one suggested by Ramzi Ghribi through a plugin or Javascript.

It is however possible to run the calculation from a single workflow that has the following conditions:

Field A and Field B must exist on Entity C to store Field A's and B's data. A duplicate field C must be set to hold the field C's current value. The field list would then be: Field A, Field B, Field C, Duplicate Field C.
Condition --> Entity A, Entity B and Field C must contain data, otherwise set Field A and Field B to "0"
Trigger --> OnUpdate of Entity A lookup OR OnUpdate of Entity B lookup OR OnUpdate of Field C, run the workflow
Step --> Update Entity C's field A and field B with the data from both lookup records.

Then add a business rule in place to conduct the calculations (Set its scope to entity to ensure its done even in the background). The business rule must conduct the following:

1. update the duplicate field C's value with the calculation; FieldA*FieldB

2. set the current field C's value to the duplicate field C's value/100 and you should be good to go.

That is a solution that does not require any dev and can be updated by either the customer or yourself with little disruption to the current records.

If you are able to create a plugin, then use the following fetch structure seen on my blog (www.ec.co.za/.../dynamics-crm-plugins-the-basics) to build the fetch for Entity A and Entity B and conduct the calculation:

if (EntityAEntity != null)
{
//EntityA
String fetchEntityA = String.Format(@"", EntityARef.Id.ToString());

EntityCollection EntityAresults = service.RetrieveMultiple(new FetchExpression(fetchEntityA));

if (EntityAresults.Entities.Count == 1)
{
entityC["fieldaschema"] = new Money(FieldARef);

service.Update(entityC);
}
}

if (EntityB != null)
{
//EntityB
String fetchEntityB = String.Format(@"", EntityBRef.Id.ToString());

EntityCollection EntityBresults = service.RetrieveMultiple(new FetchExpression(fetchEntityB));

if (EntityBresults.Entities.Count == 1)
{
entityC["fieldbschema"] = new Money(FieldARef);

service.Update(entityC);
}
}

Decimal FieldA = EntityCRef.Attributes.Contains("fieldaschema") ? ((Money)EntityCRef["fieldaschema"]).Value : 0;
Decimal FieldB = EntityCRef.Attributes.Contains("fieldbschema") ? ((Decimal)EntityCRef["fieldbschema"]).Value : 0;

decimal calculation = (FieldA * FieldB) / 100;

entityC["fieldC"] = new Money(calculation);

Obviously it's hard to determine any of the values outside of the initial requirement.

Hope this helps.

Quick Links

Leaderboard