Hi,
This is driving me nuts, I have been trying to get the latest communication for a lead and this is the code I am using but the problem is that the communication ID shown in the output is old whereas the date shown in the list is of the latest communication, I have used a similar code in Sales Orders but there I am getting the correct results, is there something wrong with my code or something else.
SELECT com.parent, com.name, max(com.communication_date)
FROM `tabCommunication` com
WHERE com.parenttype = 'Lead'
GROUP BY com.parent
Please note that the com.name column is always showing the most earliest id of the communication instead of the latest.
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/7b784768-da53-4092-8793-3b59424b940d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Yes, you are getting the all cmomunications from Leads not the latest communication from current lead, add a new condition in where:
WHERE con.parenttype = 'Lead' AND con.parent='%s'
and pass cur_frm.docname as paramenter to query
2014-03-28 8:25 GMT-03:00 Addy
<ad...@gmail.com>:
Hi,
This is driving me nuts, I have been trying to get the latest communication for a lead and this is the code I am using but the problem is that the communication ID shown in the output is old whereas the date shown in the list is of the latest communication, I have used a similar code in Sales Orders but there I am getting the correct results, is there something wrong with my code or something else.
SELECT com.parent,
com.name, max(com.communication_date)
FROM `tabCommunication` com
WHERE com.parenttype = 'Lead'
GROUP BY com.parent
Please note that the
com.name column is always showing the most earliest id of the communication instead of the latest.
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/7b784768-da53-4092-8793-3b59424b940d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
–
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/CABK1YkPUZwQmLT0EEKSJsK4WS3QUGXTPuPq_db-FCwhzq1Ozfg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Hi Maxwell,
I guess you got me wrong, what I want is a list of all leads with all their latest communications listed along with them but the problem is that when I group by lead the output is wrong even with max command in communication whereas similar commands work fine.
For example my data is something like this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
01-01-2010
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM3
|
01-01-2010
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM5
|
01-01-2010
|
|
LEAD003
|
COM6
|
29-03-2014
|
My ideal OUTPUT what I am looking for is this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM6
|
29-03-2014
|
But what I am getting in the actual result is not right, notice that the communication ID is not matching with the communication date when I run the code which I have mentioned earlier.
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
29-03-2014
|
|
LEAD002
|
COM3
|
29-03-2014
|
|
LEAD003
|
COM5
|
29-03-2014
|
Let me know what is wrong with my code since a similar mysql query was getting expected results from the Sales Order table.
PS: I just noticed that this is not the right forum for all the talk, I would request the admin to move this post to the developer forums, if possible.
On Friday, March 28, 2014 6:54:05 PM UTC+5:30, Maxwell wrote:
Yes, you are getting the all cmomunications from Leads not the latest communication from current lead, add a new condition in where:
WHERE con.parenttype = 'Lead' AND con.parent='%s'
and pass cur_frm.docname as paramenter to query
2014-03-28 8:25 GMT-03:00 Addy
<ad...@gmail.com>:
Hi,
This is driving me nuts, I have been trying to get the latest communication for a lead and this is the code I am using but the problem is that the communication ID shown in the output is old whereas the date shown in the list is of the latest communication, I have used a similar code in Sales Orders but there I am getting the correct results, is there something wrong with my code or something else.
SELECT com.parent,
com.name, max(com.communication_date)
FROM `tabCommunication` com
WHERE com.parenttype = 'Lead'
GROUP BY com.parent
Please note that the
com.name column is always showing the most earliest id of the communication instead of the latest.
–
You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/7b784768-da53-4092-8793-3b59424b940d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
–
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/a02f3751-835b-4d5f-b307-fa9c19bbde05%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Addy sorry, I could understand the problem, but it is unclear to me, saying something about it do not know how you are doing this report, you would have it available on Github?
2014-03-29 2:53 GMT-03:00 Addy
<ad...@gmail.com>:
Hi Maxwell,
I guess you got me wrong, what I want is a list of all leads with all their latest communications listed along with them but the problem is that when I group by lead the output is wrong even with max command in communication whereas similar commands work fine.
For example my data is something like this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
01-01-2010
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM3
|
01-01-2010
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM5
|
01-01-2010
|
|
LEAD003
|
COM6
|
29-03-2014
|
My ideal OUTPUT what I am looking for is this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM6
|
29-03-2014
|
But what I am getting in the actual result is not right, notice that the communication ID is not matching with the communication date when I run the code which I have mentioned earlier.
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
29-03-2014
|
|
LEAD002
|
COM3
|
29-03-2014
|
|
LEAD003
|
COM5
|
29-03-2014
|
Let me know what is wrong with my code since a similar mysql query was getting expected results from the Sales Order table.
PS: I just noticed that this is not the right forum for all the talk, I would request the admin to move this post to the developer forums, if possible.
On Friday, March 28, 2014 6:54:05 PM UTC+5:30, Maxwell wrote:
Yes, you are getting the all cmomunications from Leads not the latest communication from current lead, add a new condition in where:
WHERE con.parenttype = 'Lead' AND con.parent='%s'
and pass cur_frm.docname as paramenter to query
2014-03-28 8:25 GMT-03:00 Addy
<ad...@gmail.com>:
Hi,
This is driving me nuts, I have been trying to get the latest communication for a lead and this is the code I am using but the problem is that the communication ID shown in the output is old whereas the date shown in the list is of the latest communication, I have used a similar code in Sales Orders but there I am getting the correct results, is there something wrong with my code or something else.
SELECT com.parent,
com.name, max(com.communication_date)
FROM `tabCommunication` com
WHERE com.parenttype = 'Lead'
GROUP BY com.parent
Please note that the
com.name column is always showing the most earliest id of the communication instead of the latest.
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/7b784768-da53-4092-8793-3b59424b940d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
–
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/a02f3751-835b-4d5f-b307-fa9c19bbde05%40googlegroups.com.
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/CABK1YkPrzO%2B%2BPk4Oj%2BU3bH5uE3KktDqRAFj5X8e5erYUq0go3Q%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
2014-03-31 8:16 GMT-03:00 Maxwell Morais
<ma...@gmail.com>:
Addy sorry, I could understand the problem, but it is unclear to me, saying something about it do not know how you are doing this report, you would have it available on Github?
2014-03-29 2:53 GMT-03:00 Addy
<ad...@gmail.com>:
Hi Maxwell,
I guess you got me wrong, what I want is a list of all leads with all their latest communications listed along with them but the problem is that when I group by lead the output is wrong even with max command in communication whereas similar commands work fine.
For example my data is something like this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
01-01-2010
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM3
|
01-01-2010
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM5
|
01-01-2010
|
|
LEAD003
|
COM6
|
29-03-2014
|
My ideal OUTPUT what I am looking for is this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM6
|
29-03-2014
|
But what I am getting in the actual result is not right, notice that the communication ID is not matching with the communication date when I run the code which I have mentioned earlier.
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
29-03-2014
|
|
LEAD002
|
COM3
|
29-03-2014
|
|
LEAD003
|
COM5
|
29-03-2014
|
Let me know what is wrong with my code since a similar mysql query was getting expected results from the Sales Order table.
PS: I just noticed that this is not the right forum for all the talk, I would request the admin to move this post to the developer forums, if possible.
On Friday, March 28, 2014 6:54:05 PM UTC+5:30, Maxwell wrote:
Yes, you are getting the all cmomunications from Leads not the latest communication from current lead, add a new condition in where:
WHERE con.parenttype = 'Lead' AND con.parent='%s'
and pass cur_frm.docname as paramenter to query
2014-03-28 8:25 GMT-03:00 Addy
<ad...@gmail.com>:
Hi,
This is driving me nuts, I have been trying to get the latest communication for a lead and this is the code I am using but the problem is that the communication ID shown in the output is old whereas the date shown in the list is of the latest communication, I have used a similar code in Sales Orders but there I am getting the correct results, is there something wrong with my code or something else.
SELECT com.parent,
com.name, max(com.communication_date)
FROM `tabCommunication` com
WHERE com.parenttype = 'Lead'
GROUP BY com.parent
Please note that the
com.name column is always showing the most earliest id of the communication instead of the latest.
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/7b784768-da53-4092-8793-3b59424b940d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
–
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/a02f3751-835b-4d5f-b307-fa9c19bbde05%40googlegroups.com.
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/CABK1YkOspy11XQpVzUeDbFstOmojGQGSPrH71RgoLmTp0hKegw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Maxwell,
I have updated my code for the communication in github but still I am unable to fetch the latest communication ID, the surprising thing is that the code seems to be right (at least to my untrained eyes) and it seems to be working well for some communications but for some communications I am getting the latest dates but the id is wrong.
I guess I would have to live with it.
On Monday, April 7, 2014 6:34:38 AM UTC+5:30, Maxwell wrote:
2014-03-31 8:16 GMT-03:00 Maxwell Morais
<ma...@gmail.com>:
Addy sorry, I could understand the problem, but it is unclear to me, saying something about it do not know how you are doing this report, you would have it available on Github?
2014-03-29 2:53 GMT-03:00 Addy
<ad...@gmail.com>:
Hi Maxwell,
I guess you got me wrong, what I want is a list of all leads with all their latest communications listed along with them but the problem is that when I group by lead the output is wrong even with max command in communication whereas similar commands work fine.
For example my data is something like this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
01-01-2010
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM3
|
01-01-2010
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM5
|
01-01-2010
|
|
LEAD003
|
COM6
|
29-03-2014
|
My ideal OUTPUT what I am looking for is this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM6
|
29-03-2014
|
But what I am getting in the actual result is not right, notice that the communication ID is not matching with the communication date when I run the code which I have mentioned earlier.
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
29-03-2014
|
|
LEAD002
|
COM3
|
29-03-2014
|
|
LEAD003
|
COM5
|
29-03-2014
|
Let me know what is wrong with my code since a similar mysql query was getting expected results from the Sales Order table.
PS: I just noticed that this is not the right forum for all the talk, I would request the admin to move this post to the developer forums, if possible.
On Friday, March 28, 2014 6:54:05 PM UTC+5:30, Maxwell wrote:
Yes, you are getting the all cmomunications from Leads not the latest communication from current lead, add a new condition in where:
WHERE con.parenttype = 'Lead' AND con.parent='%s'
and pass cur_frm.docname as paramenter to query
2014-03-28 8:25 GMT-03:00 Addy
<ad...@gmail.com>:
Hi,
This is driving me nuts, I have been trying to get the latest communication for a lead and this is the code I am using but the problem is that the communication ID shown in the output is old whereas the date shown in the list is of the latest communication, I have used a similar code in Sales Orders but there I am getting the correct results, is there something wrong with my code or something else.
SELECT com.parent,
com.name, max(com.communication_date)
FROM `tabCommunication` com
WHERE com.parenttype = 'Lead'
GROUP BY com.parent
Please note that the
com.name column is always showing the most earliest id of the communication instead of the latest.
–
You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/7b784768-da53-4092-8793-3b59424b940d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
–
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/a02f3751-835b-4d5f-b307-fa9c19bbde05%40googlegroups.com.
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/a793c2c9-e979-4689-a355-b59378c954ae%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
You can send-me the link of the code on github? maybe I can help you?
2014-04-09 3:53 GMT-03:00 Addy <ad…@gmail.com>:
Maxwell,
I have updated my code for the communication in github but still I am unable to fetch the latest communication ID, the surprising thing is that the code seems to be right (at least to my untrained eyes) and it seems to be working well for some communications but for some communications I am getting the latest dates but the id is wrong.
I guess I would have to live with it.
On Monday, April 7, 2014 6:34:38 AM UTC+5:30, Maxwell wrote:
2014-03-31 8:16 GMT-03:00 Maxwell Morais
<ma...@gmail.com>:
Addy sorry, I could understand the problem, but it is unclear to me, saying something about it do not know how you are doing this report, you would have it available on Github?
2014-03-29 2:53 GMT-03:00 Addy
<ad...@gmail.com>:
Hi Maxwell,
I guess you got me wrong, what I want is a list of all leads with all their latest communications listed along with them but the problem is that when I group by lead the output is wrong even with max command in communication whereas similar commands work fine.
For example my data is something like this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
01-01-2010
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM3
|
01-01-2010
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM5
|
01-01-2010
|
|
LEAD003
|
COM6
|
29-03-2014
|
My ideal OUTPUT what I am looking for is this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM6
|
29-03-2014
|
But what I am getting in the actual result is not right, notice that the communication ID is not matching with the communication date when I run the code which I have mentioned earlier.
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
29-03-2014
|
|
LEAD002
|
COM3
|
29-03-2014
|
|
LEAD003
|
COM5
|
29-03-2014
|
Let me know what is wrong with my code since a similar mysql query was getting expected results from the Sales Order table.
PS: I just noticed that this is not the right forum for all the talk, I would request the admin to move this post to the developer forums, if possible.
On Friday, March 28, 2014 6:54:05 PM UTC+5:30, Maxwell wrote:
Yes, you are getting the all cmomunications from Leads not the latest communication from current lead, add a new condition in where:
WHERE con.parenttype = 'Lead' AND con.parent='%s'
and pass cur_frm.docname as paramenter to query
2014-03-28 8:25 GMT-03:00 Addy
<ad...@gmail.com>:
Hi,
This is driving me nuts, I have been trying to get the latest communication for a lead and this is the code I am using but the problem is that the communication ID shown in the output is old whereas the date shown in the list is of the latest communication, I have used a similar code in Sales Orders but there I am getting the correct results, is there something wrong with my code or something else.
SELECT com.parent,
com.name, max(com.communication_date)
FROM `tabCommunication` com
WHERE com.parenttype = 'Lead'
GROUP BY com.parent
Please note that the
com.name column is always showing the most earliest id of the communication instead of the latest.
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/7b784768-da53-4092-8793-3b59424b940d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
–
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/a02f3751-835b-4d5f-b307-fa9c19bbde05%40googlegroups.com.
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/CABK1YkNsoAwZ5cmnFyF8%3Dzy7WR9Fne1%3Dvbasvv6ntgHiuXvMDg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Hi Maxwell,
Here is the link for the report
link, I am trying to make. Notice that in line 69 where I am fetching the communication I have mentioned to get the latest communication. Let me know if you find any mistake.
On Wednesday, April 9, 2014 8:36:57 PM UTC+5:30, Maxwell wrote:
You can send-me the link of the code on github? maybe I can help you?
2014-04-09 3:53 GMT-03:00 Addy <ad…@gmail.com>:
Maxwell,
I have updated my code for the communication in github but still I am unable to fetch the latest communication ID, the surprising thing is that the code seems to be right (at least to my untrained eyes) and it seems to be working well for some communications but for some communications I am getting the latest dates but the id is wrong.
I guess I would have to live with it.
On Monday, April 7, 2014 6:34:38 AM UTC+5:30, Maxwell wrote:
2014-03-31 8:16 GMT-03:00 Maxwell Morais
<ma...@gmail.com>:
Addy sorry, I could understand the problem, but it is unclear to me, saying something about it do not know how you are doing this report, you would have it available on Github?
2014-03-29 2:53 GMT-03:00 Addy
<ad...@gmail.com>:
Hi Maxwell,
I guess you got me wrong, what I want is a list of all leads with all their latest communications listed along with them but the problem is that when I group by lead the output is wrong even with max command in communication whereas similar commands work fine.
For example my data is something like this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
01-01-2010
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM3
|
01-01-2010
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM5
|
01-01-2010
|
|
LEAD003
|
COM6
|
29-03-2014
|
My ideal OUTPUT what I am looking for is this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM6
|
29-03-2014
|
But what I am getting in the actual result is not right, notice that the communication ID is not matching with the communication date when I run the code which I have mentioned earlier.
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
29-03-2014
|
|
LEAD002
|
COM3
|
29-03-2014
|
|
LEAD003
|
COM5
|
29-03-2014
|
Let me know what is wrong with my code since a similar mysql query was getting expected results from the Sales Order table.
PS: I just noticed that this is not the right forum for all the talk, I would request the admin to move this post to the developer forums, if possible.
On Friday, March 28, 2014 6:54:05 PM UTC+5:30, Maxwell wrote:
Yes, you are getting the all cmomunications from Leads not the latest communication from current lead, add a new condition in where:
WHERE con.parenttype = 'Lead' AND con.parent='%s'
and pass cur_frm.docname as paramenter to query
2014-03-28 8:25 GMT-03:00 Addy
<ad...@gmail.com>:
Hi,
This is driving me nuts, I have been trying to get the latest communication for a lead and this is the code I am using but the problem is that the communication ID shown in the output is old whereas the date shown in the list is of the latest communication, I have used a similar code in Sales Orders but there I am getting the correct results, is there something wrong with my code or something else.
SELECT com.parent,
com.name, max(com.communication_date)
FROM `tabCommunication` com
WHERE com.parenttype = 'Lead'
GROUP BY com.parent
Please note that the
com.name column is always showing the most earliest id of the communication instead of the latest.
–
You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/7b784768-da53-4092-8793-3b59424b940d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
–
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/a02f3751-835b-4d5f-b307-fa9c19bbde05%40googlegroups.com.
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/ce04f5f2-23d2-47e0-9a7f-b9e7ba56dde9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
2014-04-10 4:40 GMT-03:00 Addy
<ad...@gmail.com>:
Hi Maxwell,
Here is the link for the report
link, I am trying to make. Notice that in line 69 where I am fetching the communication I have mentioned to get the latest communication. Let me know if you find any mistake.
On Wednesday, April 9, 2014 8:36:57 PM UTC+5:30, Maxwell wrote:
You can send-me the link of the code on github? maybe I can help you?
2014-04-09 3:53 GMT-03:00 Addy <ad…@gmail.com>:
Maxwell,
I have updated my code for the communication in github but still I am unable to fetch the latest communication ID, the surprising thing is that the code seems to be right (at least to my untrained eyes) and it seems to be working well for some communications but for some communications I am getting the latest dates but the id is wrong.
I guess I would have to live with it.
On Monday, April 7, 2014 6:34:38 AM UTC+5:30, Maxwell wrote:
2014-03-31 8:16 GMT-03:00 Maxwell Morais
<ma...@gmail.com>:
Addy sorry, I could understand the problem, but it is unclear to me, saying something about it do not know how you are doing this report, you would have it available on Github?
2014-03-29 2:53 GMT-03:00 Addy
<ad...@gmail.com>:
Hi Maxwell,
I guess you got me wrong, what I want is a list of all leads with all their latest communications listed along with them but the problem is that when I group by lead the output is wrong even with max command in communication whereas similar commands work fine.
For example my data is something like this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
01-01-2010
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM3
|
01-01-2010
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM5
|
01-01-2010
|
|
LEAD003
|
COM6
|
29-03-2014
|
My ideal OUTPUT what I am looking for is this:
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM2
|
29-03-2014
|
|
LEAD002
|
COM4
|
29-03-2014
|
|
LEAD003
|
COM6
|
29-03-2014
|
But what I am getting in the actual result is not right, notice that the communication ID is not matching with the communication date when I run the code which I have mentioned earlier.
|
Lead ID
|
Communication ID
|
Communication Date
|
|
LEAD001
|
COM1
|
29-03-2014
|
|
LEAD002
|
COM3
|
29-03-2014
|
|
LEAD003
|
COM5
|
29-03-2014
|
Let me know what is wrong with my code since a similar mysql query was getting expected results from the Sales Order table.
PS: I just noticed that this is not the right forum for all the talk, I would request the admin to move this post to the developer forums, if possible.
On Friday, March 28, 2014 6:54:05 PM UTC+5:30, Maxwell wrote:
Yes, you are getting the all cmomunications from Leads not the latest communication from current lead, add a new condition in where:
WHERE con.parenttype = 'Lead' AND con.parent='%s'
and pass cur_frm.docname as paramenter to query
2014-03-28 8:25 GMT-03:00 Addy
<ad...@gmail.com>:
Hi,
This is driving me nuts, I have been trying to get the latest communication for a lead and this is the code I am using but the problem is that the communication ID shown in the output is old whereas the date shown in the list is of the latest communication, I have used a similar code in Sales Orders but there I am getting the correct results, is there something wrong with my code or something else.
SELECT com.parent,
com.name, max(com.communication_date)
FROM `tabCommunication` com
WHERE com.parenttype = 'Lead'
GROUP BY com.parent
Please note that the
com.name column is always showing the most earliest id of the communication instead of the latest.
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/7b784768-da53-4092-8793-3b59424b940d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
–
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/a02f3751-835b-4d5f-b307-fa9c19bbde05%40googlegroups.com.
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
--
Best Regards.
Maxwell Morais
Python Developer powered by Webnotes framework <3
–
You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-user-forum/CABK1YkPJX-sq%3D0cvHHJ3cyXyKd7C6kUMPHRg29LRE0qONBfQHA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.