Get list of all companies(CONTACT) with the latest action taken on the companycard

We are trying to see what the state of our CONTACT database is. We want to find the last action taken (wtih date) and type of that action for each CONTACT in our database.

I am using, as suggested by the Dutch SO-supportteam, the TRAVELTRANSACTIONLOG table.

This is my query so far:

select
CONTACT.name, TRAVELTRANSACTIONLOG.ttime, TRAVELTRANSACTIONLOG.type
FROM
SuperOffice7.CRM7.TRAVELTRANSACTIONLOG
inner join SuperOffice7.CRM7.CONTACT on
TRAVELTRANSACTIONLOG.record_id = CONTACT.contact_id

It looks like it's getting met the latest actions taken on our CONTACTs, but I can't find the TRAVELTRANSACTIONLOG.type, so I have no clue what the action taken actually means...

Of course there's also the possibiblity of me having taken a wrong approach on the problem in the first place.

Please advise.

RE: Get list of all companies(CONTACT) with the latest action taken on the companycard

Hi!

In our Database reference guide is where you will find all of the codes for the TTL types.

https://community.superoffice.com/Documentation/SDK/SO.Database/infoTraveltransactionlog.html

There is not a lot of context to go on, so you could try something like this:

select C.name, TTL.ttime, TTL.type, Action =
CASE TTL.type
	WHEN 4352 THEN 'INSERT'
	WHEN 4608 THEN 'UPDATE'
	WHEN 4864 THEN 'DELETE'
	ELSE 'UNKNOWN'
END
FROM CRM7.TRAVELTRANSACTIONLOG AS TTL
inner join CRM7.CONTACT AS C on TTL.record_id = C.contact_id
WHERE TTL.type > DATEADD(HOUR, -1, GETDATE()) -- Changes in the last hour

Hope this helps!

Af: Tony Yates 15. maj 2017

RE: Get list of all companies(CONTACT) with the latest action taken on the companycard

Hi Tony,

 

Thanks that helps a bunch.

I did:

select SuperOffice7.CRM7.CONTACT.name, SuperOffice7.CRM7.TRAVELTRANSACTIONLOG.ttime, SuperOffice7.CRM7.TRAVELTRANSACTIONLOG.type, SuperOffice7.CRM7.ASSOCIATE.name,

CASE
WHEN SuperOffice7.CRM7.TRAVELTRANSACTIONLOG.type = 4352 THEN 'INSERT'
WHEN SuperOffice7.CRM7.TRAVELTRANSACTIONLOG.type = 4608 THEN 'UPDATE'
WHEN SuperOffice7.CRM7.TRAVELTRANSACTIONLOG.type = 4864 THEN 'DELETE'
ELSE 'UNKNOWN'
END as 'actie'

FROM SuperOffice7.CRM7.TRAVELTRANSACTIONLOG
inner join SuperOffice7.CRM7.CONTACT on SuperOffice7.CRM7.TRAVELTRANSACTIONLOG.record_id = SuperOffice7.CRM7.CONTACT.contact_id
inner join SuperOffice7.CRM7.ASSOCIATE on SuperOffice7.CRM7.TRAVELTRANSACTIONLOG.associate_id = SuperOffice7.CRM7.ASSOCIATE.associate_id

ORDER BY ttime DESC

(sorry for not using writing conventions)

 

However, and what I was actually looking for:

If I needed to find the last action(document, meeting, appointment, email, etc) belonging to the CONTACT. How should I approach that?

Af: Sjoerd Hoogewerf 16. maj 2017

RE: Get list of all companies(CONTACT) with the latest action taken on the companycard

Hi Sjoerd,

This is where the tablenumber field comes in... 

select C.name, TTL.ttime, TTL.type, Action =
CASE TTL.type
    WHEN 4352 THEN 'INSERT'
    WHEN 4608 THEN 'UPDATE'
    WHEN 4864 THEN 'DELETE'
    ELSE 'UNKNOWN'
END,
Entity = CASE TTL.tablenumber
	WHEN 5 THEN 'Contact'
	WHEN 6 THEN 'Person'
	WHEN 9 THEN 'Appointment'
	WHEN 10 THEN 'Document'
	WHEN 11 THEN 'Project'
	WHEN 13 THEN 'Sale'
END
FROM CRM7.TRAVELTRANSACTIONLOG AS TTL
inner join CRM7.CONTACT AS C on TTL.record_id = C.contact_id

You can find a reference to all the table numbers in the database reference guide:

Database Reference Guide

Tables by number

 

 

You will also find that you can join to the ConceptualTable table, on the tablenumber column...and get the names from there. :-)

select C.name, TTL.ttime, TTL.type, Action =
CASE TTL.type
    WHEN 4352 THEN 'INSERT'
    WHEN 4608 THEN 'UPDATE'
    WHEN 4864 THEN 'DELETE'
    ELSE 'UNKNOWN'
END,
T.name
FROM CRM7.TRAVELTRANSACTIONLOG AS TTL
inner join CRM7.CONTACT AS C on TTL.record_id = C.contact_id
INNER JOIN CRM7.CONCEPTUALTABLE AS T ON T.tablenumber = TTL.tablenumber

 

If you really want is all of the latest activities for a contact, then it seems like this isn't necessarily the right approach. 

If you are limited to SQL, the you will want to write the queries to aggregate all of that information and group it in a way that makes sense to you. 

If you must know when items were inserted, updated or deleted, then referencing the travel transaction log seems useful...knowing the corresponding table numbers and record id's.

 

Best Regards

Af: Tony Yates 16. maj 2017

RE: Get list of all companies(CONTACT) with the latest action taken on the companycard

thank you Tony for all the help.

I now did, as alternative:

SELECT CONTACT.name as contact_name, ASSOCIATE.name as asscociate, DOCUMENT.name as document_name ,APPOINTMENT.registered, APPOINTMENT.task_idx, APPOINTMENT.type, APPOINTMENT.document_id, SALE.heading as sale_name, PROJECT.name as project_name FROM CRM7.CONTACT
LEFT JOIN CRM7.APPOINTMENT on APPOINTMENT.contact_id = CONTACT.contact_id
LEFT JOIN CRM7.ASSOCIATE on APPOINTMENT.associate_id = ASSOCIATE.associate_id
LEFT JOIN CRM7.SALE on SALE.sale_id = APPOINTMENT.sale_id
LEFT JOIN CRM7.DOCUMENT on APPOINTMENT.document_id = DOCUMENT.document_id
LEFT JOIN CRM7.PROJECT on APPOINTMENT.project_id = PROJECT.project_id
ORDER BY registered DESC

and got something that comes very close (close enough) to what I needed.Just to be sure: There are no "standard queries" for common SO questions, are there?

Af: Sjoerd Hoogewerf 17. maj 2017

RE: Get list of all companies(CONTACT) with the latest action taken on the companycard

Hi Sjoerd,

When using SuperOffice, and viewing a company cards and seeing all of the latest activities in the activitiy tab, these results are the result of an underlying API known as Archive Providers.

There are several standard archive providers for different aspects of SuperOffice CRM, contacts, projects, sales, etc, and in many ways archive providers can be concetualized as programmatic views. Each archive provider requires a list of desired columns, at least one criteria (restriction) and initial paging information. 

While nearly every table in the database is exposed through an archive provider, there are several composite providers that aggregate data from several sub-providers. Every tab control in SuperOffice, and several lists, are fed data from these composite providers and are uniquely configurable by each user. 

Fundamentally they all do execute a common pattern of SQL for each desired outcome - activities for example, but do have some dynamic nature due to the user configurability. 

Most composite providers perform two-phase queries for performance reasons, first getting id's of intended results, then getting values of desired results. 

The best way to determine what SuperOffice is doing, and gain better insight into how you might translate that into SQL is to perform a trace and observe the communication.

Finally, using SQL is limited to an onsite local installation and does not scale well. Despite being an old MSSQL DBA, I am the biggest proponent of using the API's instead of SQL, because if it's strengths in this and other areas. 

Best Regards

Af: Tony Yates 18. maj 2017

RE: Get list of all companies(CONTACT) with the latest action taken on the companycard

Hello Tony,

Thanks again for your thorough answer. I am hoping for your advise on this and future exercises.

Using API's or two-stage queries is above my knowledge I am afraid. What we are doing is using queries in Excel or MSSQL Server Management Studio to combine info that cannot be combined in the SO userinterface. Say combine info from Company, Sale and Project.

In this case we were looking to find companies in our database that we haven't had dealings with for over 2 years. However we might have send them mailings... 
What, in your opinion would be the best way to go about this?

Thanks again,

Sjoerd

Af: Sjoerd Hoogewerf 26. maj 2017

RE: Get list of all companies(CONTACT) with the latest action taken on the companycard

Just in case, have you had a look into our SAINT system?
May be it can cover your use case.

regards,
Conrad

Af: Conrad Weyns 26. maj 2017

RE: Get list of all companies(CONTACT) with the latest action taken on the companycard

Hello @Conrad,

Please tell me more. I have never before heard of SAINT...

kind regards,

Sjoerd

Af: Sjoerd Hoogewerf 29. maj 2017

RE: Get list of all companies(CONTACT) with the latest action taken on the companycard

Search for saint on the community pages here...
May be this is a good start: https://community.superoffice.com/en/customer/learn/settings-and-maintenance/general/configure/saint/how-to-setup-saint/

(I believe you need a Sales Intelligence license to use it)

/conrad

Af: Conrad Weyns 29. maj 2017