How to pull activity notes into GI?

​@schneur96 Thank you for your help! 

Is there a way to only show the most recent activity for that PO? Right now I am grouping by PO #, and the aggregate function for last modified date is set to Max. But while the date is pulling in as most recent, the activity subject is not right. 

You need todo the MAX on the results grid data field not on the aggregated function fields. 

=MAX([CRActivity.Subject])

Hi ​@schneur96 I tried that as well, unfortunately it did not work. It is still giving the most recent date but not the right subject. The first line should be Test Activity 2 not Testing Activity.

If you share your GI i can take a look. 

Hi ​@schneur96 , thank you! Still trying to figure out how to aggregate the subject field to show most recent...

Hello ​@schneur96  and ​@jzhu ,

Have you found any solutions for this? I am in need of something similar.

Thanks,

RJ

I reviewed the GI and noticed that you had a grouping for both POOrder.orderNbr and CRActivity.Subject. If you are only looking to have one row for each activity then only do a grouping on the POOrder.orderNbr.

I tested setting the Data Field with both =MIN([CRActivity.Subject]) and =MAX([CRActivity.Subject]) and with the grouping just on POOrder.orderNbr its working as expected. 

​@schneur96 I have tried grouping only by POOrder.OrderNbr and using =MAX([CRActivity.Subject]), however it only gives me the latest date and not the note subject associated with it: 

Hello ​@jzhu and ​@schneur96,

I have tried setting my =MAX([CRActivity.Subject]), and still no success with my most recent Credit Hold Notes (CHN) activity. Do you have any suggestions?

Thanks!

This can be accomplished by joining the Activities DAC onto itself with the extra condition of “CreatedDate < CreatedDate”. This will leave the record that has the maximum created date without a match. Then add a condition to only include records that don't have a match and you’re left with the most recent Activity.

Efficiency-wise, this is not the best approach. There’s a discussion on this Stack Overflow thread about how using NOT EXISTS instead of ID2 IS NULL is more efficient, but I don’t know how to finagle the Generic Inquiry screen to write such a query, or if the optimizer will pick up this adjustment. Expect a performance impact if you have a lot of Activities.

See the attached proof of concept. This lists the most recent Activity per related entity.