I wasn't sure which forum to post this in so I hope this it's ok here.

For reference, we're still on 6.26 because it's been running perfectly stable doing everything we need it to do.

I'm in query builder trying to make the conditions pull the last history record for an account. For example if an account has 1,000 notes/phone calls etc, we only want to see the most recent result to find out if a company hasn't been called in a while. I've been searching the internet for hours and this site was on the only place that even came close to helping find what I was looking for.

This thread http://www.slxdeveloper.com/forum.aspx?forumid=5000&;postid=19581 references using the "Select Max(CreateDate) from History" in the history.accountid field, but every variation of it I try yields no results.

Has anyone run into a similar problem in the past or might know how to write some code that will do what we need?

Do you have Remotes?  And/or is this a report that would only be ran by Network users?

If so, you could use an SQL View to get this result.

No remotes, all LAN users.

I'm not familar with that app, but it'd be nice to have it in a Group tho since my users are familiar with that UI.

It is not an easy task, but here is how to do it:

- Create a Table with the Desired fields from the Latest History record.

- Using SQL Management Studio, drop the table, and on its place create an SQL View. Here is a Sample Query that you could use to base your View upon:

SELECT  CATEGORY, DESCRIPTION, NOTES, TYPE
FROM HISTORY H INNER  JOIN
(
 SELECT ACCOUNTID, MAX(CREATEDATE) AS CREATEDATE FROM
 HISTORY GROUP BY ACCOUNTID
) HD ON H.ACCOUNTID = HD.ACCOUNTID AND H.CREATEDATE = HD.CREATEDATE

By dropping the Table at the SQL Level and then adding a View with the same name and Schema, then you trick SLX into using the View.

As stated, this is only a good option if you don't have Remotes, otherwise you could still do it, but you would have to find the means to distribute the Views, etc, and it turns into a bit more complex process.