Order of Conthist

[wayneswc]

Hi

I've have always believed that as each historical activity is added to a contact record in Goldmine it is given a higher recid.

So i have always written my "latest history" reports with a "select * from conthist where recid in ( select max (recid)..............." type query

I have recently discovered a number of instances where my results were incorrect, and having checked some of these i have found if i pull the history for a contact record and sort it via recid (in Analyzer or Excel) the order of recid does not correspond with the order of date

Can anybody tell me, was my belief wrong, and if so is there another way of pulling the latest history?

or could there be a wrongly aligned settting in GM

Thanks

Wayne

 

[richardy]

Erm why not just sort on the ondate

 

[wayneswc]

Hi Rich
I don't think i can, I don't think i explained myself to well as to the end result i was trying to achieve, I'll try to be brief

My Database has 60,000 records with over 500,000 entries in conthist. I have 40 agents using the database a number of these agents could be contacting the same records for different reasons. What I want to do for each agent is state how many records this year they have had an appointment with, how that 1st appt was resulted and when they last contacted it and what the result was.

As you can appreciate I don't want to be trawling thru the databse doing this manually so I created view which combines contact1, contact2 and conthist which I've named completed, then using this view i wrote the following syntax

"select * from completed where (actvcode = '1st' and ondate > '2004-01-01 00:00:00.000' and userid = 'simond')
or (recid in (select max (recid) from completed where accountno in
(select accountno from completed where (actvcode = '1st' and ondate > '2004-01-01 00:00:00.000' and userid = 'simond'))
group by accountno))
order by accountno, ondate"

This worked fine and all i have to do is change the agents name , but recently it has transpired that roughly 1 in 10 or my results are wrong, and there has been a later action which i haven't picked up. having analysed this it is because the latest history on these does not have the highest recid.

I was told by frontrange that latest history would always have the highest recid, I was wondering if anyone out there could confirm this or offer me any reason why it is not happening in all instances for me


Thanks

Wayne