Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Add range on dataSourc...
Finance | Project Operations, Human Resources, ...
Answered

Add range on dataSource of form x++

(1) ShareShare
ReportReport
Posted on by Menna Allah Ahmed 163
I created a new form with datasource (PayrollEarningCode) I need to add range to this data source to filter by active earning code or getting all earning code earning code so I added this code to execute query () but it doesn't work well .
What's wrong in this code ? 
 
  [DataSource]    class PayrollEarningCode    {        /// <summary>        ///        /// </summary>        public void executeQuery()        {            QueryBuildDataSource  queryBuildDataSource , qbds1 ;            QueryBuildRange       QueryBuildRange ;            date DateValid = today();            queryBuildDataSource = this.query().dataSourceTable(tablenum(PayrollEarningCode));            qbds1 = queryBuildDataSource.addDataSource(tableNum(PayrollEarningCodeDetail));            queryBuildDataSource.clearRanges();            qbds1.clearRanges();            if (EarningCodeStatus.selection() == EarningCodeStatusCFM::Active)            {                              qbds1.addLink(fieldnum(PayrollEarningCodeDetail,EarningCode), fieldnum(PayrollEarningCode,RecId));                qbds1.joinMode(JoinMode::InnerJoin);                qbds1.relations(true);                qbds1.clearRanges();                QueryBuildRange = qbds1.addRange(fieldNum(PayrollEarningCodeDetail, ValidTo));                QueryBuildRange.value(strFmt('(%1.%2 > %3)' ,qbds1.name(), fieldStr(PayrollEarningCodeDetail, ValidTo), DateValid));                 }            else            {                queryBuildDataSource.clearRanges();                qbds1.clearRanges();                qbds1.addLink(fieldnum(PayrollEarningCodeDetail,EarningCode), fieldnum(PayrollEarningCode,RecId));                qbds1.joinMode(JoinMode::InnerJoin);                qbds1.relations(true);                         }            super();            }    }
//////
I need when I select Active get active earning code with Valid To (never) in table (PayrollEarningCodeDetail) , when I select ALL get all earning code even if active or not
  • Menna Allah Ahmed Profile Picture
    Menna Allah Ahmed 163 on at
    Add range on dataSource of form x++
    Thanks Martin , it works by this code 
        [DataSource]
    class PayrollEarningCode
    {
        /// <summary>
        ///
        /// </summary>
        public void executeQuery()
        {
      
 
            QueryBuildRange QueryBuildRange ;
            QueryBuildDataSource qbds;
            QueryBuildDataSource codeDetailDs  = SysQuery::findOrCreateDataSource(this.query(),tableNum(PayrollEarningCodeDetail),tableNum(PayrollEarningCode));
             
                QueryBuildDataSource PayrollDs , PayrollDetailDs ;
	

            if (EarningCodeStatus.selection() == EarningCodeStatusCFM::Active)
            {
 

                this.Query().datasourceNo(1).clearRanges();
           

                codeDetailDs.clearRanges();
                codeDetailDs.enabled(true);
                codeDetailDs.joinMode(JoinMode::InnerJoin);
                codeDetailDs.relations(true);
         
                date today = DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone());
           

                this.query().validTimeStateDateRange(today, dateMax());
            }
			
            else
            {
                codeDetailDs.enabled(false);
         
               this.Query().datasourceNo(1).clearRanges();
} 
Super ();
}
    Also I removed datasource from form 
     
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 231,760 Most Valuable Professional on at
    Add range on dataSource of form x++
    The error message has a point - we didn't tell the system that it should add the new data source as a child of PayrollEarningCode data source. It can be fixed simply by providing the information as the third argument of findOrCreateDataSource():
    SysQuery::findOrCreateDataSource(
    this.query(),
    tableNum(PayrollEarningCodeDetail),
    tableNum(PayrollEarningCode));
    If you fix the code, you should delete your data source in the form.
     
    If, on the other hand, you want to have the data source in the form, then you should throw away the call of findOrCreateDataSource(), because the data source will always exist.
     
    If the data source is just for filtering, and the related record will always exist, then the best type of join in ExistsJoin.
  • Menna Allah Ahmed Profile Picture
    Menna Allah Ahmed 163 on at
    Add range on dataSource of form x++
    when I removed datasource I got this0 error (Queries with multiple top level data sources cannot be applied to Forms.) beacause datasource created everytime datasource executed
  • Martin Dráb Profile Picture
    Martin Dráb 231,760 Most Valuable Professional on at
    Add range on dataSource of form x++
    Regarding getting the ID of the record with the highest ValidFrom, you can use a computed column. See a simplified example in Join first line in AX 2012.
     
    But that's a different topic than Add range on dataSource of form x++. Please create a new thread if you want a deeper discussion on views.
  • Martin Dráb Profile Picture
    Martin Dráb 231,760 Most Valuable Professional on at
    Add range on dataSource of form x++
    I wonder how can your code works if codeDetailDs is always disabled. Didn't you actually want to enable it EarningCodeStatus is Active?
     
    Regarding the error, it's indeed caused not by the code, but other change you did ("I added datasource of (PayrollEarningCode Detail)) with inner join to PayrollEarningCode datasource"). Why did you do it? You don't need it for filtering, because the data source used for filtering is created in code by findOrCreateDataSource(). It's either wrong change that should be removed, or it's a change unrelated to the topic of this thread.
  • Menna Allah Ahmed Profile Picture
    Menna Allah Ahmed 163 on at
    Add range on dataSource of form x++
        [DataSource]
    class PayrollEarningCode
    {
        /// <summary>
        ///
        /// </summary>
        public void executeQuery()
        {

       
            QueryBuildDataSource codeDetailDs = SysQuery::findOrCreateDataSource(this.query(), tableNum(PayrollEarningCodeDetail));
            QueryBuildDataSource PayrollDs =  this.query().dataSourceNo(1);
            if (EarningCodeStatus.selection() == EarningCodeStatusCFM::Active)
            {
                codeDetailDs.enabled(false);

				codeDetailDs.relations(true);
                date today = DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone());
                this.query().validTimeStateDateRange(today, dateMax());
            }
            else
            {
              codeDetailDs.clearRanges();
                codeDetailDs.enabled(false);
                PayrollDs.clearRanges();
 
            }

            super();


    
        }

    }
    this code makes range works but when I tried to modify data in field as previous screen shot (ETA Earning code) I got this error ('Cannot update a record in table Earning code detail (PayrollEarningCode Detail) with Unit of Work. An attempt to change the primary key was made in the Unit of Work.') , How can I fix it , I added datasource of (PayrollEarningCode Detail)) with inner join to PayrollEarningCode datasource 
  • Menna Allah Ahmed Profile Picture
    Menna Allah Ahmed 163 on at
    Add range on dataSource of form x++
    Even if in case view how can I get highest recId OF TOP DATES ?
  • Menna Allah Ahmed Profile Picture
    Menna Allah Ahmed 163 on at
    Add range on dataSource of form x++
    I main in queryRange not in view , method or something to call to fetch in query last records with top dates in case all ?
  • Martin Dráb Profile Picture
    Martin Dráb 231,760 Most Valuable Professional on at
    Add range on dataSource of form x++
    Yes, there are ways. One of is described in my previous reply.
  • Menna Allah Ahmed Profile Picture
    Menna Allah Ahmed 163 on at
    Add range on dataSource of form x++
    there is any way to fetch records with top dates only ?

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,884 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,760 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans