5
I would like to turn the below sql into a query that I can have accessible to the user in the AX menus. Does anyone know how to do this?
K
| select a.description,b.itemid, c.phantom,a.createddatetime,a.createdby,d.email | ||||
| from sysdatabaselog A | ||||
| inner join | ||||
| bomversion B | ||||
| on substring(a.description,1,11) = b.bomid | ||||
| inner join | ||||
| inventtable C | ||||
| on b.itemid = c.itemid | ||||
| left outer join | ||||
| sysuserinfo D | ||||
| on a.createdby = id | ||||
| where (table_ = 18 and logtype=1) | ||||
| and a.CREATEDDATETIME > '2022-01-22' | ||||
| and c.phantom=1 | ||||
It's crucial to decide what you want to do. For example, you may choose a form.
Then create a simple form, e.g. showing Description and CreatedBy fields from SysDataBaselog table in a grid.
When you have that, and the filter for Table field. You need to get a reference of the QueryBuildDataSource of the table, add a QueryBuildRange object and set its value. For example:
QueryBuildDataSource dbLogQbds = sysDataBaselog_ds.queryBuildDataSource(); QueryBuildRange tableIdRange = dbLogQbds.addRange(fieldNum(SysDataBaselog, Table)); tableIdRange.value(queryValue(tableNum(BomVersion)));
When you achieve that, you should have some understand about how this kind of development look like. It also gives you keywords that you can use to find more information on internet.
Regarding subString(), you can't use it in the same way in AX, therefore you'll have to look for a workaround. I would start by question the whole design of this query.
Are you saying that you don't know anything about queries in AX at all, or do you have a particular problem (such as with substring())?
Also, what do you mean by "having a query in AX menus"? Do you want to use a query in a form? Or in a RunBase class?
#1
André Arnaud de Cal...
292,863
Super User 2025 Season 1
#2
Martin Dráb
231,723
Most Valuable Professional
#3
nmaenpaa
101,156
Moderator
Share
Report
All responses (
Answers (
