How to get the last communication made with a Lead or Customer?

Hi,

I am trying to make a custom report, the report is basically for follow-ups on Customers and Leads. The problem is that I am unable to find a way to fetch the last communication with a lead or customer since the communication table does not have the customer or lead or supplier fields.

I remember that the communication table used to have these fields but now I am kind of stuck with my limited coding knowledge as to how to fetch the data into the report from communication table for leads and customers.


Code of the Report being developed.



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/1c3d9fdc-35cf-41df-83db-fec61e3cea0f%40googlegroups.com.

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

Aditya, I can’t understand if you can get the communication or the content of the communication

name
,

But in the same cases you should be use a SQL Query, like as bellow.

SELECT
  `content`
FROM `tabCommunication`
WHERE
    `communication_date`=MAX(`communication_date`)
     AND `parent`="%s" AND `parentname`="%s"

you shoul pass the Doctype (Lead or Customer) and the Name of doctype as parameters to the SQL and run it with webnotes.conn.sql



2014-03-21 8:13 GMT-03:00 Aditya Duggal <ad...@gmail.com>:
Hi,

I am trying to make a custom report, the report is basically for follow-ups on Customers and Leads. The problem is that I am unable to find a way to fetch the last communication with a lead or customer since the communication table does not have the customer or lead or supplier fields.

I remember that the communication table used to have these fields but now I am kind of stuck with my limited coding knowledge as to how to fetch the data into the report from communication table for leads and customers.




Note:



If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.



    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/1c3d9fdc-35cf-41df-83db-fec61e3cea0f%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




Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/CABK1YkPQahY9SP%3DEnEx41FeQj%2Bp3kUd-QV%3DEmV88dAq0Zj-BkA%40mail.gmail.com.

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

Thanks for the reply, I think you understood me well. I was trying to get the `content` field but there was a problem where I could not think how to join the 2 tables but your code has certainly helped me and here is what I would be using


comm = webnotes.conn.sql("""SELECT com.name, com.owner, com.parent, 
com.communication_date
FROM `tabCommunication` com
WHERE com.parent LIKE '%LEAD%'
GROUP BY com.parent""")

I would be passing the doctype name via a parameter but I am still not able to find the parentname field since I was getting a unknown column field with that field name.

Another issue with fetching the `content` field is that the reports are not able to handle the format and generally lose their formatting when I fetch the content field in a report.

Another request is that how could I find these kind of fields with their names since they are not mentioned in the doctype, is there a way to see the full structure of a table and where should I look for without loggin into the shell client of my server and looking up for the tables in mysql.

On Friday, March 21, 2014 6:04:26 PM UTC+5:30, Maxwell wrote:

Aditya, I can’t understand if you can get the communication or the content of the communication

name
,

But in the same cases you should be use a SQL Query, like as bellow.

SELECT
  `content`
FROM `tabCommunication`
WHERE
    `communication_date`=MAX(`communication_date`)
     AND `parent`="%s" AND `parentname`="%s"

you shoul pass the Doctype (Lead or Customer) and the Name of doctype as parameters to the SQL and run it with webnotes.conn.sql



2014-03-21 8:13 GMT-03:00 Aditya Duggal <ad...@gmail.com>:
Hi,

I am trying to make a custom report, the report is basically for follow-ups on Customers and Leads. The problem is that I am unable to find a way to fetch the last communication with a lead or customer since the communication table does not have the customer or lead or supplier fields.

I remember that the communication table used to have these fields but now I am kind of stuck with my limited coding knowledge as to how to fetch the data into the report from communication table for leads and customers.




Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/1c3d9fdc-35cf-41df-83db-fec61e3cea0f%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




Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/af0da68a-d57c-4b54-8019-e2abdaf9ae3e%40googlegroups.com.

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

Aditya, parentname is wrong the correct name of the field is parent, and the correct logic for the parent is parenttype eg:


SQL = """
SELECT `content` FROM `tabCommunication` WHERE `communication_date` = MAX ( ` communication_date` ) AND `parenttype` = "%s" AND `parentname` = "%s
""""

for row in data:
    last = webnotes.conn.sql(SQL, ( filters["doctype"],  row['name']), as_dict=True)
    row["your_field_name"] = last["content"]

About the default fields, you can view it here: https://github.com/webnotes/wnframework/blob/develop/webnotes/model/db_schema.py#L36

Below one reference about each fieldname:

  • name : Is the ID of the DocType (is based on naming options of the doctype)
  • creation : The date of creation of the doctype
  • updated : The date of the last updated of the doctype
  • owner : The user that who created the doctype
  • updated_by : The last user that who updated the doctype
  • docstatus : 0 = Not Submitted, 1 = Submitted, 2 = Cancelado
  • parent (ref): The name of the parent doctype
  • parentfield (ref): The name of the parent field on parent doctype
  • parenttype (ref): The type of the parent eg: for the Sales Order Item the parent type is Sales Order

    (ref): These fields are used for Table fields.

About the format of the field_content I think that the content is stored “as it is”, it isn’t a good way, because formatation problems, and interface compatibility, if you don’t have a problem with raw formats, I recommended to you the use of markdown to show the content as raw text, and use a popup to show the content formated to user on report only when the user click on the field.



2014-03-21 15:46 GMT-03:00 Aditya Duggal <ad...@gmail.com>:
Hi Maxwell,

Thanks for the reply, I think you understood me well. I was trying to get the `content` field but there was a problem where I could not think how to join the 2 tables but your code has certainly helped me and here is what I would be using


comm = webnotes.conn.sql("""SELECT com.name, com.owner, com.parent,
com.communication_date
FROM `tabCommunication` com
WHERE com.parent LIKE '%LEAD%'
GROUP BY com.parent""")

I would be passing the doctype name via a parameter but I am still not able to find the parentname field since I was getting a unknown column field with that field name.

Another issue with fetching the content field is that the reports are not able to handle the format and generally lose their formatting when I fetch the content field in a report.


Another request is that how could I find these kind of fields with their names since they are not mentioned in the doctype, is there a way to see the full structure of a table and where should I look for without loggin into the shell client of my server and looking up for the tables in mysql.



On Friday, March 21, 2014 6:04:26 PM UTC+5:30, Maxwell wrote:

Aditya, I can’t understand if you can get the communication or the content of the communication

name
,

But in the same cases you should be use a SQL Query, like as bellow.

SELECT
  `content`
FROM `tabCommunication`
WHERE
    `communication_date`=MAX(`communication_date`)
     AND `parent`="%s" AND `parentname`="%s"

you shoul pass the Doctype (Lead or Customer) and the Name of doctype as parameters to the SQL and run it with webnotes.conn.sql



2014-03-21 8:13 GMT-03:00 Aditya Duggal <ad...@gmail.com>:
Hi,

I am trying to make a custom report, the report is basically for follow-ups on Customers and Leads. The problem is that I am unable to find a way to fetch the last communication with a lead or customer since the communication table does not have the customer or lead or supplier fields.

I remember that the communication table used to have these fields but now I am kind of stuck with my limited coding knowledge as to how to fetch the data into the report from communication table for leads and customers.




Note:



If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.



    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/1c3d9fdc-35cf-41df-83db-fec61e3cea0f%40googlegroups.com.

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




--

Best Regards.

Maxwell Morais
Python Developer powered by Webnotes framework <3



Note:



If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.



    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/af0da68a-d57c-4b54-8019-e2abdaf9ae3e%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




Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/CABK1YkNGHgrShMM1JQ%3DKfKMP%2BgUNhhADXRFQB1E0LYjndVmRRQ%40mail.gmail.com.

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

The information provided has greatly helped me and I am able to achieve what I was trying to do.

Once again thanks for yor prompt reply.

On Saturday, March 22, 2014 12:46:23 AM UTC+5:30, Maxwell wrote:

Aditya, parentname is wrong the correct name of the field is parent, and the correct logic for the parent is parenttype eg:


SQL = """
SELECT `content` FROM `tabCommunication` WHERE `communication_date` = MAX ( ` communication_date` ) AND `parenttype` = "%s" AND `parentname` = "%s
""""

for row in data:
    last = webnotes.conn.sql(SQL, ( filters["doctype"],  row['name']), as_dict=True)
    row["your_field_name"] = last["content"]

About the default fields, you can view it here: https://github.com/webnotes/wnframework/blob/develop/webnotes/model/db_schema.py#L36

Below one reference about each fieldname:

  • name : Is the ID of the DocType (is based on naming options of the doctype)
  • creation : The date of creation of the doctype
  • updated : The date of the last updated of the doctype
  • owner : The user that who created the doctype
  • updated_by : The last user that who updated the doctype
  • docstatus : 0 = Not Submitted, 1 = Submitted, 2 = Cancelado
  • parent (ref): The name of the parent doctype
  • parentfield (ref): The name of the parent field on parent doctype
  • parenttype (ref): The type of the parent eg: for the Sales Order Item the parent type is Sales Order

    (ref): These fields are used for Table fields.

About the format of the field_content I think that the content is stored “as it is”, it isn’t a good way, because formatation problems, and interface compatibility, if you don’t have a problem with raw formats, I recommended to you the use of markdown to show the content as raw text, and use a popup to show the content formated to user on report only when the user click on the field.



2014-03-21 15:46 GMT-03:00 Aditya Duggal <ad...@gmail.com>:
Hi Maxwell,

Thanks for the reply, I think you understood me well. I was trying to get the `content` field but there was a problem where I could not think how to join the 2 tables but your code has certainly helped me and here is what I would be using


comm = webnotes.conn.sql("""SELECT com.name, com.owner, com.parent, 
com.communication_date
FROM `tabCommunication` com
WHERE com.parent LIKE '%LEAD%'
GROUP BY com.parent""")

I would be passing the doctype name via a parameter but I am still not able to find the parentname field since I was getting a unknown column field with that field name.

Another issue with fetching the content field is that the reports are not able to handle the format and generally lose their formatting when I fetch the content field in a report.


Another request is that how could I find these kind of fields with their names since they are not mentioned in the doctype, is there a way to see the full structure of a table and where should I look for without loggin into the shell client of my server and looking up for the tables in mysql.



On Friday, March 21, 2014 6:04:26 PM UTC+5:30, Maxwell wrote:

Aditya, I can’t understand if you can get the communication or the content of the communication

name
,

But in the same cases you should be use a SQL Query, like as bellow.

SELECT
  `content`
FROM `tabCommunication`
WHERE
    `communication_date`=MAX(`communication_date`)
     AND `parent`="%s" AND `parentname`="%s"

you shoul pass the Doctype (Lead or Customer) and the Name of doctype as parameters to the SQL and run it with webnotes.conn.sql



2014-03-21 8:13 GMT-03:00 Aditya Duggal <ad...@gmail.com>:
Hi,

I am trying to make a custom report, the report is basically for follow-ups on Customers and Leads. The problem is that I am unable to find a way to fetch the last communication with a lead or customer since the communication table does not have the customer or lead or supplier fields.

I remember that the communication table used to have these fields but now I am kind of stuck with my limited coding knowledge as to how to fetch the data into the report from communication table for leads and customers.




Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+unsubscr…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/1c3d9fdc-35cf-41df-83db-fec61e3cea0f%40googlegroups.com.

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




--

Best Regards.

Maxwell Morais
Python Developer powered by Webnotes framework <3



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/af0da68a-d57c-4b54-8019-e2abdaf9ae3e%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




Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

    To view this discussion on the web visit https://groups.google.com/d/msgid/erpnext-developer-forum/3bf1b4a0-e50c-4fa2-8e7c-bf90971bc99f%40googlegroups.com.

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