Custom Field in invoice based on value from other docTypes

Hello, I have two custom docTypes, one is a child table of the first, and it hold rows which have two relevant fields: CurrentValue which is an integer and a Check type field called: “expired” among other fields. Each row in the master docType have a set of rows in the child table and only one row in the child table has a (not expired) status, based on our business rules. The custom master docType have a relationship with the customer group’s docType via a link field.

What I want to accomplish is:

  1. When I submit an invoice for the first time, depending on the customer group of the invoice’s customer, I need to pick up the actual CurrentValue from my custom child table, in the (not expired) row.
  2. Assign to a custom field in the invoice.
  3. To Increment the CurrentValue in the child table and save it.

I need some help in determining if I need a custom client side script, a Server side script or something else. Could somebody give me some guidance on how to accomplish this or post some example that could lead me to the steps needed?

Any help will be really appreciated!

Thanks in advance.

I managed to solve this!

  1. I’ve created a custom read only field at invoice level.
  2. Then I created a server side function in which is called from on_submit and using frappe.db.sql, get the value from a custom doctype which have the current value for each customer group type, which is not expired.
  3. Using frappe.db.sql, increment the current value, so the next invoice will have the new incremented value.
  4. Assign the function’s returned value to the custom field.

Loving Frappe / Python / ERPNext so far!