By using this website, you agree to our Terms of Use (click here)
Recently we started using a connector to import orders from Amazon in our build. One thing that has been happening intermittently is that it is pulling date/time into the order date field instead of date. I'm working with our partner on figuring out why, but one of the problems I'm having is identifying the orders that it is happening TO.
I created a generic inquiry to review Orders and Order Dates. I grabbed the "CRCASE" table to use the "LastModifiedDate" schema that is present there to show the field as date/time. However, when I run it, it still shows everything as 12:00.
I know it is date/time because I have another sales report that has an incremental refresh setup with a stored procedure. It does grouping on order details, and has a MERGE/INSERT statement. The merge statement breaks when we have date/time, because all of a sudden we have multiple results for the same order on the same day. I don't want to have to update my stored procedure, I'd rather just ensure that orders always come in with order date.
I was able to identify in the past a rough idea where they were, because I'd set "Date = Y" and I'd get 100 results, but if I set, Date = X through Z, then if I look at orders with Date Y, I'd get 150.
We would also then run a CAST statement in the system to set the date/time back to date, and that would fix the report. So I'm 99.9% sure this is indeed the issue.
Any thoughts?
Thanks all!
Well, I kept digging on this problem on my own and was able to figure it out. First of all, I stupidly had forgotten that my report read orderdate from SOLine not SOOrder. However, even looking at SOLine.Orderdate with CRCase.LastmodifiedDateTime did not do the trick.
I finally was able to find the dupes by reproducing the grouping in power bi, finding the duplicate days, and then looking at the audit history. From there I realized that if you are in the Generic Query editor, and go to add a Data Field, if you are looking through the dropdown, you only have the option of OrderDate - however, if you click through to enter a formula, and look through the field list, you will find: =[SOLine.OrderDate_Hour]. Interestingly, if I look at SoLine.OrderDate with CRCase.Lastmodifieddatetime schema field, it shows 12:00, but for example, on my first find, if I look at [SOLine.OrderDate_Hour] it shows "9" correctly...
So case closed, but I'll leave this here in case anyone else should have the odd need to dig into these details!
Interesting. I never noticed [SOLine.OrderDate_Hour] before.
It's not an actual DAC field - the underscore are functions being applied to the underlying DAC field OrderDate.
