How to filter form datasource based on query

I need to filter records in grid based on this coding

 while select saleline join stab join indim where stab.SalesId==saleline.SalesId && indim.InventDimId==saleline.InventDimId && saleline.ItemId==_sp.PartNumber && saleline.SalesStatus==SalesStatus::Backorder

Well, so what do you need from us? We can’t answer your question if you don’t ask any.

How to convert this sql statement to query using 3 tables

You mention a grid, therefore the first step is creating a form, adding three datasource (one for each table) and configuring joins between them.

You already used addRange() in your other threads, therefore you should already know how to add ranges for ItemId and SalesStatus.

I don’t like this answer but there are hundreds of samples about this, just google 30 seconds…

 qbds1=query.addDataSource(tablenum(SalesProjectionLines));
    qbds1.addRange(fieldnum(SalesProjectionLines,Partnumber)).value('1119.0161');
    qbds2=qbds1.addDataSource(tablenum(SalesLine));
    qbds2.addRange(fieldnum(SalesLine,SalesStatus)).value(enum2str(SalesStatus::Backorder));
    qbds2.joinMode(JoinMode::InnerJoin);
    qbds2.relations(true);
    
    qbds3=qbds2.addDataSource(tablenum(InventDim));
    
    qbds3.joinMode(JoinMode::InnerJoin);
    qbds3.relations(true);
    
    queryRun = new QueryRun(query);

in execute query shall i need to add this coding??

No, you shouldn’t. The normal approach it what I explain above - when you create form data sources, the query is generated automatically and all what you would do is adding ranges those two ranges.

If you insist on writing code for the whole query rather then letting AX to do it for you, you should do it in init() and not every time when the query is executed.

Also, what’s the point of constructing QueryRun? You said you wanted to filter a grid, not that you wanted to execute the query by yourself.

I have items in salesline for that item i need to sum remainsalesphysical with status open order and goupby itemid and inventcolorid.

I added 3 tables in my form as datasource so how should i do group and sum remainsalesphysical ? can i get an example

There is no sum nor group by in the query you said you wanted. Are you still talking about the same thing?

  qbr1.value('1119.0161');
    qbr2.value(enum2str(Salesstatus::Backorder));

    SalesLine_ds.query().dataSourceTable(Tablenum(SalesLine)).addGroupByField(fieldnum(Salesline,ItemId));
    SalesLine_ds.query().dataSourceTable(Tablenum(SalesLine)).addSelectionField(fieldnum(Salesline,RemainSalesPhysical),selectionfield::Sum);
    InventDim_ds.query().dataSourceTable(Tablenum(InventDim)).addGroupByField(fieldnum(InventDim,InventColorid));
     InventDim_ds.query().dataSourceTable(Tablenum(InventDim)).relations(true);
     

I added this coding in executequery of salesline

pastedimage1551170241452v1.png

i need to show all sales id’s for this item in line grid with revision a, how could i add that?

I added salesline1 and inventdim1 ds again with salesline and inventdim table in my form and in salesline1 executequery method i added this coding

str rev,itid;
;
rev=Inventdim.InventColorId;//InventDim_InventColorId.valueStr();
itid=Salesline.ItemId;//SalesLine_ItemId.valueStr();
SalesLine1_ds.query().dataSourceTable(Tablenum(SalesLine)).addRange(fieldnum(SalesLine,ItemId)).value(Salesline.ItemId);
InventDim1_ds.query().dataSourceTable(Tablenum(InventDim)).addRange(fieldnum(InventDim,InventColorid)).value(Inventdim.InventColorId);
InventDim1_ds.query().dataSourceTable(Tablenum(InventDim)).relations(true);

but its fetching records with revision A and when i select b revision in header its not changing the grid value