"I tell ya, Slim ... t'aint normal for dat to happen"

Never underestimate the power of a denormalized view. What is that you ask? Consider the following three tables (partial structures; don't email me about missing fields):

SalesHeader (headerPK C(10))
SalesDetail (headerfk C(10), lineno N(3,0), widgetfk C(10))
Widgets (widgetPK C(10), widgetdesc C(40), widgetcost N(7,2))

If you want to display a sales order with widget detail in, let's say, a grid, you're gonna want a view that looks like:

CREATE SQL VIEW lvSDetail AS ;
SELECT salesdetail.*,widgets.widgetdesc,widgets.widgetcost FROM salesdetail, widgets WHERE salesdetail.headerfk = ?salesheader.headerpk AND salesdetail.widgetfk = widgets.widgetpk

So, all is well and good and you put the view into the DE of a form and show it in a Grid. You get especially slick and use a Combobox to allow the user to change which widget it is for a given line.

What happens on the change? Nada to widgetdesc and widgetcost. These values are populated when the view is opened or refreshed. Changing the widget doesn't automatically update those values.

What to do? Well, for one, these values are for display purposes only. So, you only need them to be right until you issue a TABLEUPDATE, at which time the view resets and all is well.

So...in the InterActive change of the combobox, REPLACE widgetdesc and widgetcost with the equivalent values in widgets and refresh the Grid. The record pointer of widgets will be pointing at the right record so the replace will work fine. No mess, no fuss.

Just make sure you don't make these two fields updateable through the field Updateable property. Oy veh, what a mess if you do :-)