Integration Specification

ClickHome will be used in the client organisation to manage Construction and Site Supervisor related processes.   However, the client will also have a range of other systems addressing other aspects of their business, and there will be some commonality with ClickHome in the data maintained by those systems.  There are benefits of convenience and efficiency to be gained by sharing data between the systems.

The specific objectives of the integration facilities are to:

o   minimise data maintenance activity;

o   improve control of the purchase order approval process by providing trade/supplier updates and job progress information;

o   improve the flow of information about Contract milestones and Notes/Discussions related to Contract tasks.

This document aims to provide guidance for the client organisation in understanding and preparing for ClickHome integration and consequently contains a moderate degree of detailed and technical information.


The Interface Architecture

            The objective of the Interface Architecture is to cater for different users who have different existing systems, and simplify/minimise the work required to build interfaces.

In concept the interface facilities provided by ClickHome can be summarised in the following diagram.


o       All facilities to the right of the Interface Border line are existing services and are defined in detail so client systems can be easily set up to work with them.


o      All facilities/procedures to the left of the Interface Border are the responsibility of the client.  Those responsible for the client’s existing systems must use their knowledge of those systems to identify appropriate data sources and process points from which to capture the required data. Guidance on how to build these components is provided in the following sections.

Note that support and assistance can be provided through ClickHome staff.


     ClickHome Imports

            The aim of this process is for the client systems to deliver transactions into predefined tables in the ClickHome database.  These tables are documented in the appendices to this document.

            The processing of these transactions within ClickHome can be scheduled to run on a regular basis during the day.  The period between runs can range from 1 minute to 24 hours, with the normal setting being about 5 minutes.   As a result the delivery of data to ClickHome can be almost immediate upon the client systems delivering the transactions.

            The ClickHome database is a Microsoft SQL Server database and the interface tables are within that database.  Provision has been made for a special username that has access to those tables alone. 

The details are: Username:       CHIntegration
                           Password:        Available on request

Clients are free to determine how they provide the data in the interface tables - within the limitations of using the special username.  However, the following general suggestions may be helpful:

a)      SQL Triggers
If the clients systems also use Microsoft SQL Server, then a successful option has been to use TRIGGERS on appropriate Insert and Update events.

Note that the interface tables may include columns for data that are not available to the TRIGGER, requiring access to other tables in the clients system to finalise each record added to the table.  This necessitates a 2-stage process as follows.

    Many of the interface tables have a field called cdInterfaceType that specifies the transaction type from a range of options (see appendix).  If that field is set to a value starting with the word LOOKUP, the ClickHome processes will ignore the transaction in the current run.

Using this feature, the TRIGGER can create the interface record with a LOOKUP setting indicating that further work needs to be done before the transaction is complete.   A second process can then obtain the missing data, fill in the missing fields in the transaction and finally change cdInterfaceType to allow processing to take place.

b)      Programming
If the client system database is not able to support the SQL Trigger approach, a program could be written using a Microsoft ADO Connection object that accesses the ClickHome database

That program could either:
  -  directly place the transactions in the interface tables    or
  -  extract the data to text files (eg. Comma delimited or XML) and use stored
      procedures to move the data into the database.

ClickHome Exports

            The process supported for sending data from ClickHome to client systems involves ClickHome either calling a predefined client function with appropriate parameters to fetch the data or to set up an interval to call information from Clickhome. 

            The client may decide to write new programs for this purpose or to include the specified classes in existing data level servers in their applications.

            The client also has the option to apply the data in each such method call directly to their database, or to store them as transactions (perhaps in a text file) and process them as a batch on a periodic basis.


Importing information into ClickHome


Import – Notes/Discussions

            Note that, for the interface of Notes / Discussions some planning is required to derive the Note Type and Note Purpose codes from the details available in the client systems.

Table:   tblInterfaceNotes

            Interface Types (cdInterfaceType):
            No interface types are relevant to this interface.  The full details of          new
            Notes / Discussions are transferred to ClickHome as they are created in
            the client systems.  NB Changes to these entries are not contemplated by the
            system and no provision is made to update them via the interface

                        These transactions should be raised as soon as the client system creates the
                        new entry. 



Export – Progress

            Provision is made to export comprehensive progress notifications about all Contract tasks to the client systems.



Called Interface

Task Planned


Task Started


Task Progressed


Task Completed


Task Deleted



Export – Purchase Orders

            Export of Purchase Order details from ClickHome is not provided and does not make sense in the context.  However, provision is made for certain specific events related to Purchase Orders to be made available to the client systems.



Called Interface

PO Approved

(full or partial approval)


PO Supplier Assigned




Export – Notes/Discussions



Called Interface

New Discussion Created


Note with this interface that ClickHome will provide its data to the client provided interface method and this includes the Type and Purpose codes.  Planning is also required here to determine how these are to be translated into the client systems.