Custom Script set_query() for custom field in Sales Order Item

Frappe Framework Custom Script
1

Hi,

I am trying to get list of Valid Schemes (custom_field) for Item-code with start and end date.
I have a Scheme Master date which has Item_code, Start and end Date. Now Start and end date fields in scheme master are optional. In case of null values those scheme are validate anytime. The schemes with start and end dates mentioned are valid only for that duration of time.

I want to populate the scheme field in Sales Order Item - with all the schemes that are made for that item and have either blank start and end date or if they have a value for start and end date mentioned that those date have to be validated from transaction date.

The below code only populates those schemes that have Start and end dates. How can I get those schemes that have blank start and end dates and just Item_codes ?

frappe.ui.form.on(“Sales Order”, “onload”, function(frm, cdt, cdn){

      frm.set_query("scheme_name", "items", function(doc, cdt, cdn){
                 doc = locals[cdt][cdn];
                 return {
                     filters: [
                            ['Scheme Master','item_code','=',doc.item_code],
                            ['Scheme Master','end_date','>=',doc.transaction_date],
                            ['Scheme Master','start_date','<=',doc.transaction_date]
                              ]
                         };
                 });

});

Please help.

Thanks & Regards
Sonali

2

If your conditions are complex then you can use query.

e.g.

3

Can you please elaborate on what to do If I need null values as well along with the conditions. Like in this case If I need all schemes with end_date as null or end_date >= transaction_date.

4

Check SQL NULL Functions

your query will be like

select name from `tabScheme Master` 
where isnull(end_date) or end_date >= transaction_date
5

Hello Sangram,
I want to fetch the item group from database using set_query .how this possible.?

6

what you want to achieve

  1. using set_query you can pass your custom filters that will give you your results
  2. If you really want to process some complex data and filter out you can write get_query to python end point which will fetch you custom and more precisely the way you want.
7

Suppose i create one item group like this
ABC==>parent group,
DEF==>sub parent ,
GHI==>child

So whenever user create new item and select the item group then he can only able to see the child element and below sub-parent

it
it959×404 20.7 KB

so i want to show parent , grant parent also like this

GHI ==>this is child
All Item Group==>ABC==>DEF

this is my tree structure

itg

Ho to acheived this ,using custome script or in another way?please help

Thanks & Reagards
Satish M.

8

You want to show Parent, Sub parent and child that is default working
if you don’t want to show any group node

    frm.set_query("warehouse", function() {
    			return {
    				"filters": {
    					"company": frm.doc.company,
    					"is_group": 0
    				}
    			};
    		});
9

Yes this i know actually my problem is diff. when user select the item_group
then there i want to display the below structure like this
child
all item group–>parent_group–>sub_parent like this

EX:–
GHI
all item group -->ABC–>DEF

like this i want

New%20Item%201%20-%20New%20Item%201
New%20Item%201%20-%20New%20Item%201792×542 28.3 KB

10

write custom get_query

frm.fields_dict['shipping_address_name'].get_query = function(doc) {
					return {
						"query": "dairy_erp.customization.sales_invoice.sales_invoice.get_customer_address",
						filters: {'customer': doc.customer}
					}
				}

Your Python endpoint should be returning attributes what you want in your case

Name, Parent account and you will need its parent too that you will have to mention in your SQL

11

Can You please tell mi step by step how i do this?