Backing a read only DocType with a database "view". Is it bad practice? Is there a better way?

[MartinHBramwell]

I customised Address with a new field called Route

Also, I created a new DocType, Route Planner, with a child table having a field for Customer.

When users go to select a Customer I want a filter to limit them to only the ones with a specific Route. However, Route is defined on the customer’s Address.

My gimmickry is to create a DocType, Route Customer, having two read only fields: Customer and Route.

I then define a database “view”, tabRoute Customer, with all the same attributes as the new table tabRoute Customer, after dropping it.

The view simply selects from an appropriate join on tabCustomer, tabDynamic Link and tabAddress. With suitable indexing on those two tables this seems efficient, easy to set up and easy to maintain.

---

My questions are:

Is this considered bad practice?

Is there an equally efficient way to use filters on the Customer field in the child table to achieve the choice limitation for users?

[szufisher]

can you provide some screenshot to explain in more details the required logic?

[MartinHBramwell]

Hi szufisher!

Thanks for taking an interest in this concern!

Hopefully this responds to what you are hoping to see.

---

The database view …

DROP TABLE IF EXISTS `tabRoute Customer`;
DROP VIEW IF EXISTS `tabRoute Customer`;
CREATE VIEW `tabRoute Customer` AS
select
          concat(A.delivery_route, "-", A.name) as name
        , A.creation
        , A.modified
        , A.modified_by
        , A.owner
        , 0 as docstatus
        , 0 as idx
        , C.name as cliente           -- Customer
        , A.delivery_route as ruta   -- Route
        , null as _user_tags
        , null as _comments
        , null as _assign
        , null as _liked_by 
    from `tabDynamic Link` DL
         left join `tabAddress`  A on A.name = DL.parent
        right join `tabCustomer` C on C.name = DL.link_name 
    where DL.parenttype = "Address" 
      and DL.link_doctype = "Customer"
;

Route Customer record:

Route Planner record:

Customer Address record:

[ankush]

Why not just use virtual doctypes instead of messing with DB?

https://frappeframework.com/docs/user/en/basics/doctypes/virtual-doctype

[MartinHBramwell]

To be honest, I have already spent so much of my life lost, exhausted and exasperated down ERPNext documentation labyrinths that I am scared to even attempt this.

I read the documentation you point to and immediately find the same old story, “Here’s what you’ll be able to do once you figure out all the things we can’t be bothered to tell you.”

Example: “the following controller code uses a JSON file as the DocType datasource”;

Ok. A concise and understandable interface controller. Good.

But:

• Where am I supposed to put it?
• Can it be a Server Script?
• Controller methods are part of Apps. Must I create an App just to be able to use this?
• If I stick the controller method within the app directories erpnext or frappe, what happens when I move the site? or if I upgrade to a new version? or someone else tries to upgrade to a new version?

[MartinHBramwell]

BTW,
currently watching this tutorial video

Virtual Doctypes Part 1 - Frappe ERPNext

But, it uses Virtual Doctypes within the context of an app.

I do not need an app for what I am doing.

[szufisher]

instead of virtual doctype, simple link field query method is enough, anyway app is needed because server script does not support link query method as of now.

1. client script as below
   
   

frappe.ui.form.on('Route Planner', {
	refresh(frm) {
		frm.set_query("customer", "items", function(doc, cdt, cdn) {
			return {
				query: "zelin_item_configurator.item_configurator.doctype.item_configuration.item_configuration.get_customer_by_route",
				filters: {
					'route': frm.doc.route
				}
			}
		});
	}
})

2. link query method in the app
   
   

@frappe.whitelist()
def get_customer_by_route(doctype=None, txt=None, searchfield=None, start=None, page_len=None, filters=None):	
	route = filters.get('route')

	dl = frappe.qb.DocType('Dynamic Link')
	address = frappe.qb.DocType('Address')
	customer = frappe.qb.DocType('Customer')

	query = frappe.qb.from_(dl
	).left_join(address
	).on(dl.parent == address.name
	).right_join(customer
	).on(customer.name == dl.link_name
	).select(customer.name,address.route
	).where(dl.parenttype == "Address"
	).where(dl.link_doctype == 'Customer'
	).where(address.route == route
	).distinct()
	
	if txt:
		query.where(customer.name.contains(txt))
	
	result = query.run()
	
	return result

3. final result

[MartinHBramwell]

You invested a lot of time preparing that and I really appreciate it.

However, I do not want to create an app, so as to add a link query, solely for the purpose of filtering data entry for one field.

Hopefully future readers of this thread will glean ideas and better understanding of how things work “under the hood”.

Thank you, again!

My database view “trick” solves my problem cleanly, so I am fine for now.
If others are aware of deeper reasons why it’s a mistake, I’ll be glad to learn of their opinions.