web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Supply chain | Supply Chain Management, Commerce / Getting Data from tabl...
Supply chain | Supply Chain Management, Commerce
Suggested Answer

Getting Data from table for specific date range

(1) ShareShare
ReportReport
Posted on by faiz7049 2,430
Hi Experts,
 
In below X++ code, smmSalesTargetTrans table store Target amount and is stored based on each month end date like 31-Jan-2024 Amount 20000 , 28-Feb-2024 Amount 30000.
The requirement is to get Target value for previous month, current week, previous week.
Currect week and previous week date is coming from parameter FromWeek, ToWeek and PrevFromWeek, PrevToWeek.
 
Difficulty I am facing to get Target value. Please help me.
 
 
 
TransDate   transDate   = contract.parmTransDate();
        FromDate   FromWeek    =   contract.parmThisWeekFrom();
        ToDate      ToWeek      =   contract.parmThisWeekTo();
        FromDate    PrevFromWeek    =   contract.parmPrevWeekFrom();
        ToDate      PrevToWeek      =   contract.parmPrevWeekTo();
        FromDate    fromDate    = dateStartMth(transDate);
        ToDate      toDate      = transDate;
        int         noOfFridayCurrMth=0;  
        int         noOfFridayPrevMth=0;
        //Previous Month
        FromDate    startPrevMthDate    =dateStartMth(prevMth(transDate));
        ToDate      endPrevMthDate      =dateEndMth(prevMth(transDate));
        //Current Month
        FromDate    startMthDate    = dateStartMth(transDate);
        ToDate      endMthDate     = dateEndMth(transDate);
		
		
		
		 HcmWorker           hcmWorker;
        smmSalesUnit        smmSalesUnit;
        DirPartyTable       dirPartyTable;
        smmSalesTarget      smmSalesTarget;
        smmSalesTargetTrans smmSalesTargetTrans;

        while select SalesPersonWorker, count(RecId)
            from smmSalesTarget
            group by smmSalesTarget.SalesPersonWorker, smmSalesUnit.ParentId, dirPartyTable.Name
            join ParentId, count(RecId)
                from smmSalesUnit
                where smmSalesUnit.SalesUnitId  == smmSalesTarget.SalesUnitId
                    && smmSalesUnit.Active      == 1
            join sum(AmountMST)
                from smmSalesTargetTrans
                where smmSalesTargetTrans.RefRecId      == smmSalesTarget.RecId
                    && smmSalesTargetTrans.TargetDate   >= startPrevMthDate //Current Month Start Date
                    && smmSalesTargetTrans.TargetDate   <= endMthDate   //Current Month End Date
            join count(RecId)
                from hcmWorker
                where hcmWorker.RecId   == smmSalesTarget.SalesPersonWorker
            join Name, count(RecId)
                from dirPartyTable
                where dirPartyTable.RecId   == hcmWorker.Person
        {
            tmpTable.initValue();
            tmpTable.SalesPersonWorker         = smmSalesTarget.SalesPersonWorker;
            //tmpTable.Product                   = smmSalesUnit.CustGroup;
            tmpTable.Region                    = smmSalesUnit.ParentId;
            
            // Current Month Target
            if (smmSalesTargetTrans.TargetDate >= startMthDate && smmSalesTargetTrans.TargetDate <= endMthDate)
          {
                tmpTable.SalesMonthlyTarget        = smmSalesTargetTrans.AmountMST;

                if (tmpTable.SalesMonthlyTarget)
                {
                    //Daily Target
                    tmpTable.SalesDailyTarget      = smmSalesTargetTrans.AmountMST/(noOfDaysCurrMth  - noOfFridayCurrMth);
   
                }
           }

            // Previous Month Target
            if (smmSalesTargetTrans.TargetDate >= startPrevMthDate && smmSalesTargetTrans.TargetDate <= endPrevMthDate)
            {
                tmpTable.SalesPrevMonthlyTarget = smmSalesTargetTrans.AmountMST;
               
            }
           
            // Current Week Target
            if (FromWeek!=dateNull() && ToWeek!=dateNull() && smmSalesTargetTrans.TargetDate >= startMthDate && smmSalesTargetTrans.TargetDate <= endMthDate)
            {
                tmpTable.SalesWeeklyTarget = (smmSalesTargetTrans.AmountMST)/4;
            }

            // Previous Week Target
            if (PrevFromWeek!=dateNull() && PrevToWeek!=dateNull() && smmSalesTargetTrans.TargetDate >= startPrevMthDate && smmSalesTargetTrans.TargetDate <= endPrevMthDate)
            {
                tmpTable.SalesPrevWeeklyTarget = (smmSalesTargetTrans.AmountMST)/4;
            }
            tmpTable.SalesMan                  = dirPartyTable.Name;
Thanks,
Faiz
Categories:
Dynamics 365 Supply Chain Management
I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 238,795 Most Valuable Professional on at
    Can you describe your problem, please? Just showing a piece of code doesn't tell us what you're struggling with and where.
  • faiz7049 Profile Picture
    faiz7049 2,430 on at
    Thank you, Martin for reply.
     
    We have smmSalesTargetTrans table store Target amount and is stored based on each month end date like 31-Jan-2024 Amount 20000 , 28-Feb-2024 Amount 30000 with Salesman.
     
    To get Monthy Target , Daily Target I can get as long as smmSalesTargetTrans table has. Difficulty is to get Weekly Target, Previous Week Target, Previous Month Target.
     
    In parameter we have 5 inputs. Report Date is used to get Daily Actual and Daily Target =Monthly/Number of working days.
     
     
     
    Report
     
  • faiz7049 Profile Picture
    faiz7049 2,430 on at
    Hi Martin,
     
    Please see smmSalesTargetTrans Target form in below screenshot. Target is defined by monthly (each month end date). Some target is not there because this is dev environment.
     
  • faiz7049 Profile Picture
    faiz7049 2,430 on at
    Hi Experts,
     
    For below X++ code. I am getting only one records. Could you please help me out.
     
     
    HcmWorker           hcmWorker;
        smmSalesUnit        SmmSalesUnit;
        DirPartyTable       dirPartyTable;
        smmSalesTarget      SmmSalesTarget;
        smmSalesTargetTrans SmmSalesTargetTrans,SmmSalesTargetTransNew;

        while select SalesPersonWorker, count(RecId)
            from SmmSalesTarget
          group by SmmSalesTarget.SalesPersonWorker, SmmSalesUnit.ParentId, dirPartyTable.Name
            join ParentId, count(RecId)
                from SmmSalesUnit
                where SmmSalesUnit.SalesUnitId  == SmmSalesTarget.SalesUnitId
                    && SmmSalesUnit.Active      == 1
               
            join count(RecId)
                from hcmWorker
                where hcmWorker.RecId   == SmmSalesTarget.SalesPersonWorker
            join Name, count(RecId)
                from dirPartyTable
                where dirPartyTable.RecId   == hcmWorker.Person
        {
            
            tmpTable.initValue();
            tmpTable.SalesPersonWorker         = SmmSalesTarget.SalesPersonWorker;
            //tmpTable.Product                   = smmSalesUnit.CustGroup;
            tmpTable.Region                    = SmmSalesUnit.ParentId;
            tmpTable.SalesMan                  = dirPartyTable.Name;
            
            
            select sum(AmountMST)
                from SmmSalesTargetTrans
                where   (SmmSalesTargetTrans.TargetDate   >= startMthDate
                && SmmSalesTargetTrans.TargetDate   <= endMthDate)

                join  SmmSalesTarget
                where   SmmSalesTarget.RecId==SmmSalesTargetTrans.RefRecId;
              
                 
            {          
         

                tmpTable.SalesMonthlyTarget        = smmSalesTargetTrans.AmountMST;

                if (tmpTable.SalesMonthlyTarget)
                {
                    //Daily Target
                    tmpTable.SalesDailyTarget      = smmSalesTargetTrans.AmountMST/(noOfDaysCurrMth  - noOfFridayCurrMth);
   
                }
            }
            tmpTable.insert();
			}
     
  • faiz7049 Profile Picture
    faiz7049 2,430 on at
    Before the X++ code was below and working correctly. I changed it because I have to apply Date filter on smmSalesTargetTrans.TargetDate like previous month , Quarterly, Weekly, Previous Week etc.
     
     
    HcmWorker           hcmWorker;
        smmSalesUnit        smmSalesUnit;
        DirPartyTable       dirPartyTable;
        smmSalesTarget      smmSalesTarget;
        smmSalesTargetTrans smmSalesTargetTrans;

        while select SalesPersonWorker, count(RecId)
            from smmSalesTarget
            group by smmSalesTarget.SalesPersonWorker, smmSalesUnit.ParentId, dirPartyTable.Name
            join ParentId, count(RecId)
                from smmSalesUnit
                where smmSalesUnit.SalesUnitId  == smmSalesTarget.SalesUnitId
                    && smmSalesUnit.Active      == 1
            join sum(AmountMST)
                from smmSalesTargetTrans
                where smmSalesTargetTrans.RefRecId      == smmSalesTarget.RecId
                    && smmSalesTargetTrans.TargetDate   >= startPrevMthDate //Current Month Start Date
                    && smmSalesTargetTrans.TargetDate   <= endMthDate   //Current Month End Date
            join count(RecId)
                from hcmWorker
                where hcmWorker.RecId   == smmSalesTarget.SalesPersonWorker
            join Name, count(RecId)
                from dirPartyTable
                where dirPartyTable.RecId   == hcmWorker.Person
        {
            tmpTable.initValue();
            tmpTable.SalesPersonWorker         = smmSalesTarget.SalesPersonWorker;
            //tmpTable.Product                   = smmSalesUnit.CustGroup;
            tmpTable.Region                    = smmSalesUnit.ParentId;
            
            
          
                tmpTable.SalesMonthlyTarget        = smmSalesTargetTrans.AmountMST;

                if (tmpTable.SalesMonthlyTarget)
                {
                    //Daily Target
                    tmpTable.SalesDailyTarget      = smmSalesTargetTrans.AmountMST/(noOfDaysCurrMth  - noOfFridayCurrMth);
   
                }
           }
		   
		   tmpTable.Insert();
		   }
     
     
  • Martin Dráb Profile Picture
    Martin Dráb 238,795 Most Valuable Professional on at
    Please tell us what you've found when you debugged your code. For example, does it start working correctly if you comment out the inner select statement? If so, the problem may be caused by the fact that you're overwriting the value of smmSalesTarget variable. Simply declare and use a variable with a different name.
  • faiz7049 Profile Picture
    faiz7049 2,430 on at
    Hi Martin,
     
    After commenting inner select statement All Salesmans are printing on report. Below is X++ code and Screenshot of output. Output is showing total Target value same to all Salesmans since I did not make relation between SmmSalesTargetTable & SmmSalesTargetTransTable. Whenever I make relation between SmmSalesTargetTable & SmmSalesTargetTransTable either in while statement or select statement. Output will either one Salesman with Target value or All Salesman without Target value. Relation between those table is  
    smmSalesTargetTrans.RefRecId      == smmSalesTarget.RecId
    I am stuck in how to get All Salesmans with Target value. Please help me.
     
      HcmWorker           hcmWorker;
        smmSalesUnit        SmmSalesUnitTable;
        DirPartyTable       dirPartyTable;
        smmSalesTarget      SmmSalesTargetTable;
        smmSalesTargetTrans SmmSalesTargetTransTable;

        while select SalesPersonWorker, count(RecId)
            from SmmSalesTargetTable         group by SmmSalesTargetTable.SalesPersonWorker, SmmSalesUnitTable.ParentId, dirPartyTable.Name
            join ParentId, count(RecId)
                from SmmSalesUnitTable
                where SmmSalesUnitTable.SalesUnitId  == SmmSalesTargetTable.SalesUnitId
                    && SmmSalesUnitTable.Active      == 1
            


            join count(RecId)
                from hcmWorker
                where hcmWorker.RecId   == SmmSalesTargetTable.SalesPersonWorker
            join Name, count(RecId)
                from dirPartyTable
                where dirPartyTable.RecId   == hcmWorker.Person
        {
            
            tmpTable.initValue();
            tmpTable.SalesPersonWorker         = SmmSalesTargetTable.SalesPersonWorker;
            //tmpTable.Product                   = smmSalesUnit.CustGroup;
            tmpTable.Region                    = SmmSalesUnitTable.ParentId;
            tmpTable.SalesMan                  = dirPartyTable.Name;
            
            select sum(AmountMST) from SmmSalesTargetTransTable 
                where (SmmSalesTargetTransTable.TargetDate>=startMthDate   && SmmSalesTargetTransTable.TargetDate<=endMthDate);
                               
            
         
 
            if(SmmSalesTargetTransTable.AmountMST!=0)
                 
           {          
         

               tmpTable.SalesMonthlyTarget        = SmmSalesTargetTransTable.AmountMST;

            tmpTable.SalesDailyTarget      = SmmSalesTargetTransTable.AmountMST/(noOfDaysCurrMth  - noOfFridayCurrMth);
   
                
           }
     
     
     
    Thanks,
    Faiz
  • Martin Dráb Profile Picture
    Martin Dráb 238,795 Most Valuable Professional on at
    Looking at the final report won't tell you what happens in your code. Use the debugger to see what's going on in your code when it executes.
     
    And if you know you have a problem with a query, focus on the query. The other code, everything you have in the report and so on are irrelevant in that and it just your debugging more complicated. So, please show us the working query and the query with your unsuccessful changes.
     
    Also, verify that the expected data really exists - we can't do it for you, because we don't have access to your database.
     
     
  • faiz7049 Profile Picture
    faiz7049 2,430 on at
    Hi Experts,
     
    In below X++ code, I want (SmmSalesTargetTransTable.TargetDate>=startMthDate   && SmmSalesTargetTransTable.TargetDate<=endMthDate) inside while loop because I have many date range like previous month etc. I tried many times but either records are duplicating to every SalesPersonWorker. 
     
    Please help me.
     while select SalesPersonWorker, count(RecId)
            from SmmSalesTargetTable         group by SmmSalesTargetTable.SalesPersonWorker, SmmSalesUnitTable.ParentId, dirPartyTable.Name
            join ParentId, count(RecId)
                from SmmSalesUnitTable
                where SmmSalesUnitTable.SalesUnitId  == SmmSalesTargetTable.SalesUnitId
                    && SmmSalesUnitTable.Active      == 1
          join sum(AmountMST) from SmmSalesTargetTransTable
         where SmmSalesTargetTransTable.RefRecId==SmmSalesTargetTable.RecId
            &&  (SmmSalesTargetTransTable.TargetDate>=startMthDate   && SmmSalesTargetTransTable.TargetDate<=endMthDate)

            join count(RecId)
                from hcmWorker
                where hcmWorker.RecId   == SmmSalesTargetTable.SalesPersonWorker
            join Name, count(RecId)
                from dirPartyTable
            where dirPartyTable.RecId   == hcmWorker.Person
        {
            
            tmpTable.initValue();
            tmpTable.SalesPersonWorker         = SmmSalesTargetTable.SalesPersonWorker;
            tmpTable.Region                    = SmmSalesUnitTable.ParentId;
            tmpTable.SalesMan                  = dirPartyTable.Name;  
            tmpTable.SalesMonthlyTarget        = SmmSalesTargetTransTable.AmountMST;
    Thanks,
    Faiz
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 238,795 Most Valuable Professional on at
    Let me format your code to make is easier to read:
    while select SalesPersonWorker, count(RecId) from smmSalesTargetTable
    group by smmSalesTargetTable.SalesPersonWorker, smmSalesTargetTable.ParentId, dirPartyTable.Name
    join count(RecId) from smmSalesUnitTable
        where smmSalesUnitTable.SalesUnitId == smmSalesTargetTable.SalesUnitId
           && smmSalesUnitTable.Active      == 1
    join sum(AmountMST) from smmSalesTargetTransTable
        where smmSalesTargetTransTable.RefRecId == smmSalesTargetTable.RecId
           && smmSalesTargetTransTable.TargetDate >= startMthDate
           && smmSalesTargetTransTable.TargetDate <= endMthDate
    join count(RecId) from hcmWorker
        where hcmWorker.RecId   == smmSalesTargetTable.SalesPersonWorker
        join Name, count(RecId) from dirPartyTable
            where dirPartyTable.RecId == hcmWorker.Person
    Is this the working query or the broken one? Can you share the other one too, so we can compare them?
     
    Is all the code needed to reproduce the problem? For example, won't it behave the same if we drop hcmWorker and dirPartyTable data sources?
    while select SalesPersonWorker, count(RecId) from smmSalesTargetTable
    group by smmSalesTargetTable.SalesPersonWorker, smmSalesTargetTable.ParentId
    join count(RecId) from smmSalesUnitTable
        where smmSalesUnitTable.SalesUnitId == smmSalesTargetTable.SalesUnitId
           && smmSalesUnitTable.Active      == 1
    join sum(AmountMST) from smmSalesTargetTransTable
        where smmSalesTargetTransTable.RefRecId == smmSalesTargetTable.RecId
           && smmSalesTargetTransTable.TargetDate >= startMthDate
           && smmSalesTargetTransTable.TargetDate <= endMthDate

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Supply chain | Supply Chain Management, Commerce

#1
Mallesh Deshapaga Profile Picture

Mallesh Deshapaga 1,070

#2
Laurens vd Tang Profile Picture

Laurens vd Tang 238 Super User 2026 Season 1

#3
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 131 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

Quality order uses default receipt location instead of actual physical receipt location

Product updates

Dynamics 365 release plans