When querying for information from Aras Innovator, it is sometimes desired to query for information relative to today's date. For example, we may wish to find a Part that a coworker told us was created yesterday, or we may want to find which Parts are set to become ineffective a week from today. In today's blog post, we'll cover the design process for one approach to add support for exactly these kinds of relative date queries. All code mentioned in this blog post can also be found in the Dynamic Date Example community project on the Aras Labs GitHub.
OOTB Support
Before we jump into our customizations, we will briefly cover what is supported in an Out of the Box (OOTB) instance. If we look at Section 5.1.2.2 of the Configuring Internationalization guide, we will find that AML requests using the add, edit, or update actions allow us to pass in __now() as a value into date properties. When the server processes our request, it will replace __now() with the date and time that the request was made.
While this is convenient, it is limited in a few ways. It is only supported for the standard AML actions that write to the database, so it is not possible to get all items that were created today for example. Additionally, this only allows for settings dates equal to exactly the date and time the request was made. It does not for example allow us to set a date for two weeks in the future relative to today. Both of these limitations will be solved by the method we will write in this blog post.
Designing the Method
When starting out with the design process for a new feature, it's helpful to look at how other people have implemented similar features. This can both save us work from needing to design something completely from scratch and make the feature more understandable by leveraging existing user knowledge. In our case of wanting to write dynamic date formulas, we can look at how Microsoft Excel implements a similar feature. When editing a cell in Excel, the user can type in TODAY() to auto-fill the current date. The user can also add or subtract from this date in order to pick relative dates. For example, TODAY() - 1 will give yesterday's date and TODAY() + 7 will give a date that is exactly a week from today. Now that we have a general idea of what our formula will look like, there are a few more considerations to make about how our Method will actually work.
Where to Run the Method
One of the first considerations we should make is where this Method is intended to be run as this decision will drive some of the actual code that we will write. One way we could do it would be to add an onBeforeGet server event to our commonly used ItemTypes like Part, Document, and CAD. While this approach is convenient for our users since they can just make get requests as usual, it does require us to log into the database and configure a server event if we ever wanted to support additional ItemTypes.
An alternative would be to simply use a custom action as described in the last section of our Advanced AML Topics blog post. This approach would not require any additional configuration beyond actually creating the Method, and we could write our Method in such a way to look up what type the user wants to query on. However, it's important to keep in mind that even small changes can frustrate users. If our users are accustomed to performing get requests, they might become confused or frustrated when asked to use a brand new action even if we make it work very similarly.
As there's pros and cons to both approaches, let's see if we can write our Method in such a way to support its use as both a server event and a custom action.
In the sample code above, we have some logic that determines whether our Method is being called from a server event or as a custom action, and it returns an appropriate item for each approach. The benefit to writing our code like this is that we only have to modify a single Method if we ever want to add or change functionality in the future.
What Properties to Check
Next, we should consider how our method will actually know which properties to parse. Two main options come to mind, and let's consider the more robust approach first.
Since our goal is to support multiple different ItemTypes, it makes sense to query the database to look up all of the date properties on the given ItemType.
This is the most flexible approach, but it does require an additional call to the database. If performance is a concern, we can consider taking a more streamlined approach where we simply hard-code in the names of the date properties we know are likely to be used.
List<string> datePropsNames = new List<string> { "created_on", "modified_on", "effective_date" };
How to Do the Calculation
Lastly, we need to write the code to actually perform our calculation. When we're parsing our date properties for a formula, we want to try to be as lenient as possible in terms of what the user can enter. Some users may naturally enter TODAY() + 1 to get tomorrow's date while others may type in 1 + TODAY(). Making sure our formula can accept multiple different formats will make the learning process and user experience more pleasant which will help with the actual adoption of this feature by our users. With the knowledge that we want to accept as much as user variation as possible, we can start considering how we will actually support that.
Parsing the property values for this kind of formula initially seems to present quite a difficult challenge. While we could write some very verbose conditional logic to parse the user's input for select strings, there is a much easier way. Instead, we can simply use a carefully formatted regular expression. This regular expression will not only help us determine if this date property even contains a formula, but it is also set up to capture the specific parts of the formula we need to run our calculation. To make the code more readable and reusable, we can contain this calculation logic inside of a few helper functions that we'll include in our Method.
Putting It All Together
Now that we have a list of date properties to check and a helper function to evaluate our relative date formula, all we need to do is loop through our date properties and run the helper function on each date that we find. The full Method code as well as instructions to install and use this in your environment can be found in the Dynamic Date Example community project.
Conclusion
If you've been following along with our Best Practices blog post, you can see the application of some of those concepts within this project. By avoiding hard-coding any specific ItemType or property names, we can contain the code for a multitude of different use cases within a single Method. If our users ever request additional functionality, we can also easily accomplish this because all of our Method code is stored in a single location. The design process is iterative and ever ongoing. Spend some time thinking about possible enhancements that could be made to this feature. If you feel inspired, we encourage you to implement those enhancements and make a pull request to share them with other members of our community.