Finding highest value Date field record from Portal

[dav1089]

Hello,

Issue:

I have Sales Activities Portal on each company's record, now I want to list all first rows of portals from each company record on another layout. Now, I also want only those rows with non-empty follow-up date fields too. My Portal is sorted by Activity Date in descending order. 

What I have done:

I have onlayoutenter script as below:

Enter Find Mode
Set Field[Daily Log::date_followup; "*"]
Set Field[Daily Log::date_activity; Max(Daily Log::date_activity)]
Perform Find[]

It is fetching all records from each Portal which has non-empty follow-up date value

 

I want only highest activity date value row from each portal 

Edited April 5, 2016 by dav1089
typo

[comment]

I would suggest you produce this report from a layout of the child table (Sales Activities? Daily Log?). First, find the records where the follow-up date field is not empty. Then sort the records by company and by date and show them in a layout with only a sub-summary by company part (i.e. no body part).

[dav1089]

I am using daily log table(child table) to produce report , on the same layout I have made it to sort it by Sales Person and Activity Date (Sub-Summary : Sales Person).. I have attached the screenshot of how it looks (Layout and script)

I don't want duplicate company daily log sales activities from daily log Portal for each company, instead I only need first record (obviously highest activity date as Portal is sorted by activity date in descending order)

[dav1089]

I also tried creating calculation field in company table which calculates max activity date and used it to find first row with highest activity date.. but had no luck

[comment]

It cannot work the way you're trying to make it work. Suppose you have 5 companies:  in order to find the 5 most recent records, you would have to perform a find with 5 requests, each with a company ID and the most recent date of that company (which you would have to collect before entering Find mode).

Note also that when you are in Find mode, the expression Max(Daily Log::date_activity) returns empty (and I suspect it does not return what you think it does in Browse mode, either).

My point is that you do not need to do all that. Instead of finding only the most recent records in each group, find all of them (that is those that meet your other criteria regarding the follow-up date) but show only data from the most recent ones, and hide all others.

 

Edited April 5, 2016 by comment

[dav1089]

okey.. So how do I show only most recent ones? 

[comment]

As I said: use a sub-summary part (by CompanyID) to show data only from the first (or last) record in each group.