How to setup the naming series?

ERPNext
1

I need to create a naming series based on a custom field I created in sales invoice. I created a field “custom_is_export_invoice”.

I need my naming series to be INV-{custom_is_export_invoice?“EX-”:“”}XXX.
So, simply if export invoice we add EX- and the number(XXX) should continue.

Here is an example:

  1. Domestic invoice - INV-001
  2. Domestic invoice - INV-002
  3. Export invoice - INV-EX-003…
2

Hi @BulkBeings
Please Read This Post I hope it will work

Thank You!

3

@Mohammadali Sorry, I don’t understand! I’m very new… Can you let me know stepwise

4

@Mohammadali I wrote this code

frappe.ui.form.on("Sales Invoice", {
  is_export: function (frm) {
      console.log('is_export:', frm.doc.custom_is_export_invoice);
      if (frm.doc.custom_is_export_invoice === 1) {
        frm.set_value("naming_series", "INV-EX-###-.FY.");
      } else {
        frm.set_value("naming_series", "INV-###-.FY.");
      }
  },   
  refresh: function(frm) {
        if (frm.doc.custom_is_export_invoice == null || frm.doc.custom_is_export_invoice == 0) {
            frm.set_value('print_format', 'BulkBeings Invoice');
        } else {
            frm.set_value('print_format', 'BulkBeings Export Invoice');
        }
    }
});

Can you help me what should I do? these are not working! I created client script

Screenshot 2024-04-17 at 1.24.32 AM
Screenshot 2024-04-17 at 1.24.32 AM2114×1044 217 KB

5

Hey mate,
Creating two naming series that share the same counter requires more technical work than you expect.

If it’s applicable in your case, keeping two different series, like the following, will be safer, scalable and easier to implement and maintain.

Domestic invoice - INV-001
Domestic invoice - INV-002
Export invoice - INV-EX-001

You can then use document naming rules to automatically assign those series.

Otherwise, if you insist on having them share the same counter, then the safest solution I can think of is the following;

  1. Create a doctype (i.e., invoiceIndex) to store an index number in an INT field (i.e., index).
  2. On your sales doctype create an INT field (i e., custom_index)
  3. Create a client script on Sales Invoice that runs onload and will;
    3.a. Fetch the last doc in our invoiceIndex doctype (using frappe.db.get_list)
    3.b. Get the value of invoiceIndex.index
    3.c. Assign the value of doc.custom_index to be invoiceIndex.index
    3.d. Edit: Add a condition to make this script run only if doc.custom_index is empty.
  4. Create a server script on Sales Invoice that triggers After Insert and will;
    4.a. Delete all docs in our invoiceIndex doctype (make sure to ignore permissions)
    4.b. Create a new doc in the same doctype and set new_doc.invoiceIndex = doc.custom_index + 1
  5. In Sales Invoice create the following fields
    5.a. Check Field (custom_is_export). You would want to check this field when creating an export invoice.
    5.b. Data Field (custom_is_export_txt)
  6. Create a client script for Sales Invoice that runs on custom_is_export to populate/clear the data field with the text “EX-” whenever the check field is enabled/disabled.
  7. Finally, set the naming of Sales Invoice to expression as the following

{YY}-{doc.custom_is_export_txt}{doc.custom_index}

Edit: I assume cancelling and amending an invoice will automatically append the amendment number to the end of the name, but test for that.

Handle invoice return series in a similar manner.

Edit 2: I forgot to mention, with the above logic, our index numbers are incremented on creation of a sales invoice which by that time is a draft. Not all drafts get submitted and you want to be able to reuse numbers while still maintaining a chronological order of invoice numbers and their dates. This will involve additional logic to both scripts and additional fields to both doctypes.

Also, to make sure your employees don’t ruin all the effort, you might want to automate the custom_is_export check field based on the customer’s territory.

1 Like
6

Hi @BulkBeings
Change the naming_series field type. and set the data field. I hope It will work

chrome-capture (4)
chrome-capture (4)1652×693 47.7 KB

Thank you!

7

@Mohammadali I’m sorry, I don’t understand… you should explain a bit more like Yamen

8

@Mohammadali 's explanation is basically the first part of my explanation, and it will result in the following

Domestic invoice - INV-001
Domestic invoice - INV-002
Export invoice - INV-EX-001

Also I don’t think it’s a good practice or even possible to modify the options of the naming_series field from “customize form” doctype. Instead, you can directly add your naming series in the Document Naming Settings and the options will automatically update.

9

@Yamen_Zakhour

I forgot to mention, with the above logic, our index numbers are incremented on creation of a sales invoice which by that time is a draft. Not all drafts get submitted and you want to be able to reuse numbers while still maintaining a chronological order of invoice numbers and their dates

Can’t we just change onload to something like onsubmit to achieve this? Does it require more code than this?

10

Then maybe you can give draft documents a temporary name like temp-inv-0001 then create a server script on submit to Rename Doc

In that case,

  1. There is no need for our additional fields
  2. Set the default naming series to “Temp-INV-####”
  3. Create a server script on submit that will;
    3.a. Get last doc from invoiceIndex
    3.b. Get the last_doc.index
    3.c. Check customer territory if it’s export or not
    3.d. Rename the current doc to “INV-last_doc.index” or “INV-EX-last_doc.index”
    3.e. Delete all docs in invoiceIndex
    3.f. Create a new doc in invoiceIndex with the index of last_doc.index + 1
2 Likes
11

Go through that link

12

Just want to verify

Here is my server script(generated by GPT):

import frappe
from frappe.model.document import Document

@frappe.whitelist()
def update_invoice_and_index(doc, method):
    # Get last doc from invoiceIndex
    last_doc = frappe.get_list("invoiceIndex", fields=["index"], order_by="creation DESC", limit=1)

    if last_doc:
        last_index = last_doc[0].index
    else:
        last_index = 0

    # Check customer territory if it's export or not
    is_export = frappe.get_value("Customer", doc.customer, "territory") == "Export"

    # Rename the current doc
    new_name = "INV-EX-" + "{:03d}".format(last_index + 1) if is_export else "INV-" + "{:03d}".format(last_index + 1)
    doc.name = new_name

    # Delete all docs in invoiceIndex
    frappe.db.sql("DELETE FROM `tabinvoiceIndex`", as_dict=True, update=None, debug=False, ignore_permissions=True)
    
    # Create a new doc in invoiceIndex with the index of last_doc.index + 1
    invoice_index = frappe.new_doc("invoiceIndex")
    invoice_index.index = last_index + 1
    invoice_index.insert()

Here is the created doctype Module name and docType name is. InvoiceIndex

Screenshot 2024-04-17 at 9.51.33 PM
Screenshot 2024-04-17 at 9.51.33 PM2562×892 116 KB

I created a temporary naming series and assigned it as default

@Yamen_Zakhour Let me know if anything seems wrong!

13

Remove these

import frappe
from frappe.model.document import Document

@frappe.whitelist()
def update_invoice_and_index(doc, method):

Then fix the indentation.

Also you are adding to the index during the rename process, and then adding another during invoiceIndex creation, choose one only.

Edit, fix the case for “invoiceIndex” to “InvoiceIndex”

To delete docs;

docs = frappe.get_all('InvoiceIndex', fields=['name'])

for doc in docs:
    frappe.delete_doc('InvoiceIndex', doc.name, force=1)
    frappe.db.commit()
1 Like
14

Also you are adding to the index during the rename process, and then adding another during invoiceIndex creation, choose one only

I think it’s correct… on renaming we don’t save it to the variable… so, it works right

15

This is the final working server side script:

custom_index_doctype_name = "invoiceIndex";
if not doc.name.startswith("INV-"):
    current_year_last_two_digits = frappe.db.sql(
        """
        SELECT RIGHT(YEAR(NOW()), 2)
    """,
        as_dict=False,
    )[0][0]

    # Extract the last two digits of the next year
    next_year_last_two_digits = frappe.db.sql(
        """
        SELECT RIGHT(YEAR(NOW()) + 1, 2)
    """,
        as_dict=False,
    )[0][0]

    last_doc = frappe.get_list(
        custom_index_doctype_name, fields=["index"], order_by="creation DESC", limit=1
    )
    if last_doc:
        last_index = last_doc[0].index
    else:
        last_index = 0

    is_export = (
        frappe.db.get_value("Customer", doc.customer, "gst_category") == "Overseas"
    )

    # Rename the current doc
    new_name = (
        "INV-EX-"
        + str(last_index + 1).zfill(3)
        + "-"
        + current_year_last_two_digits
        + "-"
        + next_year_last_two_digits
        if is_export
        else "INV-"
        + str(last_index + 1).zfill(3)
        + "-"
        + current_year_last_two_digits
        + "-"
        + next_year_last_two_digits
    )
    # doc.name = new_name

    # Save the renamed Sales Invoice
    # doc.save(ignore_permissions=True)
    frappe.rename_doc("Sales Invoice", doc.name, new_name, ignore_permissions=True)

    docs = frappe.get_all(custom_index_doctype_name, fields=["name"])

    for d in docs:
        frappe.delete_doc(custom_index_doctype_name, d.name, force=1)

    invoice_index = frappe.new_doc(custom_index_doctype_name)
    invoice_index.index = last_index + 1
    invoice_index.insert()

Thanks @Yamen_Zakhour!

1 Like