Page 1 of 1

Sales Lead Management - CRM

PostPosted: Wed Mar 09, 2016 4:24 pm
by red1
I am now embarking on a new plugin for iDempiere that follows from what Adaxa tried to do here ... bce9/#677b

sales dashboard screen.png
sales dashboard screen.png (150.21 KiB) Viewed 25964 times
However after packing in their 2Pack into an old ADempiere361, i noticed that they encroach onto core models such as C_Order.

AdaxaSalesLeadManagement.png (51.14 KiB) Viewed 25964 times
Thus I like to propose an entirely separate plugin without touching core but rich with usability:
1. Dashboard Calendar on double-click can generate ticket for Sales Rep to follow up.
2. CRM Ticket goes through the Sales Funnel process.
3. At right stage, Business Partner record is created automatically without the Sales Rep need to do that manually.
4. The record will also pick up other actions such as credit value, visits, responses. They be stored in a new tab model under BPartner window
5. Sales Order from Proposal thru Quotation will also be automatically generated at the right CRM action.
6. Email reply can be used as direct to CRM model without need to manually return to Dashboard calendar.

Re: Sales Lead Management - CRM

PostPosted: Sat Mar 19, 2016 1:47 pm
by red1
Done a window model (pack in 2Pack zip from SF), which updates the present Sales Management menu, adding Request linked to the Lead (AD_User) tab. When you click on the front dashboard calendar, it will open a Request ticket preset to Sales Lead type. You can enter your new business contact data as guided in the screen below. C=ClientName, K=SearchValue, T=603-178869-378, A=Address1, A2=Address2,

CalendarRequest.png (59.8 KiB) Viewed 25891 times
Upon save, as a new record, it will create for you a new Lead record and Request attached at the Sales Lead Dashboard window. This creation is still pending, in a day or two.
Next will be when you are handling the leads, anyone set to 'convert' will also converted to an Opportunity for you. The SQL logic at the Chart for Sales Pipeline (see chart image at the top of this thread) is as follows:
Datasource 1.. SQLFrom: C_Opportunity o
INNER JOIN C_SalesStage s ON (s.C_SalesStage_ID=o.C_SalesStage_ID)
.. SQLWhere: o.IsActive='Y' AND s.IsClosed='N 'and o.SalesRep_ID=101
.. CategoryColumn: s.Value || '. ' || s.Name
.. ValueColumn: sum(o.OpportunityAmt)
.. Table: C_Opportunity
.. KeyColumn: C_Opportunity_ID
>DataSource 2 .. ..ValueColumn sum(o.OpportunityAmt * o.Probability/100)

Re: Sales Lead Management - CRM

PostPosted: Sun Mar 20, 2016 5:56 pm
by red1
Made an initial release of plugin with embedded 2Pack. Upon install, you can go to the Dashboard calendar and make a Sales Lead pop up and save as in previous post. Then the new lead or prospect AD_User account is created linked to the SalesRep. On zooming into the pop up again sends you to the Lead window with the Request as sub tab.

Zoom2Lead.png (70.29 KiB) Viewed 25881 times
The Sales Rep Dashboard with its Lead tab view also shows the created record.

DashBoardLeadView.png (37.51 KiB) Viewed 25880 times
The user can also login using its email and password is also same email name. I am now working on that BPartner is also created alongside and its credit information can be passed from the Request summary box.
The source is pushed to

Re: Sales Lead Management - CRM

PostPosted: Thu Mar 31, 2016 11:08 am
by red1
After the Lead is Converted, it will auto-generate an Opportunity record with Initial Stage, Probability from it, ExpectedClostDate and ExpectedAmt set.

ConvertedSalesLead.png (76.67 KiB) Viewed 25811 times
Then the Dashboard has some magic to show.

DashboardAfterConvertLead.png (70.16 KiB) Viewed 25811 times
Next I will create an entirely new Campaign Process which sends Email blast to Leads, with Import Leads from Excel table, work on the Leads as Activities. This will greatly align with CRM marketing core function.

Re: Sales Lead Management - CRM

PostPosted: Thu Mar 31, 2016 10:54 pm
by red1
Related items such as Interest Area, Marketing Campaign, MailTemplate and SendMail are moved into the same menu for closer use. So please take note if you been using them elsewhere. They can still be moved back to their original locations. I am following the principle of not touching or avoiding any change in core. So I will write a manual on how they be used. I will make an InfoWindow to connect the right data sets together from their data sets. More automated event triggers will be created later to reduce redundant work.

CRMMenu.png (27.58 KiB) Viewed 25797 times
Essentially, the user conducts campaigns by sending out emails to leads via the SendMail with pre-set MailTemplate to a list of registered Interest users or a chosen BP category. The sent out mails will be listed as a sub tab under MailTemplate. I will make a trigger for the event to also create activity records that are listed under the DashBoard and Lead windows.

The Campaign window keeps a list of opportunities (read-only). There seems no corelation of this window with the rest, so i have to think of a connection.

Re: Sales Lead Management - CRM

PostPosted: Fri Apr 01, 2016 10:16 am
by red1
There is a customer window which has a Request sub-tab too. So I forgot to save the Request model to keep the C_BPartner_ID, and now that is solved. When a new CRM ticket is made, you can see the Request under the Customer window. So the Customer window will group all future request tickets. But remember they have the IsProspect flag set to true. When they make their first Order, then I will make a trigger to drop that flag.

Also I made the SalesLead RequestType to be color-coded as green.

RequestColorGreen.png (29.62 KiB) Viewed 25789 times
I made an interim movie here

I also removed the SalesRep_ID = 101 hard code that now works with #AD_User_ID@ in the ChartDataSource setting. Patch is in the 2Pack and also sent to JIRA. Then i tried assigning a Campaign to the Opportunity. Below is how it turns out. Working fine.

TotalExpectedCampaign.png (52.05 KiB) Viewed 25776 times
The Total is the OpportunityAmt. Expected is the Probability factor applied to it. Data test continues.

Re: Sales Lead Management - CRM

PostPosted: Fri Apr 01, 2016 6:30 pm
by red1
While checking around, I found out that Paul Bowden of Adaxa has done a process to convert Leads. It is called from the process icon in overhead menu bar.

ConvertLeadProcess.png (73.49 KiB) Viewed 25775 times
When clicked, it pops up a dialog box.

ConvertDialog.png (61.55 KiB) Viewed 25775 times
It already has the needed settings, so I have to revert the creation of BPartner record and leave it to this process. Also no need for the Convert event. I tested this and it takes care of them.

ConvertedOK.png (25.5 KiB) Viewed 25775 times
Changes will be committed.

Re: Sales Lead Management - CRM

PostPosted: Sat Apr 02, 2016 6:58 am
by red1
Hiep LQ pointed me to which puts the background of how this module got into core. The PDF documentation links there are important reference. I be following them closely. The above latest changes are now uploaded into

SourceForge.png (101.15 KiB) Viewed 25759 times
Made a new follow up movie to update the audience.

I also made the Sales Stage follows Adaxa reference PDF.

SalesStage.png (82.44 KiB) Viewed 25754 times
You can edit this table but login as SystemAdmin as it is used for all clients.

Re: Sales Lead Management - CRM

PostPosted: Mon Apr 04, 2016 10:36 am
by red1
When you made a new Lead, from the Request Calendar on the main Dashboard panel, and you click into it to the Lead Window, you can go to the Tab Activity to begin setting Meeting.

CreateActivity.png (96.23 KiB) Viewed 25729 times
Ensure that the Start and End DateTimes are set in order to let the generated Request appears in the Calendar. Your Description will be prefix with 'Meeting with '+UserName and written to Summary. You can edit further in the resulting Request window.

GeneratedRequest.png (50.24 KiB) Viewed 25729 times
You can click on that and it goes back to the Lead>Request tab showing it as your second request ticket. This tickets can be closed as normal requests will keep you and your supervisor in the loop. Note that i have changed the colors a bit for clearer viewing. You can change colors at the SystemAdmin > RequestType window.

Next I will look at the mail regime, where as emails been sent out during Mail Campaign can be replied and we want those emails tracked, and their canned responses analysed and help the sales team to plan follow ups.

But i also hit on a new idea this morning:
1. What if when you click on the Dashboard Calendar you are able to generate a Sales Order for that Lead on the fly?!
2. This is how it can be done: You are able to choose a RequestType = Sales Order and put in C= or K= to get the Lead record, P=<Product>, Q=<Qty>,$=<negotiatedprice>
and it will find the Lead record, generate a Sales Order (convert the Lead to Opportunity if isn't so!) under the Opportunity window > Orders tab!
The Request record also has an Order link so that will be used to associate with it. Later an InfoWindow can be created, using Aladdin!:
ColumnSet:d.IsPaid* (* to mean criteria column in Info Window!)
This Info Window will sieve through tickets to see their orders status whether shipped, invoiced or paid at one glance.
Not only that:
3. When a shipment is done and/or an Invoice is finally settled and paid, the Sales Order ticket will then be closed! In this way the company can keep track of leads, meetings and orders until paid for.

Re: Sales Lead Management - CRM

PostPosted: Mon Apr 04, 2016 7:56 pm
by red1
The Request Sales Order is done! This time, after creating a Sales Lead Request, you can right away issue a Sales Order on the fly, or even later when the Lead is converted into an Opportunity. Below is how you do it. Make sure you select the Type to be Sales Order.

RequestSalesOrder.png (44.47 KiB) Viewed 25724 times
You can even do it with just C=BPName,P=ProductName without Qty which will be defaulted to 1. $=price if included will force a price usually discounted from the actual price. But first make sure your BP Group has a pricelist or else you will hit an error and nothing is saved (which is good as you do not want rubbish saved.)

GroupBPPriceList.png (67.59 KiB) Viewed 25724 times
When you go to your Sales Order window you can see it there.

RequestCreatedOrder.png (132.95 KiB) Viewed 25724 times
I have committed the code and binary for now for anyone to test it. I also made the Request to save the Order ID so you can trace easily. What is special with my work here, is that i avoid touching any core model.

Re: Sales Lead Management - CRM

PostPosted: Wed Apr 06, 2016 2:29 pm
by red1
Now we can create Sales Order on the Fly! I am now refactoring the code a bit and writing a tutorial. Will commit the code in a while.

Tutorial.png (223.35 KiB) Viewed 25667 times
Tutorial guide and plugin at
It is so fast that the movie is over in few minutes:

Re: Sales Lead Management - CRM

PostPosted: Sun Apr 10, 2016 1:46 pm
by red1
Been troubleshooting the wonderful Charts done by Paul Bowden (ADAXA) and having a bit of trouble with the Sales YTD Chart. Referring to ... _chart.htm
helped me to use as test case and it works in his code. I realised that you need many series of data before it can draw a graph. So I made more completed SOs as the formula for the chart to register is:
Code: Select all
SELECT sum(o.GrandTotal), (C_Order_ID IN (SELECT o.C_Order_ID FROM C_Order o WHERE o.DocStatus IN ('CO','CL') AND o.AD_Client_ID=11 AND o.IsSOTrx='Y' AND 'Current' = 'Current' AND  TRUNC(o.DateOrdered, 'MM')  = TO_DATE('2016-04-01','YYYY-MM-DD') ))
And something showed up.

SalesYTDSeries.png (47.83 KiB) Viewed 25600 times
This also gives me an idea to make the Sales Order's Date Ordered and Date Promised to follow the Request Start and Complete planned dates.

RequestOrderDates.png (34.78 KiB) Viewed 25600 times
Note the dates are copied over. In this way, the user can date the SO according to the Calendar directly.

SalesOrderDates.png (58.39 KiB) Viewed 25600 times

Re: Sales Lead Management - CRM

PostPosted: Mon Apr 11, 2016 4:48 pm
by red1
You may wonder how to make the BarChart nicer. Well, it is supposed to be StackedBarChart as chosen in the Chart model. So i went into the code and solved it and submitted a patch for acceptance into core:

CorrectedStackedBarChart.png (53.46 KiB) Viewed 25589 times
The correct one is on the right. It certainly looks sexier. Also note that you can double-click on any bar and it will open up the window showing the exact data window that the bar represents!

Re: Sales Lead Management - CRM

PostPosted: Mon May 16, 2016 6:12 am
by red1
I have improved the use of Chart via a ChartMaker that automatically generates 3DStackedBar Charts for any model particularly the Sales YTD to show details according to Product or Customer breakdown and even by country, region, UOM or just any criteria. Go read here for more viewtopic.php?f=45&t=1826

Now I be showing how to setup the email communication. Request CRM since Compiere days already has an email processor to send out notifications of Request status. For example here is one that i did with the Sales Order on the fly:

SalesOrderRequestEmail.png (57.28 KiB) Viewed 25225 times
In the following post i will show from the start how to set this up properly step by step. You have to create a test email so in my case i created an email It is advisable not to use your own personal email as the security has to be lowered for gmail to be accessed from a third party such as our ERP. Of course an expert can go in and ensure secured email access, but for now, off the shelf, this is the base setting.

Re: Sales Lead Management - CRM

PostPosted: Mon May 16, 2016 6:53 am
by red1
After creating your gmail account, you have to enable IMAP in it.

EnableIMAP.png (118.66 KiB) Viewed 25180 times
You should also turn the conversation off so that each email reply does not bunch with the previous and get processing entangled up.

GmailConversationOff.png (82.3 KiB) Viewed 24895 times
That is all for now on gmail, and go to your iDempiere client to setup Client email:

ClientEmailSetup.png (66.12 KiB) Viewed 25180 times
If you get it all correct, pressing the Test button will give:

ClientEmailTest.png (33.86 KiB) Viewed 25180 times
Next we enable the Request Processor engine:

RequestProcessorTurnOn.png (47.96 KiB) Viewed 25180 times
You will see that it is inactive, so just check the Active box. Now you can go and make a Sales Order on the fly or any other Request Type. You will get an email instantly:

SalesOrderRequestEmail.png (84.04 KiB) Viewed 25180 times
You can receive updates in your email as set in the Request Processor to every 15 minutes. You can change the timing. However the Request Processor engine only kicks in after a restart. So do that and go to the root host page of iDempiere http://localhost:8080/:

RootHostPage.png (191.04 KiB) Viewed 25180 times
Click on the toolbox icon on the top right to open up the Server Monitor:

ServerMonitor.png (84.39 KiB) Viewed 25180 times
You can see among the processors running is the Request processor. Note that this depends on your tickets settings under Request Type rules. That is it! Next I will show how to setup the email from the Setup panel and System side so that you receive notification each time someones steals your server for a spin! In the end I be tackling how to import emails and process them. That is part of the new enhancement to the SalesLead CRM work I am doing under sponsorship by SYSNOVA.

Re: Sales Lead Management - CRM

PostPosted: Mon May 16, 2016 4:11 pm
by red1
Now to setup server-side email service, is done during / or from Eclipse. You can run this many times and does not affect present system nor database. It will do a test and save the email credentials in properties file. But first there is a bug, which i found and asking Hiep LQ who then solved it here So if this patch is not applied by Carlos Ruiz in core, you have to apply it manually in Eclipse first.

SetupEmailProperties.png (54.33 KiB) Viewed 25135 times
This will store your email password too in the So take note and hide it in a secured location. You will get this email in your mail box:

ServerTestMailNotice.png (145.43 KiB) Viewed 25132 times
. I am not sure if this setup is needed as the client setup before this seems sufficient to move the Request Processor and the following step is to setup the same on the System Client:

SystemClientEmailSetup.png (92.19 KiB) Viewed 25132 times
After this is done, when you restart your server you should get a notification. Here is my mail box with the 2 notices - 1 for the System Client email setup test and the idempiere-server startup:

2EmailsSystem.png (24.09 KiB) Viewed 25132 times
This is kinda groovy as it gives you proper time-stamp official notice that your server was started.

Re: Sales Lead Management - CRM

PostPosted: Tue May 17, 2016 5:13 pm
by red1
Now we run the Request Email Processor which reads from a mailbox to import into the Request table. In my i filter email that contains a key word, 'SalesLeadCRM' to be sent to a folder labelled 'request'. I also created a 'processed' folder for this purpose.

RequestEmailProcessorSetting.png (33.8 KiB) Viewed 25075 times
The EmailSvr hard code will set to port 993, so you can use just without the port. However if you are using another email server with different port then the :<port> will not be set until this patch is applied
RequestEmailProcessSuccess.png (19.66 KiB) Viewed 25075 times
SucessRequestEmailLog.png (73.33 KiB) Viewed 25075 times
NewRequestFromGmail.png (78.93 KiB) Viewed 25075 times
GmailUpdated.png (71.33 KiB) Viewed 25075 times
This is the raw off the shelf handling as done in the code by Carlos Ruiz and Hiep LQ before this. I am now studying the code to see how to improve it further. At the moment the code checks for same Message-Id to cluster together as a same request. I want to make the Requests to synch via its DocumentNo/Message-ID so that they are all gathered as updates to the same customer ticket. That will be convenient for the Sales Rep to check the email conversation sent by the marketing campaign and the reply from the prospect/customers.

Re: Sales Lead Management - CRM

PostPosted: Wed May 25, 2016 9:07 am
by red1
Now that is solved! When an incoming email is processed, it will take its LastResult as a new RequestUpdate so that you can see all updates in a single Request instead of ignored. Not only that, I have made the anonymous Request to take the email address's User details, so that under the Lead (User) window you can see the new Request added to it. In this way, the SalesRep can really keep track of all communications with the prospect in a single window. This basically concludes our marvelous improvement to Adaxa's Sales Lead CRM.

EmailUpdatedRequest.png (80.25 KiB) Viewed 24932 times
Here we see the latest record of the 4 updates received under the same email Request.

EmailRequest2Lead.png (64 KiB) Viewed 24932 times
The new Request attached to the Lead window. However the display is not helpful. In hindsight, I should include a DocumentNo and LastResult fields also. Will adjust that and update the 2Pack later. For now, i only pushed the latest code changes so that the above can be replicated.

Re: Sales Lead Management - CRM

PostPosted: Thu May 26, 2016 4:52 pm
by red1
The CRM experience should be more exciting now with the fuller email integration. LastResult with Document IDs displayed.

LeadRequestEmailChanged.png (88.05 KiB) Viewed 24911 times
All source committed to, binary with 2Pack and updated tutorial to

Re: Sales Lead Management - CRM

PostPosted: Sun Aug 06, 2017 5:02 pm
by red1
I made a POC of another Killer Concept of E2E or Email to ERP, where a user can rely entirely on Email and that means been mobile, sending an email to the ERP and it responds obediently!

Here i sent a coded message (starts with #) without going into a desktop and login directly into iDempiere. The email is fetched by iDempiere remotely too with asynchronous advantage. Meaning iDempiere need not be on the cloud or hooked via any web services. Just plain old but killer email.

EmailSent.png (20.31 KiB) Viewed 20470 times
With the Request EMail Processor (setup to run on Scheduler) and GardenWorld Request Processor running in the background, where i setup to 10 minutes timer, i got a reply back on my mobile email, within 10 minutes to mean mission accomplished!

EmailReceived.png (51.97 KiB) Viewed 20470 times
When you check on your iDempiere desktop, you will also see a confirmation in a highly visible manner.

OrderCreated.png (40.06 KiB) Viewed 20470 times
The possiblities are endless. A user can send coded messages to the ERP such as requesting for shipment status. The sender email when registered as a user in iDempiere will be tagged to receive replies.