Add range on dataSource of form x++

(1) Share

Report

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

I have the same question (0)

All responses (20)

Answers (1)

Vignesh.A 203 on at

Like (0)

Report

Hello Menna,

The table you have used "PayrollEarningCodeDetail" is a ValidTimeStateFieldType property enabled. Wherever the tables have this property enabled a normal select statement or a query statement wont work. We need to have explicit conditions to be used to filter the data in these tables.

Please check for the link which talks about a similar scenario https://community.dynamics.com/forums/thread/details/?threadid=2b061e32-d335-40dd-8be3-a22d167b7ddb

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

Like (0)

Report
First of all, look at current SQL query used by your data source, i.e. what you actually generated by your X++ code. It gives you much more information than "it doesn't work". It may allow you to solve the problem by yourself, or you'll at least have more details to share with us.

Let format and simplify your code:

[DataSource] class PayrollEarningCode { public void executeQuery() { date dateValid = today(); QueryBuildDataSource earningCodeDs = this.queryBuildDataSource(); QueryBuildDataSource codeDetailDs = earningCodeDs.addDataSource(tableNum(PayrollEarningCodeDetail)); queryBuildDataSource.clearRanges(); codeDetailDs.clearRanges(); if (EarningCodeStatus.selection() == EarningCodeStatusCFM::Active) { codeDetailDs.addLink(fieldnum(PayrollEarningCodeDetail,EarningCode), fieldnum(PayrollEarningCode,RecId)); codeDetailDs.joinMode(JoinMode::InnerJoin); codeDetailDs.relations(true); codeDetailDs.clearRanges(); QueryBuildRange validToRange = codeDetailDs.addRange(fieldNum(PayrollEarningCodeDetail, ValidTo)); validToRange.value(strFmt('(%1.%2 > %3)' ,codeDetailDs.name(), fieldStr(PayrollEarningCodeDetail, ValidTo), DateValid)); } else { codeDetailDs.addLink(fieldnum(PayrollEarningCodeDetail,EarningCode), fieldnum(PayrollEarningCode,RecId)); codeDetailDs.joinMode(JoinMode::InnerJoin); codeDetailDs.relations(true); } super(); } }

I immediately see several bugs.

Every time the query executes (e.g. a grid is sorted or filter applies), you add an extra data sources. If it executes ten times, you'll add ten data sources for PayrolEarningCodeDetails.

The correct way to apply filters to date-effective tables is using methods like validTimeStateDateRange() (on Query class). Then you won't need to fix the bug with incorrect conversion to today to string for the extended query syntax, because you won't use such a thing anymore.

You have a wrong order of fields in addLink(), but again avoid even a possibility of such a bug by using a better method: relations(true);

A good idea is building your code piece by piece and testing each step, so you won't end up with so many bugs to deal with at once.

Was this reply helpful? Yes No
Menna Allah Ahmed 163 on at

Like (0)

Report

Hello Vignesh.A ,

Can you explain please how can I use ValidTimestateRange with condition in range ?

Was this reply helpful? Yes No
Menna Allah Ahmed 163 on at

Like (0)

Report

Hello Martin Dráb ,

Can you explain please how can I avoid addDatasource ten times as your example ? I tried to add datasource in form but range doesn't work ,

Also can your explain bugs in code ? , if I use ValidTimeStateRange how can I apply condition in this range ?

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

Like (0)

Report
You have two options. Either you'll put the created data source to an instance variable and when calling executeQuery() again, you'll take the data source from there. Or you'll try to find the data source in the query and use it if it's already there.

The following example uses the latter approach:

public void executeQuery() { QueryBuildDataSource codeDetailDs = SysQuery::findOrCreateDataSource(this.query(), tableNum(PayrollEarningCodeDetail)); codeDetailDs.relations(true); if (EarningCodeStatus.selection() == EarningCodeStatusCFM::Active) { date today = DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()); this.query().validTimeStateAsOfDate(today); } else { this.query().validTimeStateDateRange(dateNull(), dateMax()); } super(); }

Was this reply helpful? Yes No
Menna Allah Ahmed 163 on at

Like (0)

Report
As I understand , this statement will get data till today only and not get data greater than today right ?
this.query().validTimeStateAsOfDate(today);

Was this reply helpful? Yes No
Menna Allah Ahmed 163 on at

Like (0)

Report

I tried your solution , it works but In case all (earning code not active) it gets all data with all date by example if earning code has date and expired and have another data and not expired it gets two lines .

How to fix it in case all ? I want to get last line only

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

Like (0)

Report

I gave you an example of code. It's up to you to use it a way that you want for your particular business scenario. If you want to use different filters, just go and do it. If you don't know how, you'll need to explain your business requirements to us.

validTimeStateAsOfDate(today) will get your records valid today. ValidTo can be at any time in future, but ValidFrom can't be.

Was this reply helpful? Yes No
Menna Allah Ahmed 163 on at

Like (0)

Report

I need if I select active get active earning code (PayrollEarningCodeDetail.ValidTo > today ) if I select All (Get all earning code active and not active) but last line only not all lines

How Can I achieve this , I don't know how to get last record of validTimeState

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

Like (0)

Report

You're wrong in thinking that active records are those with PayrollEarningCodeDetail.ValidTo > today. The problem is that you ignore ValidFrom. It's possible that a record isn't valid because ValidFrom > today.

How do you define "the last"? The one with the highest ValidFrom? If so, you can't get such a thing by a simple select statement. I suggest you consider carefully whether the requirement makes sense and if so, you implement a view returning RecId of the "highest" PayrollEarningCodeDetail for the given earning code. The you can join PayrollEarningCodeDetail with the view and enable and disable this data source as needed.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Add range on dataSource of form x++

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Add range on dataSource of form x++

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates