And here we do have the first real blog entry !!
Today's post will deal with some peculiar objects that exist in D365FO, that the standard uses sometimes and that can get us out of trouble.
All of us (programmers have surely used it, and consultants have surely seen some of them) have used "selects" or "while selects" to query records from a table and perform actions on them. What's more, we have even used the famous "queryruns" many times to do practically the same thing but in a cleaner way.
But there are times when, depending on which "select", it cannot be transformed to "queryrun" or the developer simply does not have the mindset to detect easily how to make a specific query. In the end, we are all people and each of us has the ability to see or perform tasks in a certain way.
That is why there is another way to make queries, a different way but which is also very visual and clear: using SysDaQuery.
This collection of objects does not do any kind of black magic or perform complex actions by themselves, they simply provide information on how to write sentences through code.
Query statement
Let's see an example of the standard where this type of object is used:
This code snippet is used by the warehouse management module when releasing a load (or part of it). Later I will make an entry focused exclusively on warehouse processes and which elements are involved in them.
When a load is released to the warehouse, the order quantity that is part of the works created in the release changes from the "On Order" or "Reserved Order" status to the "Physical Reserved" status. This step is the one performed in the previous fragment. As you can see, in the first place, it declares the two main tables that will carry out the query (in yellow), one of them on the entire table and the other on a selection of it that enters the method as a parameter. Secondly, the most interesting parts of this type of sentence follow one another; all those applicable conditions are then used as "where", "and" or "or" in the same way that we would write them in an SQL editor. (in red) Finally, as the two tables must be joined by a join, it makes that union in another different sentence (in blue).
With this we already have the sentence built and in a rather curious way and that we can have in a separate method. After that, we can iterate over it and perform the actions we want.
Action statements
But this is not everything that this system can offer. We can not only do query statements but also insert, update and delete records.
Let's look at examples of these types:
As you can see in these examples, other different objects that have the characteristics and conditions that the insertion, update and delete sentences have tu fulfill, are used.
Object Library
In order to build up these sentences, there are a multitude of objects to use. Either to group, to define the selection, to deal with date issues (Valid Time State), etc ...
I will share with you the list of all the objects of this utility (those mentioned above are in bold). You will see that the possibilities are immense:
SysDaAggregateProjectionField
SysDaAndExpression
SysDaAvgOfField
SysDaBinaryExpression
SysDaCountOfField
SysDaCrossCompany
SysDaCrossCompanyAll
SysDaCrossCompanyContainer
SysDaDataAccessStatement
SysDaDeleteObject
SysDaDeleteStatement
SysDaDivideExpression
SysDaEqualsExpression
SysDaFieldExpression
SysDaFindObject
SysDaFindStatement
SysDaGreaterThanExpression
SysDaGreaterThanOrEqualsExpression
SysDaGroupBys
SysDaInsertObject
SysDaInsertStatement
SysDaIntDivExpression
SysDaLessThanExpression
SysDaLessThanOrEqualsExpression
SysDaLikeExpression
SysDaMaxOfField
SysDaMinOfField
SysDaMinusExpression
SysDaModExpression
SysDaMultiplyExpression
SysDaNotEqualsExpression
SysDaOrderBys
SysDaOrExpression
SysDaPlusExpression
SysDaProjectionField
SysDaQueryExpression
SysDaQueryObject
SysDaQueryObjectBuilder
SysDaSearchObject
SysDaSearchStatement
SysDaSelection
SysDaSettingsList
SysDaSumOfField
SysDaUpdateObject
SysDaUpdateStatement
SysDaValidTimeState
SysDaValidTimeStateAsOfDate
SysDaValidTimeStateAsOfDateTime
SysDaValidTimeStateDateRange
SysDaValidTimeStateDateTimeRange
SysDaValueExpression
SysDaValueField
Having seen all these options, one wants to try and get acquainted with these new friends. And also find out what happens when queries are really complex.
Will you dare?