Hi need some help,
I modify the budget register entry form, I add new fields in budget account entry details (Item number, Item name, Unit, Price) and new columns in BudgetTransactionLine table (ItemNumber,ItemaName,Unit,Price) also i have custom SQL query.
Now i want to use my SQL query to be a lookup in the item number field in the form. Once the user select an item number the item name, unit and price will automatically posted the form and also will be save the fields value to the new added columns in BudgetTransactionLine.
Here is my custom SQL query:
SELECT DISTINCT UPPER(IT.ITEMID) [ITEMID]
,UPPER(ET.NAME) [NAME]
,INVTM.PRICE
,UPPER(IT.BOMUNITID) [UNIT]
,UPPER(INVTD.ITEMGROUPID) [ITEMGROUPID]
,UPPER(ISNULL(CASE SUBSTRING(LTRIM(RTRIM(REPLACE(IIG.NAME, ‘FG’, ‘’))), 0, 7)
WHEN ‘Canned’
THEN SUBSTRING(LTRIM(RTRIM(REPLACE(IIG.NAME, ‘FG’, ‘’))), 0, 7)
ELSE LTRIM(RTRIM(REPLACE(IIG.NAME, ‘FG’, ‘’)))
END, ‘OTHERS’)) [PRODUCTTYPE]
FROM dbo.INVENTTABLE IT
LEFT OUTER JOIN dbo.ECORESPRODUCTTRANSLATION ET ON ET.PRODUCT = IT.PRODUCT
LEFT OUTER JOIN dbo.INVENTITEMGROUPITEM INVTD ON IT.ITEMID = INVTD.ITEMID
LEFT OUTER JOIN dbo.INVENTITEMGROUP IIG ON INVTD.ITEMGROUPID = IIG.ITEMGROUPID
LEFT OUTER JOIN dbo.INVENTTABLEMODULE INVTM ON IT.ITEMID = INVTM.ITEMID
WHERE INVTM.PRICE > 0 AND INVTM.MODULETYPE = 1
Lookup forms are based on AX queries, not on T-SQL code. Unfortunately your code can’t be directly rewritten to AX queries, because you’re using things that AX doesn’t support (such as DISTINCT, SUBSTRING). Therefore this code isn’t useful for what you want; you must design the query again; this time taking AX into account.
But before writing any query, thing about your data model. For example, the need to parse NAME suggests that you violated already the first normal forms - the attribute isn’t atomic. Your table will be much easier to query is it’s design correctly.
Hi Martin,
I already fix this. I create new table and insert data that I need and create a lookup method, now I can select item number in the form and save it to the table. Now all I want is auto fill the other fields (Item name, Unit, Price) after I select Item number and save it also on table. How can I do that? do you have a sample code?
Why do you want to duplicate the data? It sounds you’re now going to violate the second normal form.
Currently I used display method to show the value in the fields (Item name, Unit, Price) after I select in Item number lookup. I just want to save also the value that display in Item name, Unit, Price filed to the table just like Item number.
Image below is the current view of my form.
it sounds that you indeed have no reason to duplicate the data, therefore doing it would be a mistake. Not only it’s unnecessary and it would waste database space, but more importantly you would have to add extra code to maintain the data in sync (otherwise you would end up with inconsistent database).
The right solution is adding InventTable data source to the form and either join it or link it to your data source. Also note that you’re table will need a relation to InventTable.
