Page 1 of 1

Retrieve notes ordered by date

Posted: Sun Aug 17, 2014 11:22 pm
by phil.cohen.hci
I need to find the date of the last note for each of several clientCorporations. My approach is to enumerate each of the clientContacts under each clientCorporation, and then enumerate each of the Notes under each clientContact using getEntityNotes. This works but it's very slow (some of the clientCorporations have upwards of 50 clientContacts). I have some questions:

1. Is there a faster way to do this? I know Hibernate doesn't do Joins.
2. Can I use query instead of getEntityNotes? If so, what's the syntax (I've tried the obvious one but it doesn't work).
3. Does getEntityNotes guarantee that the Notes are in date order when they're returned? Even if they've just been ported from another application?
4. Is there a way I can tell getEntityNotes (or getEntityNotesWhere) how to order the returned records?

(Background: incoming leads are allocated to salespeople on a rota, but if the client has been talked to by someone in the past 5 months they get that incoming call instead. So I'm writing an app that can *quickly* answer the question: "Is this client name in BH, and if so has anyone spoken to them in the past 5 month?")

Re: Retrieve notes ordered by date

Posted: Thu Sep 04, 2014 5:42 am
by SO-BHSupport
Hi Phil,

So I think we can simplify your logic by quite a bit here - we do have the date of the last note as an available field to search on from the Contact (and Candidate) entity.

This is called dateLastComment and is updated each time a note is added to the system against that record with the note added's date and time.

A working query in REST would be:
/core/search/ClientContact?fields=id,name,dateAdded,dateLastComment&sort=-dateAdded&start=0&count=25&query=isDeleted:0 AND dateLastComment:[19140904 TO 20991231235959] - this is looking for all notes from 1914 to 2099. I have to admit it is likely there is a better method to look for those where this field is populated with something but that's the method that I know of and can confirm that works.

A working query in Web Services 2.5 would be:
<where> dateLastComment > '12/12/09 12:00:00 AM'</where>

So what I'd recommend is doing your lookup of Contacts under the ClientCorp, then searching these records for those with a dateLastComment and ordering by this to get the very latest Corp's note.

Hope this helps!

Simon O'Keeffe
Bullhorn Support

Re: Retrieve notes ordered by date

Posted: Thu Sep 04, 2014 7:16 pm
by phil.cohen.hci
Thanks for that. Would be even more useful to have that field at the clientCorporation level, indicating the last note added to any of the contacts linked to that corporation ... that way your customer could more easily identify those clients that hadn't been contacted for a while.