How to create SQL Query Lookup

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

Related
Recommended