Convert X++ select statement to query (URGENT)

(0) Share

Report

Posted on by Community Member

I need to convert the following X++ select statement (working code) into Query to permanently filter PO form (already attempted, looking for other ways to implement/optimize/best practice):

while select purchReqTable where purchReqTable.ApprovalGroupId == 'HSE'
{
select purchReqLine where purchReqLine.PurchReqId == purchReqTable.PurchReqId;

if(purchReqLine.RecId>0)
{
while select purchTable where purchTable.PurchId == purchReqLine.PurchId
{
i++;
info(int2str(i) + ') ' + purchTable.PurchId);
}
}
}

info('Total ' + int2str(i));

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Suggested answer

Martin Dráb 237,878 Most Valuable Professional on at

Like (0)

Report
Assuming that you want to filter the PurchTable datasource, you need something like this:

QueryBuildDataSource purchTableDs = purchTable_ds.queryBuildDataSource(); QueryBuildDataSource reqLineDs = purchTableDs.addDataSource(tableNum(PurchReqLine)); QueryBuildDataSource reqTableDs = reqLineDsě.addDataSource(tableNum(PurchReqTable)); reqLineDs.relations(true); reqLineDs.joinMode(JoinMode::ExistsJoin); reqTableDs.relations(true); reqTableDs.addRange(fieldNum(PurchReqTable, ApprovalGroupId)).value(queryValue('HSE'));

Was this reply helpful? Yes No
Suggested answer

Muthusamy 4 on at

Like (0)

Report

Hi,

You have the table relation of

1. PurchReqLine.PurchReqTable = PurchReqTable.RecId

2. PurchLine.PurchReqLineRefId = PurchReqLine.LineRefId

Create the join query and loop it.

You can get the PurchId from PurchLine Table otherwise you can link PurchTable with PurchLine Table and get the data.

Hope this relation will help you to write query.

Thanks

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Query returned empty after fixing error from purchTable_ds.queryBuildDataSource() and initialization error

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

@Martin

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Working Code (moved editted code from executeQuery to init method of PurchTable DataSource):

//super();

Query query = new Query();

QueryBuildDataSource purchTableDs;

QueryBuildDataSource reqLineDs;

QueryBuildDataSource reqTableDs;

;

purchTableDs = query.addDataSource(tableNum(PurchTable));

reqLineDs = purchTableDs.addDataSource(tableNum(PurchReqLine));

reqTableDs = reqLineDs.addDataSource(tableNum(PurchReqTable));

reqLineDs.relations(true);

reqLineDs.joinMode(JoinMode::ExistsJoin);

reqTableDs.relations(true);

reqTableDs.addRange(fieldNum(PurchReqTable, ApprovalGroupId)).value(queryValue('HSE'));

purchTable_ds.query(query);

Many thanks to you all + @Martins

Was this reply helpful? Yes No
Martin Dráb 237,878 Most Valuable Professional on at

Like (0)

Report

I wouldn't drop the standard query as you do; I would modify it as shown in my code snippet. You risk breaking a lot of things configured in datasources and done by code running before your code.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I agree with you... but the existing form (PurchTable) was duplicated (and renamed - HSE Purchse Orders) specifically for this purpose (HSE requirements).

Was this reply helpful? Yes No