How to filter SalesLine table with CreateDatetime field to fetch record for duplicate item

Hi All,

Need suggestion on how to filter creatDateTime in salesLine table by using date in a job. Below is my sample job.

static void filtersalesorderLine()
{
SalesLine salesLine;
SalesLine countRecID;
InventTable inventTable;

transdate fromDate = 22/11/2018;
TransDate toDate = 28/11/2018;

While select SalesLine
exists join inventTable
where salesLine.ItemId == inventTable.ItemId
&& salesLine.createDateTime >= DateTimeUtill::newDateTime(fromDate,000)
&& salesLine.createDateTime <= DateTimeUtill::newDateTime(todate, 000)
&& inventTable.Itemtype == ItemType::Service
{
select count(RecID) countRecID group by countRecID.ItemId
where countRecID.SaledId == salesLine.salesId;
If(countRecID.RecId >1)
{
info(strfmt("%1",salesLine.SalesId));
}
}
}

Above job is working perfect if I removed createDatetime filter or if I gave createdBy filter.
But I my requirement is to filter SalesLine table with CreateDatetime field to fetch record for duplicate item.

Please suggestion how to achieve this by using createDateTime filter.

Thanks in advance.

Hi,

please try the below,

static void filtersalesorderLine()
{
SalesLine salesLine;
SalesLine countRecID;
InventTable inventTable;
            
transdate fromDate = 22\11\2018;
TransDate toDate = 28\11\2018;

While select /*count(RecId)*/  * from SalesLine
     join inventTable
         where salesLine.ItemId == inventTable.ItemId
              //&& salesLine.createdDateTime >= DateTimeUtil::newDateTime(fromDate,86400) 
             
              //&& salesLine.createdDateTime <= DateTimeUtil::newDateTime(toDate,86400)  // DateTimeUtill::newDateTime(todate, 000)
                //&& inventTable.Itemtype == ItemType::Service
{
       select count(RecId) from countRecID group by countRecID.ItemId
       where countRecID.SalesId == salesLine.salesId;
       If(countRecID.RecId >1)
       {
         
            info(strfmt("%1,%2",salesLine.SalesId,salesLine.createdDateTime));
       }
}
}

I have done upto my understanding.If you want anything more please reply in detail of your requirements.

regards,

Bharath K

Thanks Bharath for your reply.

My requirement is to filter salesline table with createddatetime field. As I showed in my job. And in your job you committed my main filter.

Hi,

You want to filter the salesline table with created datetime field.Do you want to get salesline details a particular range of dates or how ?

Can you please tell that?

Regards,

Bharath

What’s your version of AX?
Is CreatedDateTime enabled on SalesLine?
Do you mean that the query doesn’t return anything, or is there another reason why isn’t “working perfect”? Use the debugger if you don’t know.
If the query doesn’t return anything, are you sure that you have any sales lines with services in the given company created in the given time range?

By the way, can’t you use a single query?

while select count(RecID), ItemId  salesLine
    group by ItemId
    where salesLine.createDateTime >= DateTimeUtill::newDateTime(fromDate,000)
       && salesLine.createDateTime <= DateTimeUtill::newDateTime(todate, 000)

       exists join inventTable
         where salesLine.ItemId == inventTable.ItemId
            && inventTable.Itemtype == ItemType::Service

Or even better, you could have a query with having (returning just lines with count(RecId) > 1).

Issue now resolved :).

After little modification (show below) my job is working.

DateTimeUtill::newDateTime(todate, str2time(00:00:00)

str2time(00:00:00) isn’t valid X++ code.
You meant str2time(‘00:00:00’), but it returns 0, so it couldn’t fix anything. You can easily test that your new code does the same thing as the old one:

utcDateTime oldTime = DateTimeUtil::newDateTime(today(), 0);
utcDateTime newTime = DateTimeUtil::newDateTime(today(), str2time('00:00:00'));

if (newTime == oldTime)
{
    info("It's the same thing!");
}

Thanks Martin for replying.

Actually it’s working. With my previous code system trying to filter with debugger not going inside of while loop and while I checked I found that system trying to filter salesline with very old dates 2yrs back date.

But with my new code system is filtering salesline with my given date.

So you claim that DateTimeUtill::newDateTime(todate, str2time(00:00:00) compiles and it returns something else than DateTimeUtil::newDateTime(today(), 0)? I don’t buy it; it’s syntactically wrong and even you fix the compilation error, my test above proves that it returns the same thing.

I trust you that you somehow fixed your problem, but not by this piece of code.