Using SQL element when saving new record

[todd.bellamy]

How do i use a variable from the current row to fill in an SQL element field on save of a record?

I have a cost field that is tied to a piece of inventory. After the inventory piece is selected in the form I want the associated cost (its in the inventory table) to fill in on the inventory use log. The SQL is good but how do i use current row variables? What should the syntax be for "row variable" below?

SELECT fab_inventory.pcost from fab_inventory, logp
WHERE fab_inventory.id = logp.rig and logp.rig = " row variable"

 

[cheesegrits]

I need to know more about your structure and work flow.

Are you trying to do this on submission of the fab_inventory form?

What is the "inventory use log"? Are you trying to create a new row in that? or modify an existing row?

The SELECT clause you gave makes no sense, as you have

WHERE fab_inventory.id = logp.rig and logp.rig = " row variable"

Which is comparing the same field (logp.rig) twice with an AND, which doesn't make sense to me.

So can you describe your table and (relevant) field info, and the exact workflow you are trying to achieve.

-- hugh

 

[todd.bellamy]

Mr. Gritts,

Thanks for the reply. I have two tables. A table with inventory rig numbers and prices.

I have a tracking table and form for that tracking table for everytime something is used in inventory.

I want the user to enter into the form the inventory ID and have the cost of that inventory populated on the tracking table (along with some dates and other data) upon the form submission. I was hoping to use the SQL element to select and populate price from the inventory table to the tracking table upon submission. I know the autofill feature can do this but my users HATE to have to click ok to look up the value.

Typically in SQL i would accomplish the join between the inventory and tracking tables by saying something like

"select cost from inventory where inventory.rig = :rig "

Any recommendations?