How to make a table field searchable in another doctype

Hey guys,
So I created a new Doctype named Guests which has a simple form to fill in some information.

The Guests doctype is set as a table in Event doctype and is basically filled in from there. However, the table field “name” I would like to make it searchable to be able to create new names or search and select already existing ones.

Any idea how to achieve this ?

Thank you in advance for your assistance.

In the guest doctype, there will be a field called “Name”. Set its type as “Link”. Then in Options, put in the name of the doctype you want the information from (search the names from).

Hope this helps.

1 Like

That’s for basic links but I want to do something else.

In the Event doctype, the table is already a link to the Guests. The guests details are added when creating an event. The text field “name” appearing from Guests must be searchable … so the link must be from inside the event doctype

So I am thinking if there is a specific code to add to be able to link a specific field from an already linked table in a doctype.

I hope I make sense what I want to accomplish haha

DONE!

So what I did was in the custom doctype Guests that I created, the “name” field I made it link and as Options I put the name of the custom doctype.

This in return made the “name” field of this custom doctype, searchable in the Event.

Ok, so it’s not done…

It seems that if you link a doctype to itself, you cannot save data.

How can you make a specific custom field searchable in another doctype ?

One way is to use get_query function to set a query on child table Link field:

frappe.ui.form.on('parent_doctype', {
    refresh: {
    	frm.fields_dict['child_table_field_name'].grid.get_field('link_field_name').get_query = function (doc, cdt, cdn) {
        	return {
        		query: 'dotted.path.to.query'
	    	}
    	};
    }
}

The query should return a list of two-element lists: [[id, label], ...].

This is getting technical and it beats me… could you do a paid project to achieve this with me ? :slight_smile:

You’ll have to provide more detail on your setup then.

Create the Name doctype with a different name and then link it to the Name Field.

The problem with creating another Name doctype is this:

The data is entered from within the Event doctype - the employees will never enter the data from within the Custom Doctype.

So if I make another name doctype, how do I link it inside the Event doctype as well ? Also, in the Event doctype I would like to automatically fetch specific fields related to that specific “name” record previously added

Since Guest is already linked to Event, you don’t need to explicitly link Name to Event.

The problem is, if you have a returning guest who is already in the records, you cannot add him again. So you must search for the guest… that’s why I want the name field to be searchable.

You cannot add the same name if it’s already added some time before…

That’s because you have many-to-many relationship between Guest and Event. You have to create an additional linking table to model this relationship. Then, when you create a new Event you will be filling this new link table on the Event form with the Guest data you will be able to search for or create anew.

The problem will be your buisiness requirement that your employees shoud only be able to fill in the Guest data from the Event form. You will need to write custom logic, which will intercept the data you enter in the link table on the Event form and will handle creation of the new Guest entries without redirecting the user to the Guest entry form.

Ah we don’t need to write the custom logic … I am only saying that the user will only enter the guest data from within the event.

Done and achieved with custom script thanks to @shah_kalpit

Also special thanks to @igrekus

1 Like

Do you mind sharing the solution?

You’re welcome!

Still on this matter, we’ve just discovered an issue.

Although you can search for previously added records, selecting one and saving the doctype will give you the error: Duplicate name

When editing the Custom Doctype for the field “name” to uncheck the option Unique, saving the doctype does not save the uncheck option.

Is this a known behaviour for the field option Unique ?

The Custom Script solution:

frappe.ui.form.on("Guests", {
   name1: function (frm,cdt,cdn) {
       frappe.call({
               method: "frappe.client.get_value",
               args: {
                   "doctype": "Guests",
                   "filters": {
                       'name1': locals[cdt][cdn].name1
                   },
                   "fieldname": ['height', 'weight', 'shoe_size','cert_level','no_dives','last_dive']
               },
               callback: function (r) {
                   if (r.message != undefined) {
                       locals[cdt][cdn].height = r.message.height;
                       locals[cdt][cdn].weight = r.message.weight;
                       locals[cdt][cdn].shoe_size = r.message.shoe_size;
                       locals[cdt][cdn].cert_level = r.message.cert_level;
                       locals[cdt][cdn].no_dives = r.message.no_dives;
                       locals[cdt][cdn].last_dive = r.message.last_dive;
                       cur_frm.refresh_fields('guests');
                   }
               }
           });
   }
})

Could be something related to the custom script ? I can’t tell :slight_smile:

1 Like

It’s a great help, thanks!