I'm trying to get a GRID working for this scenario:

Here are the relevant fields I will be asking about below:

JobItem.iPartKey

Parts.PartNo
Part.ipkey

I have a relation in place between JobItem.iPartKey->Parts.ipkey.

As you can see, I have a JobItem table which stores a key (iPartKey) that points to they key of a Parts.ipkey. Now the user will not enter the key value (it is autoinc), rather, they will enter a part number which I need to lookup the corresponding "part key" for that part number in the Parts table and store the part key in JobItem.iPartKey.

So I need to let them enter a part NUMBER in the column textbox, but I need to lookup that string in the Parts.PartNo, and get the Parts.ipkey, and strore that integer value in JobItem.iPartKey.

In that column, I need to display Parts.PartNo, but the underlying value that makes it work is JobItem.iPartKey.

I think I need to use the DynamicCurrentControl property to let the grid display the Parts.PartNo value, but I cannot get all the settings right to let me enter a value in the textbox, validate it (do the look up), set the JobItem.iPartKey, but display the Part.ParNo.


Please help. What all do I need to support this structure.


Here's the textbox.valid() code from my lookup textbox class that I have as one of the controls in the grid colum. After it validates the entered Part Number, I can then read the corresponding key value by referencing this.ipkey.

Dimension laProperties(10)

Local llValidated, lcLookup

laProperties='' && Blank out the entire array (char type)
laProperties[1]=0 && int type for iPartKey
laProperties[4]=0.00 && Numeric type for price
laProperties[7]={  /  /  } && Date type for date_added


 IF !Empty(this.value)

 *-- Determine exact size of PartNo fields---
 lcPartNo=Padr(Alltrim(this.Value),Fsize('part_no', 'Parts'))

 *-- 2008-09-02: Added support for AltPPartNo lookup
  Select iPkey, part_no, altpartno, dwg_no, price, cust_num ,vendor, date_added, makebuy;
   From parts;
   Where part_no==lcPartNo;
   Into Array laProperties

   If _tally=0
     MessageBox('Part No not found.',16,'Notice:')
    Else
     llValidated=.t.
    endif

  Endif 

  this.ipkey=laProperties[1]
  this.cPart_no=laProperties[2]
  this.cAltPartNo=laProperties[3]
  this.cDwg_no=laProperties[4]
  this.nPrice=laProperties[5]
  this.cCustno=laProperties[6]
  this.cVendno=laProperties[7]
  this.dDate_added=laProperties[8]
  this.cMakeBuy=laProperties[9]
 
  this.found=Iif(llValidated or Empty(this.value), 1, 0)
  this.refresh
  return (this.found)