Skip to main content

Hello! 

I have an attribute field for call activities that is called the “CALLDURATION.” This field is alphanumeric, so I have a formula that converts it to integer when using aggregate functions. 

I need to calculate the total call duration of multiple call activities performed by each person and have it display in the result next to OwnerID, is there a way to calculate that in GI and what function/formula should I use? 

 

This is the formula I use to convert the alphanumeric value to just calculate the number part. 

 

In the Total Time Spent Column, I need a formula that would return the value of total call duration for each owner. ex: first row has made four calls, so Total Time Spent column should display the sum of all four calls made by first row owner. 

 

Hello @jzhu ,

 

The GI aggregate functions calculate all non-null values of the group. I would first check the OwnerID group.

 

Hope this helps!

Hi @hdussa 

The OwnerID group has all non-null values. How does this come in play when trying to calculate the Duration attribute field? Is there something I’m missing? Thanks! 

Hi @jzhu ,

 

I thought this was a simple one but apparently not 🙂.

I tried this locally and unable to apply aggregate functions on the UDFs nor return the values by joining the CSAnswers. After deep diving, noticed the UDFs are stored in the “KvExt” database tables and Acumatica does not expose them as DACs, hence, they are invisible in the GI to make a table relation.

Creating the “CRActivityKvExt” DAC using customization and make a LEFT JOIN with CRActivity.NoteID/ CRActivityKvExt.RecordID columns could enable applying the SUM aggregate function on the stored value.

The below article will help you to create a KvExt DAC.

https://stackoverflow.com/questions/57597280/is-there-a-way-to-get-user-defined-fields-into-selectors/57601545#57601545

@hdussa Thank you for your response!

So I am able to use aggregate function on attributes in the aggregate and total aggregate function columns to calculate sum and avg of the call duration. The issue is because I am using CInt() to convert the alphanumeric value to just numbers, it seems like I can’t include another aggregate function on top of that formula. The formula can only calculate the call duration of one activity, but I can’t add up the time for all activities for one person.

Would I need to build a new DAC for this as well? Is there a formula that could covert the all call duration values to numbers (which I have), and then sum those numbers up? 

Hi @jzhu ,

 

I just checked whether SUM aggregate works on the Attribute field but it does support.

 

Hi @hdussa Thank you for your insight! We found another way to get the total call time we want is through pivot tables so we didn’t have to create a new DAC. 

Hi @jzhu ,

I am glad you figured out another solution.

You might want to share it here that may help others.

 

Thanks!

The solution we found to this is to save the Generic Inquiry as a Pivot Table and work with the data from there. Once you have converted the alphanumeric value into an integer. when you move the field into “values” section, the pivot table automatically sums that field in there. No codes or formulas needed once in the pivot table! 

Reply


Terms & ConditionsCookie settings