Unable to get the latest communication for a lead

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
+55 11 954329659




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
+55 11 954329659




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.

For more options, visit https://groups.google.com/d/optout.



--

Best Regards.

Maxwell Morais
Python Developer powered by Webnotes framework <3
+55 11 954329659




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.

Addy, you can solve it?


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.

For more options, visit https://groups.google.com/d/optout.



--

Best Regards.

Maxwell Morais
Python Developer powered by Webnotes framework <3



--

Best Regards.

Maxwell Morais
Python Developer powered by Webnotes framework <3
+55 11 954329659




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:
Addy, you can solve it?


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.

For more options, visit https://groups.google.com/d/optout.



--

Best Regards.

Maxwell Morais
Python Developer powered by Webnotes framework <3



--

Best Regards.

Maxwell Morais
Python Developer powered by Webnotes framework <3
+55 11 954329659




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:
Addy, you can solve it?


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.

For more options, visit https://groups.google.com/d/optout.



--

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.



--

Best Regards.

Maxwell Morais
Python Developer powered by Webnotes framework <3
+55 11 954329659




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:
Addy, you can solve it?


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.

For more options, visit https://groups.google.com/d/optout.



--

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.



--

Best Regards.

Maxwell Morais
Python Developer powered by Webnotes framework <3
+55 11 954329659




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.

Hi Addy!

I add a comment inside your code, please look https://github.com/adityaduggal/custom_erpnext/commit/b08ee8f152c92a9a91c8d58a3146953ee0e3528c#diff-5343218bfa3fc4077ee279ca927150adR64



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:
Addy, you can solve it?


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.

For more options, visit https://groups.google.com/d/optout.



--

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.



--

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.



--

Best Regards.

Maxwell Morais
Python Developer powered by Webnotes framework <3
+55 11 954329659




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.