Custom Script to show only a certain asset type in Child Table Field

I have a custom child table in the Project DocType. It is working as expected, with just one thing. In that Child Table, there is a field called asset_name. This field shows as expected all assets to choose from. In my case, when selecting it, it lists all available assets.

I wanted to filter the dropdown list with only the assets of a certain type: Library Media

In the past, I’ve managed to write a custom script to filter a field in a DocType itself, but based on that I don’t seem to get this working in a child table.

This is what I tried:

frappe.ui.form.on("Project Information Media Assets", {
	setup: function(frm,cdt,cdn) {
		frm.set_query("asset_name", function() {
			return {
				filters: [
					["asset_name", "asset_category", "=", "Library Media"]
				]
			}
		});
	}
});

Where

  • Project Information Media Assets is the name of the Child Table
  • Library Media is the asset_category that should be shown when selecting the asset_name field

With the above custom script, no filtering is done in the asset_name-field, it keeps displaying all available assets.

Any insights or help would really be appreciated.

Hi @pedro_vde,

Please try it like:

frappe.ui.form.on("Project Information Media Assets", {
	setup: function(frm,cdt,cdn) {
		frm.set_query("asset_name", function() {
			return {
				filters: [
					["Asset", "asset_category", "=", "Library Media"]
				]
			}
		});
	}
});

Reload and check it.

Thanks.

Try this

frappe.ui.form.on("Project", {
    refresh: function(frm,cdt,cdn) {
        frm.set_query('Project_Info_Media_Assets','asset_name',function(frm){
            return {
                filters:{
                    'asset_category':"Library Media"
                }
            }
        })
    }
})

Syntax is:


frappe.ui.form.on("Parent Doc", {
    refresh: function(frm,cdt,cdn) {
        frm.set_query('child_table_name','field_name_in_child',function(frm){
            return {
                filters:{
                    //your filter
                    'asset_category':"Library Media"
                }
            }
        })
    }
})

Thank you both @NCP and @Shankar_V for your suggestions, but none of them seem to work.

@NCP - your suggestion throws a Debugger Pause in Console on frappe.ui.form.on("Project Information Media Assets", { where it halts the loading. When continuing the asset_name field keeps showing all available assets.

@Shankar_V - your suggestion throws the following errors in the Console at the reload of the page:

script_manager.js:24 ƒ (frm,cdt,cdn) {
        frm.set_query('Project Information Media Assets','asset_name',function(frm){
            return {
                filters:{
                    'asset_category':"Library Media…
r @ script_manager.js:24
a @ script_manager.js:100
(anonymous) @ script_manager.js:118
Promise.then (async)
(anonymous) @ dom.js:265
frappe.run_serially @ dom.js:263
trigger @ script_manager.js:132
(anonymous) @ form.js:499
Promise.then (async)
(anonymous) @ dom.js:265
frappe.run_serially @ dom.js:263
render_form @ form.js:489
(anonymous) @ form.js:458
Promise.then (async)
initialize_new_doc @ form.js:455
trigger_onload @ form.js:433
refresh @ form.js:330
render @ formview.js:108
(anonymous) @ formview.js:92
callback @ model.js:216
success @ request.js:78
200 @ request.js:122
(anonymous) @ request.js:266
i @ jquery.min.js:2
fireWith @ jquery.min.js:2
z @ jquery.min.js:4
(anonymous) @ jquery.min.js:4
load (async)
send @ jquery.min.js:4
ajax @ jquery.min.js:4
frappe.request.call @ request.js:248
frappe.call @ request.js:98
(anonymous) @ model.js:208
with_doc @ model.js:202
fetch_and_render @ formview.js:81
show_doc @ formview.js:76
make_and_show @ formview.js:32
(anonymous) @ formview.js:15
callback @ model.js:173
success @ request.js:78
200 @ request.js:122
(anonymous) @ request.js:266
i @ jquery.min.js:2
fireWith @ jquery.min.js:2
z @ jquery.min.js:4
(anonymous) @ jquery.min.js:4
load (async)
send @ jquery.min.js:4
ajax @ jquery.min.js:4
frappe.request.call @ request.js:248
frappe.call @ request.js:98
with_doctype @ model.js:147
make @ formview.js:12
show @ factory.js:24
render_page @ router.js:206
render @ router.js:185
route @ router.js:106
set_route @ desk.js:170
startup @ desk.js:69
init @ desk.js:29
frappe.start_app @ desk.js:13
(anonymous) @ desk.js:24
i @ jquery.min.js:2
fireWith @ jquery.min.js:2
ready @ jquery.min.js:2
J @ jquery.min.js:2
form.js:1369 Uncaught (in promise) TypeError: Cannot read properties of undefined (reading 'grid')
    at frappe.ui.form.Form.set_query (form.js:1369:27)
    at refresh (project__custom_js:3:13)
    at r (script_manager.js:22:12)
    at a (script_manager.js:100:16)
    at script_manager.js:118:22

and again, all available assets are shown in the asset_name_field

The first difference between both approaches that I’ve noticed is the fact that one starts with reference to the Child Table (Project Information Media Assets) and the other to Parent DocType (Project) what makes me wonder what is the right approach… as I thought that for a child table one would start with a call to the Child Table… no?

@pedro_vde You made a mistake. In syntax as I mentioned child_table_name refers to the fieldname of the child table in parent doc.
Eg:
Consider Sales Invoice and Sales Invoice Item. Here the child table is linked with the fieldname of items in Sales Invoice.

Like that you need to mention the fieldname of the Table in the 3rd line.

Check it, please: Overriding Link Query By Custom Script

Thank you both again for the follow-up!

@Shankar_V I know I’ve made a mistake in the original thread-start on the forum by stating the name of the Child Table wrongly. It is Project Information Media Assets and not the one with the underscores (I wanted to change it to the one with the underscores later to see the difference between original DocTypes/Child Tables and the custom-made ones. I left the idea as this change would break the link to the Child Table that is already in use)

When using your suggestion/syntax I think it should be:

frappe.ui.form.on("Project", {
    refresh: function(frm,cdt,cdn) {
        frm.set_query('Project Information Media Assets','asset_name',function(frm){
            return {
                filters:{
                    'asset_category':"Library Media"
                }
            }
        })
    }
})

But as stated in an earlier post this does not work and keeps throwing these errors in the console:

script_manager.js:24 ƒ (frm,cdt,cdn) {
        frm.set_query('Project Information Media Assets','asset_name',function(frm){
            return {
                filters:{
                    'asset_category':"Library Media…
r @ script_manager.js:24
a @ script_manager.js:100
(anonymous) @ script_manager.js:118
Promise.then (async)
(anonymous) @ dom.js:265
frappe.run_serially @ dom.js:263
trigger @ script_manager.js:132
(anonymous) @ form.js:499
Promise.then (async)
(anonymous) @ dom.js:265
frappe.run_serially @ dom.js:263
render_form @ form.js:489
(anonymous) @ form.js:458
Promise.then (async)
initialize_new_doc @ form.js:455
trigger_onload @ form.js:433
refresh @ form.js:330
render @ formview.js:108
(anonymous) @ formview.js:92
callback @ model.js:216
success @ request.js:78
200 @ request.js:122
(anonymous) @ request.js:266
i @ jquery.min.js:2
fireWith @ jquery.min.js:2
z @ jquery.min.js:4
(anonymous) @ jquery.min.js:4
load (async)
send @ jquery.min.js:4
ajax @ jquery.min.js:4
frappe.request.call @ request.js:248
frappe.call @ request.js:98
(anonymous) @ model.js:208
with_doc @ model.js:202
fetch_and_render @ formview.js:81
show_doc @ formview.js:76
make_and_show @ formview.js:32
(anonymous) @ formview.js:15
callback @ model.js:173
success @ request.js:78
200 @ request.js:122
(anonymous) @ request.js:266
i @ jquery.min.js:2
fireWith @ jquery.min.js:2
z @ jquery.min.js:4
(anonymous) @ jquery.min.js:4
load (async)
send @ jquery.min.js:4
ajax @ jquery.min.js:4
frappe.request.call @ request.js:248
frappe.call @ request.js:98
with_doctype @ model.js:147
make @ formview.js:12
show @ factory.js:24
render_page @ router.js:206
render @ router.js:185
route @ router.js:106
set_route @ desk.js:170
startup @ desk.js:69
init @ desk.js:29
frappe.start_app @ desk.js:13
(anonymous) @ desk.js:24
i @ jquery.min.js:2
fireWith @ jquery.min.js:2
ready @ jquery.min.js:2
J @ jquery.min.js:2
form.js:1369 Uncaught (in promise) TypeError: Cannot read properties of undefined (reading 'grid')
    at frappe.ui.form.Form.set_query (form.js:1369:27)
    at refresh (project__custom_js:3:13)
    at r (script_manager.js:22:12)
    at a (script_manager.js:100:16)
    at script_manager.js:118:22

When I use the button “Add script for Child Table” on the Client Script page, it starts the added setup also with the name of the chosen Child Table and not the Parent DocType. That is what really confuses me.

As I’m new to this kind of “programming”, I’m trying to figure out how the scripting works. It seems that I don’t have the right skills to handle Custom Scripting in a Child Table as I’m struggling with it quite seriously. Hence another thread on this forum: https://discuss.erpnext.com/t/custom-script-help-needed-adding-value-from-main-doctype-in-child-table/87501

Thank you @NCP for the link - as I’m always looking for ERPNext documentation but I seem to forget that I have to look into the Frappe documentation to get any wiser about the core of the system.

I’m quite convinced that both my issues will disappear once I figure out how to handle Custom Scripts, so all your help is really appreciated and more than welcome!

Hi @pedro_vde,
Just for information, asset_category field in Project (Parent level) or Project Information Media Assets (child level) ?

asset_category is a field in the Project Information Media Assets - Child Table

Ok, @pedro_vde,

Please apply it.

frappe.ui.form.on("Project", {
  onload: function (frm) {
   // please check your field name of project_information_media_assets in Project customize form
    frm.set_query("asset_name", "project_information_media_assets", function (doc, cdt, cdn) {
      let row = locals[cdt][cdn];
      return {
        "filters": {
          'asset_category' : "Library Media"
        },
      };
    });
  },
});

Then reload and check it, please.
It’s working on my side v13.

Thanks.

Yes @NCP that’s it! And thanks to you I do know what I did wrong!

I was calling the name of the Child Table but, I needed to use the field name where the Child Table sits on the Parent DocType

so my working script is:

frappe.ui.form.on("Project", {
  onload: function (frm) {
   // please check your field name of project_information_media_assets in Project customize form
    frm.set_query("asset_name", "media_assets", function (doc, cdt, cdn) {
      let row = locals[cdt][cdn];
      return {
        "filters": {
          'asset_category' : "Library Media"
        },
      };
    });
  },
});

What I’ve learned today and thanks to you guys:

  • use the Parent DocType Name on frappe.ui.form.on(
  • when referring to a Child Table use the name of the field that shows the Child Table in the Parent DocType

Just an additional question in regard to the filters. I presume it is possible to add various filters to the same script. Is it just a question by adding an extra line after 'asset_category' : "Library Media" with the extra filter, or should there be a “,” or something else in between? Like:

frappe.ui.form.on("Project", {
  onload: function (frm) {
   // please check your field name of project_information_media_assets in Project customize form
    frm.set_query("asset_name", "media_assets", function (doc, cdt, cdn) {
      let row = locals[cdt][cdn];
      return {
        "filters": {
          'asset_category' : "Library Media"
          'stock_uom' : "Piece"
        },
      };
    });
  },
});