Server Script - Get Nearest Date from Child Table?

1

Hello,

I have customised the Supplier DocType and added a “Supplier Certifications” Child table (with the link field Certifications)

The child table will hold the name and expiry date of various professional certifications the Supplier may have.

On save, I want to find the closest expiry date from the Child table (column name expiry_date) holding all the certifications and update a field on the Supplier DocType with this date (field name certification_expiry). In turn a notification will be sent when the date is near.

I have tried to make a Server Script for this but it does not appear to work.

from frappe.model.document import Document

def update_parent_date(doc, method):
    parent = doc.parent
    child_table_data = frappe.db.sql("""SELECT expiry_date FROM `Supplier Certifications` WHERE parent = %s""", (parent), as_dict=1)
    closest_date = None
    closest_diff = None
    today = frappe.utils.now_datetime().date()
    for row in child_table_data:
        date_column = row.get("expiry_date")
        diff = abs((today - date_column).days)
        if closest_date is None or diff < closest_diff:
            closest_date = date_column
            closest_diff = diff
    parent_doc = frappe.get_doc("Supplier", parent)
    parent_doc.set("certification_expiry", closest_date)
    parent_doc.save()

Can anyone help me troubleshoot this or suggest a better way to achieve this?

Thanks,
Paul

2

did you try sql “order by” to order the expiry_date column

3

Thanks but I forgot to mention I also got an import error on trying to save.

So does this mean I am trying to use something non-standard that I cannot import to get the script to work?

Traceback as follows

### App Versions

{
“erpnext”: “14.0.0-dev”,
“frappe”: “15.0.0-dev”,
“healthcare”: “0.0.1”,
“hrms”: “1.0.0”,
“lms”: “0.0.1”,
“payments”: “0.0.1”
}

### Route

Form/Supplier/KEYA

### Trackeback

Traceback (most recent call last):
File “apps/frappe/frappe/app.py”, line 56, in application
response = frappe.api.handle()
File “apps/frappe/frappe/api.py”, line 53, in handle
return _RESTAPIHandler(call, doctype, name).get_response()
File “apps/frappe/frappe/api.py”, line 69, in get_response
return self.handle_method()
File “apps/frappe/frappe/api.py”, line 79, in handle_method
return frappe.handler.handle()
File “apps/frappe/frappe/handler.py”, line 48, in handle
data = execute_cmd(cmd)
File “apps/frappe/frappe/handler.py”, line 86, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File “apps/frappe/frappe/init.py”, line 1589, in call
return fn(*args, **newargs)
File “apps/frappe/frappe/utils/typing_validations.py”, line 33, in wrapper
return func(*args, **kwargs)
File “apps/frappe/frappe/desk/form/save.py”, line 34, in savedocs
doc.save()
File “apps/frappe/frappe/model/document.py”, line 316, in save
return self._save(*args, **kwargs)
File “apps/frappe/frappe/model/document.py”, line 368, in _save
self.run_post_save_methods()
File “apps/frappe/frappe/model/document.py”, line 1093, in run_post_save_methods
self.run_method(“on_update”)
File “apps/frappe/frappe/model/document.py”, line 925, in run_method
run_server_script_for_doc_event(self, method)
File “apps/frappe/frappe/core/doctype/server_script/server_script_utils.py”, line 39, in run_server_script_for_doc_event
frappe.get_doc(“Server Script”, script_name).execute_doc(doc)
File “apps/frappe/frappe/core/doctype/server_script/server_script.py”, line 98, in execute_doc
safe_exec(self.script, _locals={“doc”: doc}, restrict_commit_rollback=True)
File “apps/frappe/frappe/utils/safe_exec.py”, line 81, in safe_exec
exec(
File “”, line 3, in
ImportError: import not found

### Request Data

{
“type”: “POST”,
“args”: {
“doc”: “{"name":"KEYA","owner":"Administrator","creation":"2023-01-23 09:02:45.675071","modified":"2023-02-01 11:55:11.143946","modified_by":"Administrator","docstatus":0,"idx":0,"naming_series":"SUP-.YYYY.-","supplier_name":"KEYA","country":"United Kingdom","supplier_group":"All Supplier Groups","supplier_type":"Company","is_transporter":0,"is_internal_supplier":0,"represents_company":"","language":"en-GB","allow_purchase_invoice_creation_without_purchase_order":0,"allow_purchase_invoice_creation_without_purchase_receipt":0,"is_frozen":0,"disabled":0,"warn_rfqs":0,"warn_pos":0,"prevent_rfqs":0,"prevent_pos":0,"on_hold":0,"hold_type":"","is_approved_supplier":0,"asl_approved":"Approved","is_critical_supplier":0,"is_iso_certification_required":1,"doctype":"Supplier","companies":[],"accounts":[],"certifications":[{"name":"a4bfd06d2d","owner":"Administrator","creation":"2023-01-23 09:02:45.675071","modified":"2023-02-01 11:55:11.143946","modified_by":"Administrator","docstatus":0,"idx":1,"certificate_name":"0f42321e8d","expiry_date":"2023-02-11","parent":"KEYA","parentfield":"certifications","parenttype":"Supplier","doctype":"Supplier Certifications"}],"__onload":{"addr_list":[],"contact_list":[],"dashboard_info":[]},"__last_sync_on":"2023-02-01T11:56:16.553Z","__unsaved":1}”,
“action”: “Save”
},
“btn”: {
“jQuery360034766118875718191”: {
“events”: {
“click”: [
{
“type”: “click”,
“origType”: “click”,
“guid”: 664,
“namespace”: “”
}
]
}
}
},
“freeze”: true,
“headers”: {},
“error_handlers”: {},
“url”: “/api/method/frappe.desk.form.save.savedocs”
}

### Response Data

{
“exception”: “ImportError: import not found”
}

4

yes you can not use import in server script you will need to make custom app,
this is the list of allowed server script keywords: Script API

5

Thanks, I’d prefer to avoid making a custom app if I can. Do any of the imports cover what I am trying to achieve or is it outwith what a Server script can do. Would a client script be a better approach?

6

if the following is what you are trying to import you dont need it as i can see you dont use it in your code
from frappe.model.document import Document

7

I have removed it and the import error has gone, I have also added the ORDER BY as suggested but nothing is returned to the parent DocType field.

def update_parent_date(doc, method):
    parent = doc.parent
    child_table_data = frappe.db.sql("""SELECT expiry_date FROM `Supplier Certifications` WHERE parent = %s ORDER BY expiry_date ASC""", (parent), as_dict=1)
    closest_date = None
    closest_diff = None
    today = frappe.utils.now_datetime().date()
    for row in child_table_data:
        date_column = row.get("expiry_date")
        diff = abs((today - date_column).days)
        if closest_date is None or diff < closest_diff:
            closest_date = date_column
            closest_diff = diff
    parent_doc = frappe.get_doc("Supplier", parent)
    parent_doc.set("iso_certification_expiry", closest_date)
    parent_doc.save()

So how can I work out if the script is not reading the data, not setting the data or most likely doing neither lol

8

hello, when you work in server scrip you dont need to call that function so just use:

parent = doc.parent
child_table_data = frappe.db.sql("""SELECT expiry_date FROM `Supplier Certifications` WHERE parent = %s ORDER BY expiry_date ASC""", (parent), as_dict=1)
closest_date = None
closest_diff = None
today = frappe.utils.now_datetime().date()

for row in child_table_data:
    date_column = row.get("expiry_date")
    diff = abs((today - date_column).days)
    if closest_date is None or diff < closest_diff:
        closest_date = date_column
        closest_diff = diff

parent_doc = frappe.get_doc("Supplier", parent)
parent_doc.set("iso_certification_expiry", closest_date)
parent_doc.save()
9

Still running into a couple of issues with the Doctypes

The last script generates the following error on save

image
image725×253 15 KB

Which I think I’ve solved by adding tab infront of the child Doctype to give.

parent = doc.parent
child_table_data = frappe.db.sql("""SELECT expiry_date FROM `tabSupplier Certifications` WHERE parent = %s ORDER BY expiry_date ASC""", (parent), as_dict=1)
closest_date = None
closest_diff = None
today = frappe.utils.now_datetime().date()

for row in child_table_data:
    date_column = row.get("expiry_date")
    diff = abs((today - date_column).days)
    if closest_date is None or diff < closest_diff:
        closest_date = date_column
        closest_diff = diff

parent_doc = frappe.get_doc("Supplier", parent)
parent_doc.set("iso_certification_expiry", closest_date)
parent_doc.save()

But on save I now get the following error

image
image726×214 9.5 KB

So have I not correctly specified the names of the Parent and Child DocTypes? Supplier None almost suggests that I have the right DocType but for some reason thinks I have not specified a record within that DocType.

Any ideas?

10

Can anyone help me find a way to achieve this?

11

Gave up on Python and managed to get the following JS code working to achieve what I needed. Just posting it here in case it helps anyone else.

frappe.ui.form.on("Supplier", "refresh", function(frm) {
  if (!frm.doc.__islocal) {
    update_iso_certification_expiry(frm.doc);
  }
});

function update_iso_certification_expiry(doc) {
  let certifications = doc.certifications;
  if (!certifications) {
    doc.iso_certification_expiry = null;
    return;
  }
  let today = frappe.datetime.now_date();
  let diffs = {};
  certifications.forEach(certification => {
    let expiry_date = certification.expiry_date;
    let diff = Math.abs(
      frappe.datetime.get_diff(today, expiry_date, "day")
    );
    diffs[diff] = expiry_date;
  });
  let sorted_diffs = Object.keys(diffs).sort();
  let closest_date = diffs[sorted_diffs[0]];
  cur_frm.set_value("iso_certification_expiry", closest_date);
  cur_frm.refresh_field("iso_certification_expiry");
}
1 Like