Select expression statement in D365FnO

(0) Share

Report

Posted on by Riyas ahamed F

1,215

Hi all,

I wrote a code below its working fine for me.

headerCopy.ThirdPartyLeaveReturnTransRef    = (select firstonly TransTypeId from MPTransactionsSetup
where  MPTransactionsSetup.TransTypeWorker == MPTransactionTypeWorker::LeaveReturn).TransTypeId;
headerCopy.ThirdPartyLeaveEncTransRef       = (select firstonly TransTypeId from MPTransactionsSetup
where  MPTransactionsSetup.TransTypeWorker == MPTransactionTypeWorker::LeaveEnc).TransTypeId;
headerCopy.ThirdPartyLeaveReqTransReference = (select firstonly TransTypeId from MPTransactionsSetup
where  MPTransactionsSetup.TransTypeWorker == MPTransactionTypeWorker::Leave).TransTypeId;
headerCopy.ThirdPartyTicketEncTransRef      =(select firstonly TransTypeId from MPTransactionsSetup
where  MPTransactionsSetup.TransTypeWorker == MPTransactionTypeWorker::TicketEnc).TransTypeId;
headerCopy.ThirdPartyTicketReqTransRef      = (select firstonly TransTypeId from MPTransactionsSetup
where  MPTransactionsSetup.TransTypeWorker == MPTransactionTypeWorker::TicketEnc).TransTypeId;

headerCopy.insert();

Output :

The question is my TL rejected in code review "Don't use select statement expression and use single select"

so that i did it an another way, below my code.

select firstonly setup
      where (setup.TransTypeWorker == MPTransactionTypeWorker::LeaveReturn
    || setup.TransTypeWorker == MPTransactionTypeWorker::LeaveEnc
    || setup.TransTypeWorker == MPTransactionTypeWorker::Leave
    || setup.TransTypeWorker == MPTransactionTypeWorker::TicketEnc
    || setup.TransTypeWorker == MPTransactionTypeWorker::TicketEnc);

    headerCopy.ThirdPartyLeaveReturnTransRef     = setup.TransTypeId;
    headerCopy.ThirdPartyLeaveEncTransRef        = setup.TransTypeId;
    headerCopy.ThirdPartyLeaveReqTransReference  = setup.TransTypeId;
    headerCopy.ThirdPartyTicketEncTransRef       = setup.TransTypeId;
    headerCopy.ThirdPartyTicketReqTransRef       = setup.TransTypeId;
    headerCopy.insert();

Below output.

issue : its showing all leave once select lookup its showing correct value. i want to once inserted show the correct status.

As shown in the first screen shot i want so that what am i missing in second code ?

please give me your idea.

Thanks

I have the same question (0)

All responses (4)

Answers (3)

Sort by

Verified answer

nmaenpaa 101,172 Moderator on at

Like (1)

Report
Copy link

Link copied!

You should have just one select statement. But since you need many records, it must not be a "firstonly" select statement.

Your original code selects 5 different records with 5 different select statements. Now you need to select the same 5 records with one select statement. Then iterate the result, and use if statements to set correct field in headerCopy table, based on setup.TransTypeWorker.

You can also ask directly from the person who did the code review, I'm sure they can help you.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 239,784 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
It should be completely obvious that your code is wrong. You clearly assign the same value (setup.TransTypeId) to all fields, which isn't what you want.

I don't know your MPTransactionsSetup table. If there is a single record for each TransTypeWorker, simply iterate the table and assign values to the right field based on the type:

while select TransTypeId, TransTypeWorker from setup // where clause if needed { switch (setup.TransTypeWorker) { case MPTransactionTypeWorker::LeaveReturn: headerCopy.ThirdPartyLeaveReturnTransRef = setup.TransTypeId; break; case MPTransactionTypeWorker::LeaveEnc: headerCopy.ThirdPartyLeaveEncTransRef = setup.TransTypeId; break; ... } }

If there may be more records for the same type, you'll need to use 'group by' (and decide which TransTypeId you want to take).

Notice that the code is not just more efficient, but also easier to write and read.

Was this reply helpful? Yes No
Verified answer

ergun sahin 8,826 Moderator on at

Like (1)

Report
Copy link

Link copied!
while select setup where (setup.TransTypeWorker == MPTransactionTypeWorker::LeaveReturn || setup.TransTypeWorker == MPTransactionTypeWorker::LeaveEnc || setup.TransTypeWorker == MPTransactionTypeWorker::Leave || setup.TransTypeWorker == MPTransactionTypeWorker::TicketEnc || setup.TransTypeWorker == MPTransactionTypeWorker::TicketEnc) { switch (setup.TransTypeWorker) { case MPTransactionTypeWorker::LeaveReturn: headerCopy.ThirdPartyLeaveReturnTransRef = setup.TransTypeId; break; . . . case MPTransactionTypeWorker::TicketEnc: headerCopy.ThirdPartyTicketEncTransRef = setup.TransTypeId; break; } } headerCopy.insert();

Was this reply helpful? Yes No
Riyas ahamed F 1,215 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your valuable answer.

And, in your three answer I learnt interesting things in while loop condition switch case.

Again thank you very much sir(Nikolaos Mäenpää , Martin Dráb, Ergün Şahin). :)

Was this reply helpful? Yes No