Simplifying IT, Empowering Businesses

What is Sage 300 Construction and Real Estate (CRE)?   

Sage 300 Construction and Real Estate (formerly known as Timberline Office) is a construction management solution that helps real estate companies to manage their projects and real estate properties.   

It provides total integration of the tool to all the facets of your organization- accounting, job cost, project management, estimating, payroll and property management, etc.  

This is a product from Sage tailored for the Real Estate Industry to manage their project and properties’ end-to-end life cycle.  

Different options to integrate with Sage 300 Construction and Real Estate (CRE)  

There are the following ways to facilitate integration with Sage 300 CRE setup:  

1.  Timberline Data ODBC Driver (provided as part of Sage 300 CRE installer)  

This option allows you to access the only Accounting & Management Data of Sage 300 CRE. Besides allowing real-time reading access to Accounting & Management Data of Sage 300 CRE, it also allows updating specific selected fields’ data.

This article explains how to enable integration between your application with the Accounting & Management Data of Sage 300 CRE and update the value of the ‘Status’ field of ‘Job’ setup in Sage 300 CRE.

2.  Pervasive ODBC Engine Interface  

This option allows you to access the purchase inventory and service management data from Sage 300 CRE. This option only allows you to read the Data and does not allow you to update the same. 

Sage 300 Web API  

This option allows enabling complete and real-time integration with the data of all the modules from Sage 300 CRE. To proceed with this option, you must enroll in the Sage 300 CRE Development Partner Program (DPP). 

Steps to enable integration through Timberline Data ODBC Driver  

Setup the DSN:  

We need to set up the DSN, which our ASP.NET Web API application will consume to read/update the data. Let us discuss how to set up DSN.

1. Click on Control Panel -> Administrative tools -> ODBC Data Sources (32-bit)

2. Go to ‘System DSN’ and click on ‘Add…’

3. Select the ‘Timberline Data’ drive from the list shown in the ‘Create New Data Source’ dialog and click on ‘Finish.’ 

4. Give Data Source Name as per your choice and choose Database Type as ‘Accounting Data Folder’ 

5. Click on ‘Select Folder’ and select the path of the ‘Accounting’ folder of Sage 300 CRE 

6. Click on Options to display the Data Source Options 

7. In Table and Field Naming, choose the ‘Custom Descriptions’ option from the list  

• Custom descriptions

 When you select this option, the descriptive name for tables and fields will display via ODBC (e.g., table name format = MASTER_JCM_Job)  

•  Standard descriptions

When you select this option, the descriptive name for tables and fields displays via ODBC (e.g., table name format = JCM_MASTER_Job)  

•  Dictionary names

Dictionary names are used when you require table & field names, like internal table and field names.

They are less descriptive than the standard description, but they provide the same functionality (e.g., table name format = MASTER_JCM_Record_x)  

The following table exhibits the Accounts Receivable customer field as an example to show how different Field Naming options affect the area that appears:  

Naming option	Display table and field name	Comments
Standard descriptions	ARM_MASTER_CUSTOMER.Customer	No change regardless of custom descriptions for AR Customers. Standard descriptions are always the default descriptions.
Custom descriptions	MASTER_ARM_CUSTOMER.Customer	If the custom description for AR Customer is “client,” the field appears as MASTER_ARM_CLIENT.client
Dictionary names	MASTER_ARM_RECORD_1.CUST	No change regardless of custom descriptions. Internal names always appear in dictionary names.

   Use maximum table segment size.  

The option ‘Use maximum table segment size’ determines how many columns you can include in a table before the table is split into two tables when accessed via ODBC.  

• When you clear the check box, a table that exceeds 255 columns is split into two tables. For example, the Job Cost Master .jcm file Job record is divided into two tables, the JCM_Master_Job_1 and JCM_Master_Job_2.  
• When you select the check box to use the maximum table segment size, the table is not split. For example, the Master. jcm file Job record does not break and is one table, JCM_Master_Job.  

 Note: Some programs, including Microsoft Access, do not support the use of maximum table segment size. Clear the check box when you use DSNs associated with those programs.  

Sample Code  

DSN is set up. Let us see how to use that to enable the communication between your ASP.NET Web API and Sage 300 CRE.  

We will update the value of the ‘Status’ field for the ‘Job’ stored in Sage 300 CRE through the DSN we just set up for the demonstration. Let us see how to do that.  

Set the Connection string as mentioned below in config file  

<add name=”OdbcConnectionString” connectionString=”DSN=Timberline Data;uid=<Username>;Pwd=<Password>;Driver={Timberline Data}” />  

Note: replace the <Username> with one of an actual username and <Password> with a relevant password of that user who is having access to ‘Accounting’ module of Sage 300 CRE. 

Setup the Controller  

Right-click on ‘Project,’ click on ‘Add New Item,’ select ‘Controller,’ and enter the name ‘Sage300CREController.’ 

We will create the ‘OpenJobs’ action (as shown below), which updates the status of closed Jobs (stored in MASTER_JCM_JOB_1 table of timberline) to ‘In Progress.’ 

public class Sage300CREController : BaseController 
{ 
   public HttpResponseMessage OpenJobs(string JobNo) 
  { 
      OdbcConnection todbc = ConfigurationHelper.GetConnectionString(OdbcConnectionString); 
            try 
           { 
               todbc.OpenConnection(); 
                if (!string.IsNullOrEmpty(JobNo)) 
               { 
                   todbc.CreateCommand("Select Status from MASTER_JCM_JOB_1 where lower (Status)= 'closed' AND Job='" + JobNo + "'", DBFactoryCommandType.TEXT_COMMAND); 
 
                   DataTable dtjob = todbc.GetTable(); 
                    if (JobNo != null) 
                   { 
                       todbc.CreateCommand("update MASTER_JCM_JOB_1 set Status = 'inprogress' where Job='" + JobNo + "'", DBFactoryCommandType.TEXT_COMMAND); 
                       todbc.ExecuteCommand(); 
                   } 
               } 
           } 
            catch (Exception ex) 
           { 
                throw ex; 
           } 
            finally 
           { 
               todbc.CloseConnection(); 
           } 
  } 
}   

Note: We have used the inline query for demonstration purposes, which is not recommended for the production environment. Please make necessary changes in your code accordingly.  

This way, you can perform the essential update (for the allowed fields by Sage) using the Timberline Data ODBC driver provided as part of the Sage 300 CRE setup.

Hope you are now thorough with Integrating Sage 300 CRE with ASP.NET Web API. In case you require any further assistance, do not hesitate to get in touch with a DEV IT expert.