How to create Filter Doctype

Devershi_Vashistha · July 26, 2022, 7:25am

Hello everyone,

I am new to custom scripting that’s why need some help.

Let me explain my problem,
What I want is to create a filter which can filter out customer email ID based on 5 parameters and add them to email group member doctype.

Filters are:

Country (Address Doctype)
City (Address Doctype)
Intent (Session History Collection Doctype)
Package (Session History Collection Doctype)
From Date (Session History Collection Doctype)
To Date (Session History Collection Doctype)

So there are 2 doctypes I want to filter out customer email from.
First is Address doctype.

From here City and Country will be used as filters.
And second is Session History Collection doctype which is a doctype I created.

From here Intent, Package/Service of interest and current date time will be used as filters.

Now, here is the Filter doctype.

I want to write a custom script or to know a way so that when I add these filters (Its not mandatory to fill all or any filters) and hit save It first runs through address doctype and session history collection doctype and get email ID which fulfill all the present filters.

I am thinking what we can do is first get the list of email ID from address doctype then get another list of email ID from session history collection doctype and then check whichever email ID is present in both list can be used as final email.

After then we will be having a list of email ID then as we also have mentioned email group it create that many email group member in email group member doctype.

Thank you for your time.
Any help is appreciated Thank you.

Devershi_Vashistha · July 26, 2022, 12:24pm

Hello @kid1194 can you please help?
Or anyone else any help is valuable.

kid1194 · July 26, 2022, 2:16pm

@Devershi_Vashistha Sure buddy. I’ll read your post and get back to you.

kid1194 · July 26, 2022, 2:32pm

@Devershi_Vashistha After reading your post, I can tell you that what you want can be done easily.

For now I want you to answer the following questions:

Do you want the code to run on a specific form or you want it to run manually by clicking a button?
Do you want to create an email group member for each email filters?
If Q.2 is yes, will the email groups be created manually by you?
If Q.2 is yes, will you save the filtered list anywhere or you want it just to be displayed? And where do you want to save it or display it?

Also, please post a screenshot of the Address Doctype fields.

I’m already creating the code for you.

Devershi_Vashistha · July 26, 2022, 3:00pm

Thank you @kid1194 ,
For question 1 - I want the code to run when we hit save button on filter doctype or a new button is also fine.
Q2 - yes so for all emails which got filtered I want to create an email group member for that specific email group which is mentioned in filter.
Q3 Yes manually
Q4 I don’t want the filtered list any where for now, just to add them as email group member

kid1194 · July 26, 2022, 3:09pm

@Devershi_Vashistha Great. Please also post a screenshot of the Email Group Member Doctype fields.

Devershi_Vashistha · July 26, 2022, 3:10pm

Yes sure @kid1194 ,

kid1194 · July 26, 2022, 3:51pm

@Devershi_Vashistha Final thing before I post the code, how is the naming of the Email Group Member doctype?

Devershi_Vashistha · July 26, 2022, 3:56pm

@kid1194 hash is written in autonaming but it shows it by email id.

kid1194 · July 26, 2022, 4:01pm

@Devershi_Vashistha I thought that a button called Apply Filter appears after the Filter is saved, but what if the button is clicked more than once to re-apply the filter? Should the existing emails at Email Group Member be removed first?

Devershi_Vashistha · July 26, 2022, 4:02pm

@kid1194 for that can we check for duplication?
if email is present in email group member for that email group it shouldn’t add it again.

kid1194 · July 26, 2022, 4:06pm

@Devershi_Vashistha Ok. Wait for the code.

kid1194 · July 26, 2022, 4:27pm

@Devershi_Vashistha This the code. Please let me know if it doesn’t work for you.

At the end, there is a log code to post to the console the total existing emails, total filtered emails and the total posted emails. Please remove after the code is working.

frappe.ui.form.on("Filter", {
    refresh: function(frm) {
        if (!frm.is_new()) {
            frm.add_custom_button('Apply Filter', () => frm.trigger('add_filtered_emails'));
            frm.change_custom_button_type('Apply Filter', null, 'success');
        }
    },
    add_filtered_emails: function(frm) {
        let emails = [];
        let pipe = new Promise ((resolve) => { resolve(frm.doc); });
        pip.then(doc => {
            let filter = {};
            if (!!doc.country) filter.country = doc.country;
            if (!!doc.city) filter.city = doc.city;
            frappe.db.get_list('Address', {
                fields: ['email_id'],
                filters: filter
            }).then(data => {
                $.each(data, function(i, v) {
                    emails.push(v.email_id);
                });
            });
            return doc;
        }).then(doc => {
            let filter = {};
            if (!!doc.intent) filter.intent = doc.intent;
            if (!!doc.package) filter.package = doc.package;
            if (!!doc.start_date && !!doc.end_date) {
                filter.datetime = [
                    'between',
                    [doc.start_date + ' 00:00:00', doc.end_date + ' 00:00:00']
                ];
            } else if (!!doc.start_date) {
                filter.datetime = ['>=', doc.start_date + ' 00:00:00'];
            } else if (!!doc.end_date) {
                filter.datetime = ['<=', doc.end_date + ' 00:00:00'];
            }
            frappe.db.get_list('Session History Collection', {
                fields: ['email'],
                filters: filter
            }).then(data => {
                $.each(data, function(i, v) {
                    if (emails.indexOf(v.email) < 0) emails.push(v.email);
                });
            });
            return doc;
        })
        .then(doc => {
            frappe.db.get_list('Email Group Member', {
                fields: ['email'],
                filters: {
                    email: ['in', emails]
                }
            }).then(data => {
                if (data.length) {
                    $.each(data, function(i, v) {
                        let idx = emails.indexOf(v.email);
                        if (idx >= 0) emails.splice(idx, 1);
                    });
                }
                let docs = [];
                let args = [];
                $.each(emails, function(i, v) {
                    docs.push({
                        doctype: 'Email Group Member',
                        email_group: doc.email_group,
                        email: v,
                    });
                });
                if (docs.length > 200) {
                    for (var i = 0, l = docs.length; i < l; i += 200) {
                        args.push(docs.slice(i, i + 200));
                    }
                } else {
                    args = [docs];
                }
                let result = [];
                $.each(args, function(i, arg) {
                    let ret = frappe.call({
                        type: 'POST',
                        method: 'frappe.client.insert_many',
                        args: {
                            docs: arg,
                        }
                    });
                    result.push(...ret);
                });
                // for debugging purpose only
                console.log(
                    'Total existing members: ' + data.length,
                    'Total filtered emails: ' + emails.length,
                    'Total inserted emails: ' + result.length
                );
            });
        });
    }
});

kid1194 · July 26, 2022, 4:30pm

@Devershi_Vashistha I have updated the code because there was a mistake in it.

Devershi_Vashistha · July 26, 2022, 4:32pm

Ok sure I am checking it.

Devershi_Vashistha · July 26, 2022, 5:07pm

@kid1194 I am trying it and it is not working so I wanted to know will this work if we leave a few fields empty in filter?
will it filter out for just the fields we entered value in?

kid1194 · July 26, 2022, 5:16pm

@Devershi_Vashistha Did you check the console for the log? Can you post a screenshot of it?

I want to know if the filtered emails is the problem or the multiple insert is.

Devershi_Vashistha · July 26, 2022, 5:20pm

I entered these information.

this is an existing address which have the same city and country.

but after clicking on apply filter this didn’t change.

kid1194 · July 26, 2022, 5:23pm

@Devershi_Vashistha I see.

Devershi_Vashistha · July 26, 2022, 5:25pm

does this help