Want to add condition in query filter

Hi Team,

with the previous requirement if checkbox(Show Partial Salesorder) is enable then, it was required to show the record of sales line with “prod status completed” as a filter in the load planning work bench form. so below was the code which was working fine

qbdsSalesLine = this.query().dataSourceTable(tableNum(SalesLine));
if(!showPartialSalesOrders.checked())
{              
	qbdsProdTable = qbdsSalesLine.addDataSource(tableNum(ProdTable));
	qbdsProdTable.addLink(fieldNum(SalesLine, InventRefId), fieldNum(ProdTable, ProdId));
	qbdsProdTable.relations(false);
	qbdsProdTable.joinMode(JoinMode::InnerJoin);
	qbdsProdTable.addRange(fieldNum(ProdTable, ProdStatus)).value(enum2Str(ProdStatus::Completed));
}

but now the requirement again changed
in which if the checkbox(Show Partial Salesorder) is unchecked then it will check the prodstatus of all the salesline. if it the status is completed for all the lines then only it will show all the lines records else it should not show anything in the load planning work bench form.
i am not getting how i will put condition to check all the line records with prod status completed. as i cannot add the range here.

please suggest any valuable solution.

Thanx in advance.

What do you mean by “all” lines? It surely doesn’t mean all sales order line in the system.

it means the lines present in the particular sales order.

Couldn’t you use something like this?

select SalesLine
	not exists join SalesTable
		where SalesTable.SalesId == SalesLines.SalesId
		join SalesLineForCheck
			where SalesLineForCheck.SalesId == SalesTable.SalesId
			   && SalesLineForCheck.ProdStatus != ProdStatus::Completed

I’m showing it as a select statement just to make it easier to understand clearer. We surely can implement the same with Query* classes.

Thank you Martin. I am so grateful to you.
but the problem is I wanted the code in query, as I am a fresher I am able to write the select statement but not able to understand how to convert it into query.
could you please convert the same or the below part in to query , if you do not mind.
SalesLineForCheck.ProdStatus != ProdStatus::Completed

thanx in advance

salesLineForCheckQbds.addRange(fieldNum(SalesLine, ProdStatus))
    .value(SysQuery::valueNot(ProdStatus::Completed));
1 Like