Need to loop the lines and add condition in query class

Hi Team,

Need a Urgent help…

i have a scenario for where… when a if condition satisfies then for a particular sales order, the lines whose product status is ended will show up in the form “WHSLoadPlanningWorkbench”

below are the code

if (condition)

qbdsProdTable = qbdsSalesLine.addDataSource(tableNum(ProdTable));
qbdsProdTable.addLink(fieldNum(SalesLine, InventRefId), fieldNum(ProdTable, ProdId));
qbdsProdTable.addRange(fieldNum(ProdTable, ProdStatus)).value(enum2Str(ProdStatus::Completed));


it is working as expected. but for the else condition, it should check all the lines of the sales order, if all the lines product status are completed then only all line records should visible in form “WHSLoadPlanningWorkbench”. else there would be no record to be visible.

it will be like

if(some condition to check the prodstatus of all lines completed or not )

     if(all lines prodstatus completed)
      records to be visible in the WHSLoadPlanningWorkbench form.
    else no record to be visible

i am not sure how to achieve this else part in query. please help me out in this… it is better if i get the piece of code for reference.

i have written this particular code in executeSalesLineQuery() method of ExtensionOf(formDataSourceStr(WHSLoadPlanningWorkbench, WHSInventTransSumDimSales).
please help me out.
thanx in advance.

Didn’t you already have a solution in your thread Want to add condition in query filter?

HI Martin,
it actually didnot work
so in execute query of ExtensionOf(formDataSourceStr(WHSLoadPlanningWorkbench, WHSInventTransSumDimSales). i wrote below line code

        //Show lines only when ALL are in Ended state else dont show any line          
        SalesId salesIdLocal = element.salesId;
        SalesLine salesLine;
        ProdTable prodTable;
        select firstonly salesLine
				where salesLine.SalesId == salesIdLocal
			notexists join prodTable
				where salesLine.SalesId == prodTable.InventRefId
				&& salesLine.InventTransId == prodTable.InventRefTransId               
				&& prodTable.ProdStatus == ProdStatus::Completed;
            qbds.addRange(fieldNum(WHSInventTransSumDim, RecId)).value("0");               

this one working fine unless i get the sales id reference but when i try to open LoadPlanningWorkbench form from warehousemanagement, this code doesnot work as it is not getting sales id reference.
please suggest any solution, how can achieve the same result from warehousemanagement as well

SalesId in the form is filled in only if the form is called with SalesTable or SalesLine as a parameter.
If it’s not called for such a record, your current code doesn’t make logical sense. You’re trying to refer to the caller SalesId even if there is none.
In the previous thread, you wanted data related to SalesLine data source. Isn’t it what you should do?

could you please elaborate a little. i am not getting actually. from warehouse management if i open the form, i am not sure how can i make the same filter which i did in my code

Your code depends on SalesId. I have no idea how you want to “make the same filter” if you don’t have any SalesId. It sounds like a bug in logic, not just in code.

yes i understand, this particular code depends on salesid. but as now form is getting opened directly without any salesid reference, this logic is not working .
but i am not getting what code i should write to filter my records. can you provide me and code for reference?
i wrote the below code
qbds = this.query().dataSourceTable(tableNum(salesLine));
qbdsProd = qbds.addDataSource(tableNum(ProdTable));
qbdsProd.addLink(fieldNum(salesLine, InventTransId), fieldNum(ProdTable, InventRefTransId));
qbdsProd.addLink(fieldNum(salesLine, SalesId), fieldNum(ProdTable, InventRefId));
qbdsProd.addRange(fieldNum(ProdTable, ProdStatus)).value(enum2Str(ProdStatus::Completed));
it is returning me the records whose status is not completed. but i want to check all the sales lines of that particular order then return if all are completed state else return no records.

I still sounds to me as the same problem as in your previous thread, and therefore my answer it the same too.
Let me repeat the suggested solution:

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

You said it didn’t work, but that’s not a problem description I can work with. You would have to tell me more about the problem.