Set interactive grid value based on dropdown select list

Question

I am trying to make a cost value populate in an interactive grid column based on the value selected in an Item column.

My setup is:

An "Item" table which contains a list of Items, and the "Cost" assigned to the item.

A "Quote" table which contains general info about the quote, i.e. company name, date created, signed, signed date etc.

A "Quote_Items" table which assigns an Item to a quote by Quote_ID, and Item_ID. I added a Cost column to the Quote Items table to be able to see it in the interactive grid, and update it. For the Quote Items I created an Interactive grid to add Items to the quote.

I set the Item_ID column in my interactive grid to a popup LOV which populates the Item_ID based on the selected item.

To populate the "Quote_Item.Cost" I added a dynamic action to the Item_ID column to:

Event:Change selection Type: Column(s) Region: ..Item List Column(s): Item_ID

Action: Set Value Set Type: SQL Statement SQL Statement: SELECT I.COST FROM WIP_ITEMS I WHERE I.ITEM_ID = ITEM_ID Items to Submit: COST

The result is, when I select an Item from the dropdown, all values in the "Items.Cost" table are displayed in comma separated values in the field: Quote Item populating all item costs in one field

It seems like the "WHERE I.ITEM_ID = ITEM_ID" part of the SQL query is not working correctly? How can I get it to show only the Value associated with the Item_ID?

Answer 1

Syntax to reference a column in the SQL query > :COLUMN_NAME

So your SQL query should looks like this:

SELECT i.cost
  FROM wip_items i
 WHERE i.item_id = :ITEM_ID 

And you need to modify the "Items to Submit" parameter too, because it must always be the value which your reference in the where condition, so in your case it should be:

Items to Submit: ITEM_ID