Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Variable declaration f...
Finance | Project Operations, Human Resources, ...
Suggested answer

Variable declaration for select query X++

(1) ShareShare
ReportReport
Posted on by Voltes 144
Hello everyone,
 
If I want to run few lines of select query in X++, but the table that I'm using is the same table. For illustration is something like this :
 
1. Run /select sum(salesamount) from salesline join salestable where salesline.salesid == salestable.salesid and salestable.salesstatus == open order/
2. Run /select sum(salesamount) from salesline join salestable where salesline.salesid == salestable.salesid and salestable.salesstatus == delivered/
3. Run /select sum(salesamount) from salesline join salestable where salesline.salesid == salestable.salesid and salestable.salesstatus == invoiced/
 
At each step, I saved the amount result to my custom table's record. Do I need to declare 3 variables salesline and 3 variables salestable ? or 1 variable each table is enough ?
Btw, those 3 will be inside a while statement, which the /where clause/ actually needed to add some more from that where loop.
 
 
Thanks.
  • Layan Jwei Profile Picture
    Layan Jwei 7,340 Super User 2024 Season 2 on at
    Variable declaration for select query X++
    Hi Community member,
     
    Is your issue solved? If yes please verify the answers that helped.
  • Suggested answer
    Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,587 Super User 2024 Season 1 on at
    Variable declaration for select query X++
    For code looks perspective you can add a method with parameter the sales id and type and simply called that which will return required value.
     
    In other cases, as all these status are from header and no 2 status can be there for one sales order you can simply create a SalesLine buffer inside while and based on condition like a switch case just write the code.
  • Raj Borad Profile Picture
    Raj Borad 497 on at
    Variable declaration for select query X++
    Hi,
     
    As per my knowledge, You can use one variable for all the query like,
     
    select sum(salesamount) from salesline join salestable where salesline.salesid == salestable.salesid and salestable.salesstatus == open order/
    yourtable.insert();
     
    select sum(salesamount) from salesline join salestable where salesline.salesid == salestable.salesid and salestable.salesstatus == delivered/
    yourtable.insert();
     
    select sum(salesamount) from salesline join salestable where salesline.salesid == salestable.salesid and salestable.salesstatus == invoiced/
    yourtable.insert();
     
    OR
     
    If you want to use and it suitable your requirement, you can change the query and use the group by on SalesStatus of Sales Table.
     
    OR
     
    You can also create a different method for find the SalesAmount  from SalesLine based on the SalesStatus of SalesTable.
     
    Thank you,
    Raj Borad
  • Martin Dráb Profile Picture
    Martin Dráb 230,569 Most Valuable Professional on at
    Variable declaration for select query X++
    A more efficient approach would be using a single query with group by SalesStatus.
  • Suggested answer
    Layan Jwei Profile Picture
    Layan Jwei 7,340 Super User 2024 Season 2 on at
    Variable declaration for select query X++
    Hi Community member,
     
    If after the first select statement, you saved the value in your new table and u don't want anything more from this select statement, then declaring one variable is enough. However, if for example, you want use the first variable after calling the 2nd select statement then you will need more than one variable to not lose the value.
     
    It's better to share with us the whole code.
     
    Thanks
    Layan Jweihan
    Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future
  • Suggested answer
    MuthukumaranAX Profile Picture
    MuthukumaranAX 2,901 on at
    Variable declaration for select query X++
    If you want to retrieve the sales amount based on the statutes then yes it is better you have 3 different variables.
     
    OR 
     
    you can write a separate method with a sales status parameter to return sales amount. you can call this method as needed and use it.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,883 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,569 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans