CRM Enhancement Specification for xTuple ERP 3.5

Overview

This document describes a large set of enhancements and modifications to the CRM module of xTuple ERP. The purpose of these changes is to create a dashboard or workbench ("My Workbench") that will allow a user to view and manage CRM activities, accounts and contacts from a single window, and to enhance the issue-tracking capabilities of xTuple so that it rivals dedicated issue-tracking applications, such as Mantis.

Functional Requirements

New CRM priority list based on conversation with John R. 1/7/10

 

Features for this release:

1. CRM Workbench and Parameter Widget
   1. Create new CRM workbench screen
      1. combine activities list into a single screen that can be filtered by owner
      2. separate tabs for activities, accounts and contacts owned by user
      3. allow user to filter activities, save last filter state when window is closed
      4. Allow user filter sets to be saved and reused (THIS MAY NOT MAKE IT)
      5. on activities tab, add link to show a roll-out calendar (THIS MAY NOT MAKE IT)
2. Enhancements to Incident Screen
   1. Add Reporter field to Users section
   2. Add Subscribers (Monitors) tab
      1. Create a new tab to manage subscribers
      2. Add function to query in EDI profile to create a “subscribers” token to add all subs to the cc list for updates.
   3. Update Relationships
      1. Move relationships (Item and Lot/Serial) to documents tab (with files and images)
      2. Move Receivables to header section.
      3. Move To-dos to Documents/Relationships tab
      4. Create new docrel table to define inter-document relationships
      5. Port existing relationships (Items and Lot/Serial) into the new docrel table
      6. Allow for different relationship types (parent, child, related to, duplicate of
3. Recurrence on Tasks
   1. New instances of the task will be created with due dates set to the desired interval.
   2. New instances of any child tasks associated with the recurring task will also be created.

NOTE: The relationships info will be integrated into the existing Documents widget, which will make it apply anywhere that the Documents tab is currently used.

Features that will not make it into this release:

1. Add standard fields to all activities types (create date, modified date, owner, status, etc)
2. Standardize statuses -- allow user-defined statuses for all activity types
3. Standardize priorities -- allow user-defined priority for all activity types
4. Track history of changes to all activity types
5. Combine tasks and todo, rename all as Task
6. Other updates to Tasks, Opportunities, Projects (Other than the Documents tab)

New Terms and Definitions

Task

• • In the current product we have both “tasks” and “to-dos”. These are not differentiated in a meaningful way, so they will be combined into the single category of Tasks. Tasks will be related to other CRM components, including Projects, Incidents and Opportunities. A task describes an action that can be assigned to a user.

Related Existing Functionality

xTuple already includes the concept of Incidents, which are analogous to issues in Mantis. Incidents are a CRM construct designed to allow customers or agents to report problems, to assign them to internal resources for resolution, and to track their progress until they are resolved.

xTuple also has separate screens for Incidents, Opportunities, Projects and Tasks, but not a single screen, or workbench, that pulls the information together.

Similar and Related Request

• 0008818: Updated date column on Incident Workbench
  http://www.xtuple.org/issuetracker/view.php?id=8818

• 0008610: Contact information is not viewable on the To-Do list
  http://www.xtuple.org/issuetracker/view.php?id=8610

• 0005419: Assign incidents to the user who created it
  http://www.xtuple.org/issuetracker/view.php?id=5419

• 0005363: When viewing incident detail from a task, need to see all of it
  http://www.xtuple.org/issuetracker/view.php?id=5363

• 0009746: Make project task numbers auto generate
  http://www.xtuple.org/issuetracker/view.php?id=9746

• 0009763: "Documents" Functionality for Projects
  http://www.xtuple.org/issuetracker/view.php?id=9763

Conflicting Features

What features currently exist and what feature requests have we received that might conflict with the feature described here?

 N/A

User-Level Functionality

Changes to CRM menu

 

Add CRM Workbench link at top of menu, above Incident

Add icon for CRM Workbench to the main application window, in the CRM section.
 

Window Changes

New CRM Workbench

 

This is the main CRM Workbench window. It combines information from all the CRM content types into a single screen that can be filtered and sorted.

STATE MEMORY

The CRM Workbench will remember the last state it was in when the user closes the window or the app and will reopen to that state.

WINDOWS THAT WILL BE REPLACED BY MY WORKBENCH

The My Workbench screen will not replace any current screens.

USER FILTER

The user filter at the top of the screen defaults to show the current user as the selected user. Users with Admin privs will have the ability to select all users or to switch to a different selected user.

TOP LEVEL TABS

The CRM Workbench has three top-level tabs, that allow users to select to work with Activities, Accounts, or Contacts.

SEARCH

The Search box allows the user to input text and the screen will search for that text in the following fields: Description, Notes, Comments

SHOW

The next box allows the user to select with types to display in the list. By default, Tasks, Incidents, Opportunities and Projects will be selected, and Completed, Subscribed and Inactive will be deselected.

CALENDAR

The Calendar button will expand the screen to show the calendar. It will display activities matching what appears in the Activities List.

MORE FILTERS

This button will open up a section for adding additional custom filters (see Parameter Widget spec).

SELECT SAVED FILTER SET

This dropdown list will show a list of all saved filter sets. A filter set is a collection of filter criteria that was entered by the user and saved with a custom name. When a named filter is selected, it will be applied to the list.

ACTIVITIES LIST

This section displays all the filtered results. There is a default selection and order of columns, but columns may be added or removed, resized and reordered by the user. The screen will remember the last configuration of the column list for the user and will display it that way the next time the screen is opened.

User can left-click and drag column headers to reorder columns in the display.

Right-click on column header:
 

• Reset all widths
  ---------------------------------------
• Do not remember widths
• Do not remember sort order
  ---------------------------------------
• [column list―check marks next to active columns]
   

Right-click on item in list:

•  Mark as completed [if task]
  ---------------------------------------
• Copy all
• Copy row
• Copy cell
  ---------------------------------------
• Export contents

  

AUTOMATICALLY UPDATE

When this is selected, the query will run at a regular interval, and the results list will automatically update to show any new results.

TOTAL #

Displays the total number of activities in the list after all currently selected filters are applied.

BUTTONS

Close – closes the window

Query – executes the currently selected filters and rebuilds the activities list

Print – Prints the contents of the filtered list of activities

Reset – resets the window to it's default state

New – show a drop down boxpop to allow user to select to create To-Do, Task, Incident, Opportunity or Project

Edit – open the currently-selected item for editing

View – open the currently-selected item for viewing
 

CRM WORKBENCH WITH MORE FILTERS OPEN

See Parameter Widget

  

PRIVILEGES AND ACCESS

We'll need a set of rules to govern what activities a user can see on the CRM Workbench screen.

Possible filters:

• View owned activities

• Edit owned activities

• View all activities

• Edit All activities

INCIDENT SCREEN

What's different here?

Head section: Added Reporter field

Moved the Receivables section out of the Relationships tab, since it will not be possible to link to receivables using the new standard Links|Internal tab.

Tabs section: Changed tab order, added Characteristics, Added Links (changed from To-Dos), added Subscribers

INCIDENT SCREEN, LINKS TAB

 

The Links tab will have two radio buttons. The first is for displaying Internal document links, the second for external (URL) links. 

The types of Links to internal documents are:

• Parent of
• Child of
• Related to
• Duplicate of

The "Open Doc" button will open the selected document for editing.

The "New" button will drop down a list of all possible doc types that can be linked. Selecting a doc type from the list will open a window to add a new doc of that type. When the doc is saved, a link will be added to the Incident.

• Incident
• To-Do
• Task
• Project
• Opportunity

The "Attach” button will open a screen for adding an Internal links to an existing document to the list of relationships.

 

• Address,
• BBOMHead,
• BBOMItem,
• BOMHead,
• BOMItem,
• BOOHead,
• BOOItem,
• CRMAccount,
• Contact,
• Customer,
• Employee,
• Incident,
• Item,
• ItemSite,
• ItemSource,
• Location,
• LotSerial,
• Opportunity,
• Project,
• PurchaseOrder,
• PurchaseOrderItem,
• ReturnAuth,
• ReturnAuthItem,
• Quote,
• QuoteItem,
• SalesOrder,
• SalesOrderItem,
• TransferOrder,
• TransferOrderItem,
• Vendor,
• Warehouse,
• WorkOrder

 

    [INSERT SCREEN FOR ADDING RELATIONSHIPS]

The "View" button will open an existing relationship for editing

The "Remove" button will open a dialog box asking "Remove this relationship?" [Yes/No]

 

INCIDENT WINDOW, SUBSCRIBERS TAB

 

The Subscribers tab is where you manage the people who will be monitoring the issue, beyond the users and contacts who are already accounted for (owner, reporter, assigned to).

The “New” button will open a Subscriber window where the user can select a User or Contact to subscribe to the issue..

The "View" button will open the subscriber for editing.

Edit...

The “Remove” button will pop up a window confirming that the user wants to remove the subscriber

 

TASK WINDOW

 

What's different:

Combining aspects of the current “task” entity with the current “to-do” entity.

Priority and Status options are configured in Master info.

Due date can be arbitrarily set by the user, but all other dates (e.g., started, completed, etc) will be written to the history tab when the Status field is changed.

New "Recurring" section allows the activitiy to be scheduled to recurr. What does this mean, exactly?

• New instances of the task will be created with due dates set to the desired interval.
• New instances of any child tasks associated with the recurring task will also be created.

Adding Characteristics tab

Remove Status tab, Add “T&E” tab with “Hours” and “Expenses” fields (although these are still not attached to anything).

Adding History tab. Note that all system comments should go into History tab, not Comments tab. This means that the history tab will contain the dates for all modifications of the Incident.

Adding Documents tab.

 

TASK WINDOW, RELATIONSHIPS TAB

 

Relationships will be defined as they are for Incidents. 

 

PROJECT WINDOW

Changes to Project Window:

Add Status field to header

Move “Description” down to tabbed area and call it “Notes” (more standardization)

Add tabs for Relationships, Characteristics, History, Documents, Subscribers and Alarms

Remove tab for Tasks. Tasks will appear on the Relationships tab.

The Relationships tab works the same as it does on the Incidents screen
 

OPPORTUNITIES WINDOW

What's new on this page:

To-Dos tab replaced by Relationships tab.

Relationships tab works like others in the CRM group.

Add History tab

Add Documents tab

Add Subscribers tab

Add Alarms tabs

 

CRM WORKBENCH, ACCOUNTS TAB

 

The Accounts tab will replace the Accounts List and Search windows.

The Search box searches the fields that are selected in the Search Options.

Show Search Options opens the section of the page where the user can select fields to search on.

The “Show” section allows the user to select to view All, or only certain types of CRM accounts.

 

ACCOUNTS TAB, SHOW SEARCH OPTIONS SELECTED

 

This window shows the search options expanded, and the default selections.

 

CRM WORKBENCH, CONTACTS TAB

 

The Contacts tab replaces the Contacts List and Search windows.

 

CONTACTS TAB, SHOW SEARCH OPTIONS SELECTED

 

This is the Contacts tab with the Show Search Options section expanded. The window shows the default selections for searching contacts.

Report Changes

Existing reports, listed below, may need to be updated to reflect any changes to tables or architecture after CRM update.

• Order Activity by Project

• Incidents by CRM Account

• Tasks by User and Incident

Batch Manager Changes

What functionality do we expect to add to, remove from, or change in the Batch Manager?

• Subscribers and Alarms tabs will exist on Incidents, Opportunities and Projects. These may all need to interface with Batch Manager to send notifications.

Usability Considerations

We are introducing a couple of new interface features with this update, described below.

• Users may experience some confusion due to the new way of setting filters on the workbench activities list. In the current product, all available filters appear at the top of the window, above the list. In the new system, most filters will be hidden until the “More filters” button is pressed. Once the filters are opened, the user must configure each one in a new way. The concept of saving filter sets is also new to xTuple.

• The subscribers concept is also new to xTuple, although it exists in Mantis, where users can “monitor” an issue. We are adding subscribers to Incidents, Opportunities and Projects in xTuple as a way to create a notification list for changes to these types of content. Because this is new, it may cause come head-scratching.

All of these issues may cause some confusion for users. It would be a good idea to include some sort of help file that focuses on explaining the new filter management system, especially since we are thinking that we'll roll this new system out into other areas of the application.

Problems and Alternatives

It is possible that users will complain that we are hiding filtering options that they were used to finding in the header. People may be upset that we are fixing something that, in their opinion, wasn't broken. How do we counter this?

One option would be to preserve the old header as an option for users who still want to work that way.

Another would be a user setting that automatically expands the More Filters option on any window where it exists. This option is probably better, as it wouldn't require us to maintain two different code sets. We'd just have to define an option switch for the user.

 

 

Internal Design

Now that we have defined what we have to do and how we think it should look to the user, we can describe the internal structure of the application. If possible, outline the architecture of the feature implementation here. Then in the sections below describe the changes and additions to the application to create this architecture.

 

Basic Algorithms

Are there particular algorithms required to implement this feature? If so, describe them here.

If you are going to outline a particular algorithm in pseudo-code
    put it in a programlisting
    try to limit lines to approximately 80 characters
    try to limit programming constructs while still being clear about intent
end if // a programming construct that may be unavoidable to maintain clarity

 

Custom Widget Changes

Do we expect to modify existing custom widgets to support the windows described above?

What new custom widgets will we need? Will they be derived from existing widgets? What behavior do we expect from them?

 

Schema Changes

What changes do we anticipate making to the database schema? New tables? Views? Indexes?

 2 new schemas: doc and act.

 

doc schema

All entities in xTuple have some common characteristics that allow them to function as a virtual file system, we will introduce a new table called “doc”

• doc.doc
  • doc_id serial NOT NULL,
  • doc_number serial NOT NULL,
  • doc_name text,
  • doc_active boolean DEFAULT true,
  • doc_descrip text,
  • doc_create_date date,
  • doc_last_update timestamp without time zone NOT NULL DEFAULT now(),
  • doc_status character(1) NOT NULL DEFAULT 'N'::bpchar,
  • doc_doctype_id integer NOT NULL,
  • doc_notes text,
  • doc_owner_username text,
  • doc_creator_username text,

There will also be some new tables related to the doc table, to manage doc types and statuses, as well as relationships between docs, subscriptions to docs, and history of changes to docs.

• doc.docrel
  • docrel_id serial PRIMARY KEY NOT NULL,
  • docparent_id integer REFERENCES doc.doc (doc_id) NOT NULL,
  • docchild_id integer REFERENCES doc.doc (doc_id) NOT NULL,
  • docrel_type character varying(1),

• doc.doctype
  • doctype_id serial NOT NULL,
  • doctype_name text NOT NULL,
  • doctype_descrip text,
  • doctype_active boolean DEFAULT true,
  • doctype_table_ref text NOT NULL

• doc.doctypestatus
  • docstatus_id serial NOT NULL,
  • docstatus_doctype_id integer NOT NULL,
  • docstatus_status_id integer NOT NULL

• doc.docstatus
  • status_id serial NOT NULL,
  • status_name text NOT NULL,
  • status_descrip text

• doc.subs
  • subs_id serial NOT NULL,
  • subs_doc_id integer NOT NULL,
  • subs_subscriber_id integer,
  • subs_subscriber_type text 

• dochist [NEEDS TRIGGER FUNCTIONS in the doc table]
  • dochist_id serial NOT NULL,
  • dochist_doc_id integer NOT NULL,
  • dochist_change character(1),
  • dochist_timestamp timestamp without time zone NOT NULL DEFAULT now(),
  • dochist_username text NOT NULL DEFAULT "current_user"(),
  • dochist_descrip text

act schema

Make a top-level data structure for activities called act. The act table will inherit the doc table.

• act.act
  • act_crmacct_id integer,
  • act_cntct_id integer,
  • act_assinged_username text,
  • act_recurring boolean DEFAULT false,
  • act_recurring_interval integer,
  • act_recurring_type text,
  • act_recurring_until date,
  • act_recurring_act_id integer

Make tables for the specific activity tables. These will inherit the act table (and the doc table).

• act.op
  • op_opstage_id integer,
  • op_opsource_d integer,
  • op_optype_id integer,
  • op_probability_prcnt integer,
  • op_amount numeric(20,4),
  • op_target_date date,
  • op_actual_date date,
  • op_curr_id integer

• act.proj
  • proj_start_date date,
  • proj_due_date date,
  • proj_assigned_date date,
  • proj_completed_date date,
  • proj_so boolean,
  • proj_wo boolean,
  • proj_po boolean

• act.inc
  • inc_timestamp timestamp without time zone NOT NULL DEFAULT now(),
  • inc_inccat_id integer,
  • inc_incseverity_id integer,
  • inc_incpriority_id integer,
  • inc_incresolution_id integer,
  • inc_lotserial text, -- inc_lotserial is deprecated
  • inc_ls_id integer,
  • inc_aropen_id integer,
  • inc_item_id integer

• act.task
  • task_hours_budget numeric(18,6) NOT NULL,
  • task_hours_actual numeric(18,6) NOT NULL,
  • task_exp_budget numeric(16,4) NOT NULL,
  • task_exp_actual numeric(16,4) NOT NULL,
  • task_seq integer NOT NULL DEFAULT 0,
  • task_start_date date,
  • task_due_date date,
  • task_assigned_date date,
  • task_completed_date date

Views

• act.incident
• act.opportunity
• act.proj
• act.relationships
• act.task
• act.subscriptions

Functions

• act.getincidentid.sql
• doc.deletesubs.sql
• doc.getdocid.sql
• doc.getdocrelid.sql
• doc.getsubnameid.sql

 

The new whatever-you-call-it table

Column Name	Description	Data Type	Comments
field1	Describe me	data_type	how it's used
foreign_key	Describe me	integer	foreign key to other_id
enum_field	Status or similar field	character(1)	Can take one of the following:  * A value  * Different Value  * Final Value

 

What privileges do we need?

Privileges for feature
Name	Description
MaintainWhatever-you-call-it	Can Add/Edit/Delete stuff
ViewWhatever-you-call-it	Can View stuff

 

Stored Procedure Changes

What stored procedures do we expect to create, eliminate, or change?

 

Performance Considerations

How will this feature impact the performance of the application overall?

 

Error Handling

What internal errors do we anticipate might occur?

How can we best hide them from the user, prevent them, or fix them automatically?

 

QA Considerations

Does this feature require any special tools or data to test? Is it testable (some features may be hard to reach from the user level)?

What are some anticipated areas of concern that require special attention?

 

Documentation Considerations

Is there a significant documentation impact?

Does the feature require a standalone essay or only field-level descriptions?

 

Release Considerations

What release would we like to target?

What is the potential impact on a release if we don't finish in time?

What happens if we only get some of the functionality done?

Are there any pieces that would be useful on their own?

Are there any dependencies on other features or applications (such as Qt 4 or a particular release of OpenRPT)?

What is the anticipated impact on users, particularly those who have customized the sources, reports, and database schema?