In my SmartConnect 21 map, the goal is to restrict my OData Query using a date restriction to restrict for any data newer than “yesterday”. In this case “yesterday” will be defined as midnight of the previous day.
If possible, the best approach would be to query at the source data level. While this could also be done in the map itself – as we’ll see in this example as well – for performance it is best to filter at the source data level.
A BC OData Query does allow for filtering on any field in the source data. In this example, we’ll be using BC Page 116 G/L Registers.
When using filters on dates, there are no “built-in” filters for things like “yesterday” – so we have to calculate that ourselves and store the results in a SmartConnect User Defined Global Variable – and then use the global variable as part of the OData filter being passed on the call.
The user Global Variable GBL_DATE was created and defaulted to an example date with the time set to midnight to test which will be what the code is going to do on the map.
The BC OData Query is created and the newly published web service selected:
To add the filter to the map, press the ellipsis (the 3 dots) for that service to open the OData filter window.
The SystemCreatedAt field is a datetime field in this web service and the field is added along with the filter ‘ge’ which is Greater than or equal to and then using our global variable.
Pressing Preview to check our filter fails unexpectedly with the error:
The DateTimeOffset text ‘2024-07-01T00:00:00’ should be in format ‘yyyy-mm-ddThh:mm:ss(‘.’s+)?(zzzzzz)?’ and each field value is with valid range. CorrelationId: xxxxxxxxxxx
Because there is a CorrelationId in the error message, we know that this error did come from BC and isn’t coming directly from SmartConnect.
While it does seem that the date was in the incorrect format, upon further investigation the problem is that the datetime is missing the trailing ‘Z’ on the datetime entered on the initial global variable.
As we can see in the error message, the datetime value of ‘2024-07-01T00:00:00’ was entered and it should be ‘2024-07-01T00:00:00Z‘ with the trailing ‘Z’.
Fixing the default value of the GBL_DATE variable to be ‘2024-07-01T00:00:00Z’ allowed the query to run successfully and only returned data greater than that value and returned the expected results.
While we do have the solution to the error, to finish the example, we need to create a map that uses this source and goes to any destination.
In the “Before Integration” tasks, we need a .NET Scripting task to calculate “yesterday” based on the current day.
'Get the Current DateTime, Add -1 days to get "yesterday" and format
'the date only setting the time to be 00:00:00. And don't forget the Z!
GBL_DATE= DateTime.Now.AddDays(-1).ToString("yyyy-MM-ddT00:00:00Z")
return true
//Get the Current DateTime, Add -1 days to get "yesterday" and format
//the date only setting the time to be 00:00:00. And don't forget the Z!
GBL_DATE= DateTime.Now.AddDays(-1).ToString("yyyy-MM-ddT00:00:00Z");
return true;
Also attached is the example map for SmartConnect 21 created for this example which also contains a Restriction that we could also use that does the same thing as the OData Filter. It is there and functional but since the OData Query Restriction works successfully, it doesn’t provide additional benefit other than being an example of how we could use a .NET scripting Restriction to also filter data from the destination if was necessary.
