About Accsys Consulting

Accsys Consulting is a Canberra based company established in 1992 to provide specialist financial management and information technology consulting services to public and private sector organisations.

We assist clients to develop their customer relationship management (CRM), enterprise resource planning (ERP) and financial management information systems (FMIS), utilising personnel extensively experienced in the implementation and support of Sage Intacct, Sage 300 and Sage CRM.

The directors and staff of Accsys Consulting have been implementing and supporting Sage 300 and CRM for over 20 years.

We have the capacity to assist clients prepare financial statements, liaise with auditors and review grant acquittal statements, utilising personnel who are Chartered Accountants.

We have built our reputation on providing our clients with effective solutions, rapid implementation, and dependable service.

Follow Us On Social
 

How do I summarize, in days, when each Opportunity was last modified?

How do I summarize, in days, when each Opportunity was last modified?

More specifically, this information is usually intended to be outputted as an additional column, in the Opportunity pipeline screen.

How the "Days in Stage" column appears in the Opportunity pipeline.

How the “Days in Stage” column appears in the Opportunity pipeline.


To do this, you’ll need to:

  1. Modify the database view used by the Opportunity pipeline.
  2. Create a translation.
  3. Add the new column to the pipeline screen.

 

Step 1: Modify vListOpportunities

The Pipeline screen uses the vListOpportunities view, to provide the columns for it to display.  In Sage CRM, go to the Administration section, select the Opportunties entity and under the Views tab, update the view definition to include our new field:

CAST((DATEDIFF(hh, wi.WkIn_UpdatedDate, GETDATE()) / CAST(24 AS DECIMAL(18,1))) AS DECIMAL(18,1)) AS 'oppo_daysinstage',

and an inner join to the Workflow Instance table (we’re using the Updated Date on the workflow instance as an indicator of when the Opportunity was last modified; you could just use the Opportunity’s Updated Date, but then that wouldn’t include workflow progression).

INNER JOIN WorkflowInstance AS wi
		ON wi.Wkin_CurrentEntityId = 10
			AND wi.WkIn_CurrentRecordId = oppo_opportunityid

Your view definition should end up looking like the following:

View definition for vListOpportunities.

View definition for vListOpportunities.

Step 2: Add a translation for the new field

To display a user-friendly name for the oppo_daysinstage field, go to Administration -> Customisation -> Translations and create a new translation using the following details:

Parameters for a new oppo_daysinstage translation.

Parameters for a new oppo_daysinstage translation.

 

Step 3: Add “Days in Stage” to the Pipeline screen

For the last step, go to Administration -> Customisation -> Opportunities -> Lists and select the Opportunities List item.  Select the “Days in Stage” field in the Field drop-down, click Add and then save your changes.

Adding the "Days in Stage" field as a new column.

Adding the “Days in Stage” field as a new column.


When you go back to the Dashboard, your Opportunities Pipeline should now display this brand new field!



To request a demo, please complete the form below
To download the product brochure, please complete the form below
Please Contact Me