Microsoft Dynamics CRM (Archived)

Looking for advice on how to approach finding the max value in a series of records

(0) Share

Report

Posted on by ACECORP

1,579

I am looking for some advice on how to approach reaching the goal described below.

I need to get the logic of the approach figured out, so I know how to approach writing my CRM 2011 Plugin code.

Given

I have an entity named Payments, that contains many records.

The two fields I am concerned with in the Payments entity are the Payment Number field and the Forfeit Number field.

Every record in the Payments entity has data in these two fields.

Many records in the Payments entity may have the same value in the Forfeit Number field.

Of those records that contain the same value in the Forfeit Number field, each will ALWAYS have a different number in the Payment Number Field.

When a new payment is entered, my post-operation plugin need to do the following when the user saves the new payment:

(1)Search all the payments that have same Forfeit Number as the current payment that is being entered, and (2) determine what the greatest whole number value is in the Payment Number field, among all these records. Then return that whole number, add 1 to it, and place that in the current record's Payment Number field.

Looking at the "steps of the process" shown above as (1) and (2) -- how might these actually be implemented from a process standpoint?

I can't write code to do this until I understand exactly what I need to do, and determining the logical steps to do what's listed above is where I am having difficulty.

If I know what I am looking for, I can use JQUERY within my plugin to obtain the GUID of the record, and have done that type of thing before. However in this particular situation, I don't know what I am looking for because the Payment Number field may be a 1 or it may be a 10938474 and I will never know that ahead of time. That's what I need to use this plugin to "find".

Any recommendations on the "logic of the approach" would be greatly appreciated.

*This post is locked for comments

All responses (6)

Answers (1)

Aric Levin 30,188 on at

Like (0)

Report

RE: Looking for advice on how to approach finding the max value in a series of records

Jim, you are very welcome. This is what the community is all about...
ACECORP 1,579 on at

Like (0)

Report

RE: Looking for advice on how to approach finding the max value in a series of records

Aric,

Thanks so much for the jumpstart. You are a miracle worker! That works exactly as I needed.

I've never done anything with conditional expression fetch style code before so this type of approach adds a whole world of possibilities to my arsenal.

Your clear presentation allows me to understand what's happening and apply it to other things, in addition to building onto it and changing it around to accomplish different things.

Thanks so much!
ashlega 34,475 on at

Like (0)

Report

RE: Looking for advice on how to approach finding the max value in a series of records

Or you could use "orderby" on the payment number and "count=1" when creating fetchXml. Add a condition for the ForfeitNumber (equals the value being entered), and you'll get your max number.

I'm not sure how is JQUERY involved, though. JQUERY works on the client side (javascript library). Plugins work on the server side. Are you trying to do this on the client?
Verified answer

Aric Levin 30,188 on at

Like (0)

Report

RE: Looking for advice on how to approach finding the max value in a series of records

Hi Jim,

That is too much work. If you have to retrieve the entire entity and do a comparison on each record.

That is the purpose of the max attribute.

See example:

ConditionExpression expr = new Condition

        public int GetMaxValue(string entityName, string attributeName, string filterAttributeName, Guid filterAttributeId)
        {
            int rc = 0;
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("<fetch distinct='false' mapping='logical' aggregate='true'> ");
            sb.AppendLine(" <entity name='" + entityName + "'> ");
            sb.AppendLine(" <attribute name='" + attributeName + "' alias='" + attributeName + "_max' aggregate='max'/> ");
            sb.AppendLine(" <filter type='and'>");
            sb.AppendLine("   <condition attribute='" + filterAttributeName + "' operator='eq' value='" + filterAttributeId.ToString() + "' /> ");
            sb.AppendLine(" </filter> ");
            sb.AppendLine(" </entity> ");
            sb.AppendLine("</fetch>");

            EntityCollection totalCount = service.RetrieveMultiple(new FetchExpression(sb.ToString()));
            if (totalCount.Entities.Count > 0)
            {
                foreach (var t in totalCount.Entities)
                {
                    rc = (int)((AliasedValue)t[attributeName + "_max"]).Value;
                    break;
                }
            }
            return rc;
        }
ACECORP 1,579 on at

Like (0)

Report
RE: Looking for advice on how to approach finding the max value in a series of records

I'll take a look at your suggestion when time permits.

However, since I already started going down the road of JQUERY, I think I am onto something that will work, and I'll stay the course for the time being.

Here is what I ended up doing.

The Forfeit Number field is actually a Lookup field to a related entity, as opposed to a text box.

Since I know the forfeit number I need, I used an entity collection and JQUERY to "find the" GUID that corresponds to the Forfeit Number.

Next I pass the Forfeit Number GUID I obtained from the previous step to a new entity collection with JQUERY, to get from that a collection of Payment Records that contain the Forfeit Number I am seeking to obtain the Max Payment Number from.

Once I have that entity collection populated with the potential candidate records, I will loop through it and do some kind of comparison on the Payment Number fields to ultimately arrive at the Max payment Number

Then I will take that Max payment number and increment it by 1, and then add that to the current payment.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Looking for advice on how to approach finding the max value in a series of records

I think you've got the right steps, but you'll need to do a FetchXML to get the 'max' value of the payment number. There are some examples of aggregations in FetchXML in this article.

msdn.microsoft.com/.../gg328122.aspx

Good luck!