top of page

Let's get the selects excited: SysDaQuery

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?

Comments


bottom of page