I don't see anything obvious Michael. Just a couple of things though, you have several iId fields in different tables. None of these are related to the updatable column in the Materials table. Normally I see some weirdness in updates when the view Tables property is messed up, and not set properly based on the columns getting updated, or set to update.
The error you are getting is related to an object property. Any chance in scope of working with the view update that there is a memvar with the same name as the view (or one of the aliases you are using in the SQL) and it is trying to find the name of the property iId instead of the view column? Rick White Light Computing, Inc. www.whitelightcomputing.com www.swfox.net www.rickschummer.com -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MB Software Solutions Sent: Monday, November 12, 2007 10:10 AM To: [EMAIL PROTECTED] Subject: Re: VFP9 problem with updating local view Eurico Chagas Filho wrote: > Hi > > Check the select statement and the base table structure. > Check the updatable fields. Than eliminate the iid field and > check if the view works. > > But I have to have the IID field in their so that the view knows which column is the key column to update. I did run the GENDBC to look at the definition and it did appear to be correct: FUNCTION MakeView_VMATERIALS ***************** View setup for VMATERIALS *************** CREATE SQL VIEW "VMATERIALS" ; AS SELECT m1.iid, m1.njobid, m1.nitemid, m1.nmatlid, m1.cRecType, m1.cNotes, m1.nqty, m1.nfactor, m1.nunitcost, m1.nTotalQty, m1.nTotalCost, m1.loverridetotalqty, m1.loverridetotalcost, left(m1.cNotes, 40) as cNotes40, it1.nScopeID, lum1.cdescription, lum1.cqtytype, lum1.cfactortype, lum1.ctotalqtytype, lum1.cunitcosttype, lum1.cType, lum1.cClass, lum1.lBuyout, lum1.cSKU1, lum1.nDefaultLength, NVL(c1.nOverheadCost, 00000.00) as nOverheadCost, NVL(c1.nSalesTaxCost, 00000.00) as nSalesTaxCost, NVL(c1.nRealizationCost, 0000000000.00) as nRealizationCost, NVL(c1.nMarkupCost, 0000000000.00) as nMarkupCost, NVL(c1.nCustomerCost, 0000000000.00) as nCustomerCost FROM eiwdb!Materials m1 LEFT JOIN eiwdb!costs c1 ON m1.iid = c1.iKey AND c1.cRecType = "M" AND c1.njobid = ?viJobID JOIN eiwdb!luMaterials lum1 ON m1.nmatlid = lum1.iid JOIN eiwdb!items it1 ON it1.iid = m1.nItemID WHERE m1.nJobID = ?viJobID DBSetProp('VMATERIALS', 'View', 'UpdateType', 1) DBSetProp('VMATERIALS', 'View', 'WhereType', 1) DBSetProp('VMATERIALS', 'View', 'FetchMemo', .T.) DBSetProp('VMATERIALS', 'View', 'SendUpdates', .T.) DBSetProp('VMATERIALS', 'View', 'UseMemoSize', 255) DBSetProp('VMATERIALS', 'View', 'FetchSize', 100) DBSetProp('VMATERIALS', 'View', 'MaxRecords', -1) DBSetProp('VMATERIALS', 'View', 'Tables', 'eiwdb!materials') DBSetProp('VMATERIALS', 'View', 'Prepared', .T.) DBSetProp('VMATERIALS', 'View', 'CompareMemo', .T.) DBSetProp('VMATERIALS', 'View', 'FetchAsNeeded', .F.) DBSetProp('VMATERIALS', 'View', 'ParameterList', "viJobID,'I'") DBSetProp('VMATERIALS', 'View', 'Comment', "mjb 03-06-07 removed cSKU2" + CHR(13) + "mjb 03-29-07 made nFactor n(10,4) instead of n(10,3)" + CHR(13) + "mjb 08-02-07 added n?Cost fields (to be computed after requery)") DBSetProp('VMATERIALS', 'View', 'BatchUpdateCount', 1) DBSetProp('VMATERIALS', 'View', 'ShareConnection', .T.) DBSetProp('VMATERIALS', 'View', 'AllowSimultaneousFetch', .F.) *!* Field Level Properties for VMATERIALS * Props for the VMATERIALS.iid field. DBSetProp('VMATERIALS.iid', 'Field', 'KeyField', .T.) DBSetProp('VMATERIALS.iid', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.iid', 'Field', 'UpdateName', 'eiwdb!materials.iid') DBSetProp('VMATERIALS.iid', 'Field', 'Caption', "Primary Key") DBSetProp('VMATERIALS.iid', 'Field', 'DataType', "I") * Props for the VMATERIALS.njobid field. DBSetProp('VMATERIALS.njobid', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.njobid', 'Field', 'Updatable', .T.) DBSetProp('VMATERIALS.njobid', 'Field', 'UpdateName', 'eiwdb!materials.njobid') DBSetProp('VMATERIALS.njobid', 'Field', 'Caption', "Job ID") DBSetProp('VMATERIALS.njobid', 'Field', 'DataType', "I") * Props for the VMATERIALS.nitemid field. DBSetProp('VMATERIALS.nitemid', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.nitemid', 'Field', 'Updatable', .T.) DBSetProp('VMATERIALS.nitemid', 'Field', 'UpdateName', 'eiwdb!materials.nitemid') DBSetProp('VMATERIALS.nitemid', 'Field', 'Caption', "Item ID (relation to Items record)") DBSetProp('VMATERIALS.nitemid', 'Field', 'DataType', "I") * Props for the VMATERIALS.nmatlid field. DBSetProp('VMATERIALS.nmatlid', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.nmatlid', 'Field', 'Updatable', .T.) DBSetProp('VMATERIALS.nmatlid', 'Field', 'UpdateName', 'eiwdb!materials.nmatlid') DBSetProp('VMATERIALS.nmatlid', 'Field', 'Caption', "Material ID") DBSetProp('VMATERIALS.nmatlid', 'Field', 'DataType', "I") * Props for the VMATERIALS.crectype field. DBSetProp('VMATERIALS.crectype', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.crectype', 'Field', 'Updatable', .T.) DBSetProp('VMATERIALS.crectype', 'Field', 'UpdateName', 'eiwdb!materials.crectype') DBSetProp('VMATERIALS.crectype', 'Field', 'Caption', "Record Type (O=Original, C=Change, S=Service, R=Re") DBSetProp('VMATERIALS.crectype', 'Field', 'DataType', "C(1)") DBSetProp('VMATERIALS.crectype', 'Field', 'DefaultValue', "'O'") * Props for the VMATERIALS.cnotes field. DBSetProp('VMATERIALS.cnotes', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.cnotes', 'Field', 'Updatable', .T.) DBSetProp('VMATERIALS.cnotes', 'Field', 'UpdateName', 'eiwdb!materials.cnotes') DBSetProp('VMATERIALS.cnotes', 'Field', 'Caption', "Notes for this line item") DBSetProp('VMATERIALS.cnotes', 'Field', 'DataType', "M") * Props for the VMATERIALS.nqty field. DBSetProp('VMATERIALS.nqty', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.nqty', 'Field', 'Updatable', .T.) DBSetProp('VMATERIALS.nqty', 'Field', 'UpdateName', 'eiwdb!materials.nqty') DBSetProp('VMATERIALS.nqty', 'Field', 'Caption', "Qty Needed") DBSetProp('VMATERIALS.nqty', 'Field', 'DataType', "N(10,3)") * Props for the VMATERIALS.nfactor field. DBSetProp('VMATERIALS.nfactor', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.nfactor', 'Field', 'Updatable', .T.) DBSetProp('VMATERIALS.nfactor', 'Field', 'UpdateName', 'eiwdb!materials.nfactor') DBSetProp('VMATERIALS.nfactor', 'Field', 'Caption', "Material Factor") DBSetProp('VMATERIALS.nfactor', 'Field', 'DataType', "N(10,4)") * Props for the VMATERIALS.nunitcost field. DBSetProp('VMATERIALS.nunitcost', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.nunitcost', 'Field', 'Updatable', .T.) DBSetProp('VMATERIALS.nunitcost', 'Field', 'UpdateName', 'eiwdb!materials.nunitcost') DBSetProp('VMATERIALS.nunitcost', 'Field', 'Caption', "Unit Cost") DBSetProp('VMATERIALS.nunitcost', 'Field', 'DataType', "N(11,4)") * Props for the VMATERIALS.ntotalqty field. DBSetProp('VMATERIALS.ntotalqty', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.ntotalqty', 'Field', 'Updatable', .T.) DBSetProp('VMATERIALS.ntotalqty', 'Field', 'UpdateName', 'eiwdb!materials.ntotalqty') DBSetProp('VMATERIALS.ntotalqty', 'Field', 'Caption', "Total Quantity") DBSetProp('VMATERIALS.ntotalqty', 'Field', 'DataType', "N(13,3)") * Props for the VMATERIALS.ntotalcost field. DBSetProp('VMATERIALS.ntotalcost', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.ntotalcost', 'Field', 'Updatable', .T.) DBSetProp('VMATERIALS.ntotalcost', 'Field', 'UpdateName', 'eiwdb!materials.ntotalcost') DBSetProp('VMATERIALS.ntotalcost', 'Field', 'Caption', "Total Cost") DBSetProp('VMATERIALS.ntotalcost', 'Field', 'DataType', "N(13,2)") * Props for the VMATERIALS.loverridetotalqty field. DBSetProp('VMATERIALS.loverridetotalqty', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.loverridetotalqty', 'Field', 'Updatable', .T.) DBSetProp('VMATERIALS.loverridetotalqty', 'Field', 'UpdateName', 'eiwdb!materials.loverridetotalqty') DBSetProp('VMATERIALS.loverridetotalqty', 'Field', 'Caption', "Override Total Qty Calculation?") DBSetProp('VMATERIALS.loverridetotalqty', 'Field', 'DataType', "L") * Props for the VMATERIALS.loverridetotalcost field. DBSetProp('VMATERIALS.loverridetotalcost', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.loverridetotalcost', 'Field', 'Updatable', .T.) DBSetProp('VMATERIALS.loverridetotalcost', 'Field', 'UpdateName', 'eiwdb!materials.loverridetotalcost') DBSetProp('VMATERIALS.loverridetotalcost', 'Field', 'Caption', "Override Total Cost Calculation?") DBSetProp('VMATERIALS.loverridetotalcost', 'Field', 'DataType', "L") * Props for the VMATERIALS.cnotes40 field. DBSetProp('VMATERIALS.cnotes40', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.cnotes40', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.cnotes40', 'Field', 'UpdateName', '') DBSetProp('VMATERIALS.cnotes40', 'Field', 'Caption', "Notes (first 40 chars)") DBSetProp('VMATERIALS.cnotes40', 'Field', 'DataType', "C(40)") * Props for the VMATERIALS.nscopeid field. DBSetProp('VMATERIALS.nscopeid', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.nscopeid', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.nscopeid', 'Field', 'UpdateName', 'eiwdb!items.nscopeid') DBSetProp('VMATERIALS.nscopeid', 'Field', 'Caption', "Backlink to Scopes table (if part of a scope item)") DBSetProp('VMATERIALS.nscopeid', 'Field', 'DataType', "I") * Props for the VMATERIALS.cdescription field. DBSetProp('VMATERIALS.cdescription', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.cdescription', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.cdescription', 'Field', 'UpdateName', 'eiwdb!lumaterials.cdescription') DBSetProp('VMATERIALS.cdescription', 'Field', 'Caption', "Material Description") DBSetProp('VMATERIALS.cdescription', 'Field', 'DataType', "C(50)") * Props for the VMATERIALS.cqtytype field. DBSetProp('VMATERIALS.cqtytype', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.cqtytype', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.cqtytype', 'Field', 'UpdateName', 'eiwdb!lumaterials.cqtytype') DBSetProp('VMATERIALS.cqtytype', 'Field', 'Caption', "Qty Type") DBSetProp('VMATERIALS.cqtytype', 'Field', 'DataType', "C(10)") DBSetProp('VMATERIALS.cqtytype', 'Field', 'DefaultValue', "'LF'") * Props for the VMATERIALS.cfactortype field. DBSetProp('VMATERIALS.cfactortype', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.cfactortype', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.cfactortype', 'Field', 'UpdateName', 'eiwdb!lumaterials.cfactortype') DBSetProp('VMATERIALS.cfactortype', 'Field', 'Caption', "Factor Type") DBSetProp('VMATERIALS.cfactortype', 'Field', 'DataType', "C(10)") DBSetProp('VMATERIALS.cfactortype', 'Field', 'DefaultValue', "'LBS/LF'") * Props for the VMATERIALS.ctotalqtytype field. DBSetProp('VMATERIALS.ctotalqtytype', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.ctotalqtytype', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.ctotalqtytype', 'Field', 'UpdateName', 'eiwdb!lumaterials.ctotalqtytype') DBSetProp('VMATERIALS.ctotalqtytype', 'Field', 'Caption', "Total Qty Type") DBSetProp('VMATERIALS.ctotalqtytype', 'Field', 'DataType', "C(10)") DBSetProp('VMATERIALS.ctotalqtytype', 'Field', 'DefaultValue', "'LBS'") * Props for the VMATERIALS.cunitcosttype field. DBSetProp('VMATERIALS.cunitcosttype', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.cunitcosttype', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.cunitcosttype', 'Field', 'UpdateName', 'eiwdb!lumaterials.cunitcosttype') DBSetProp('VMATERIALS.cunitcosttype', 'Field', 'Caption', "Unit Cost Type") DBSetProp('VMATERIALS.cunitcosttype', 'Field', 'DataType', "C(10)") DBSetProp('VMATERIALS.cunitcosttype', 'Field', 'DefaultValue', "'$/LB'") * Props for the VMATERIALS.ctype field. DBSetProp('VMATERIALS.ctype', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.ctype', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.ctype', 'Field', 'UpdateName', 'eiwdb!lumaterials.ctype') DBSetProp('VMATERIALS.ctype', 'Field', 'Caption', "Material Type") DBSetProp('VMATERIALS.ctype', 'Field', 'DataType', "C(20)") * Props for the VMATERIALS.cclass field. DBSetProp('VMATERIALS.cclass', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.cclass', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.cclass', 'Field', 'UpdateName', 'eiwdb!lumaterials.cclass') DBSetProp('VMATERIALS.cclass', 'Field', 'Caption', "Material Class") DBSetProp('VMATERIALS.cclass', 'Field', 'DataType', "C(20)") * Props for the VMATERIALS.lbuyout field. DBSetProp('VMATERIALS.lbuyout', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.lbuyout', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.lbuyout', 'Field', 'UpdateName', 'eiwdb!luMaterials.lbuyout') DBSetProp('VMATERIALS.lbuyout', 'Field', 'Caption', "Buyout material?") DBSetProp('VMATERIALS.lbuyout', 'Field', 'DataType', "L") * Props for the VMATERIALS.csku1 field. DBSetProp('VMATERIALS.csku1', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.csku1', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.csku1', 'Field', 'UpdateName', 'eiwdb!lumaterials.csku1') DBSetProp('VMATERIALS.csku1', 'Field', 'Caption', "Internal SKU#") DBSetProp('VMATERIALS.csku1', 'Field', 'DataType', "C(16)") * Props for the VMATERIALS.ndefaultlength field. DBSetProp('VMATERIALS.ndefaultlength', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.ndefaultlength', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.ndefaultlength', 'Field', 'UpdateName', 'eiwdb!lumaterials.ndefaultlength') DBSetProp('VMATERIALS.ndefaultlength', 'Field', 'Caption', "Default Length of Material (for Bill of Materials") DBSetProp('VMATERIALS.ndefaultlength', 'Field', 'DataType', "I") * Props for the VMATERIALS.noverheadcost field. DBSetProp('VMATERIALS.noverheadcost', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.noverheadcost', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.noverheadcost', 'Field', 'UpdateName', '') DBSetProp('VMATERIALS.noverheadcost', 'Field', 'DataType', "N(8,2)") * Props for the VMATERIALS.nsalestaxcost field. DBSetProp('VMATERIALS.nsalestaxcost', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.nsalestaxcost', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.nsalestaxcost', 'Field', 'UpdateName', '00') DBSetProp('VMATERIALS.nsalestaxcost', 'Field', 'Caption', "Sales tax cost") DBSetProp('VMATERIALS.nsalestaxcost', 'Field', 'DataType', "N(13,2)") * Props for the VMATERIALS.nrealizationcost field. DBSetProp('VMATERIALS.nrealizationcost', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.nrealizationcost', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.nrealizationcost', 'Field', 'UpdateName', '00') DBSetProp('VMATERIALS.nrealizationcost', 'Field', 'Caption', "Realization cost") DBSetProp('VMATERIALS.nrealizationcost', 'Field', 'DataType', "N(13,2)") * Props for the VMATERIALS.nmarkupcost field. DBSetProp('VMATERIALS.nmarkupcost', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.nmarkupcost', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.nmarkupcost', 'Field', 'UpdateName', '00') DBSetProp('VMATERIALS.nmarkupcost', 'Field', 'Caption', "Markup cost") DBSetProp('VMATERIALS.nmarkupcost', 'Field', 'DataType', "N(13,2)") * Props for the VMATERIALS.ncustomercost field. DBSetProp('VMATERIALS.ncustomercost', 'Field', 'KeyField', .F.) DBSetProp('VMATERIALS.ncustomercost', 'Field', 'Updatable', .F.) DBSetProp('VMATERIALS.ncustomercost', 'Field', 'UpdateName', '00') DBSetProp('VMATERIALS.ncustomercost', 'Field', 'Caption', "Customer cost") DBSetProp('VMATERIALS.ncustomercost', 'Field', 'DataType', "N(13,2)") ENDFUNC btw--I use Rick Schummer's ViewEditor to maintain these views, so don't think I manually entered *all* of this information (like the captions)....I'm not that crazy! http://www.whitelightcomputing.com/prodvieweditorpro.htm -- Michael J. Babcock, MCP MB Software Solutions, LLC http://mbsoftwaresolutions.com http://fabmate.com "Work smarter, not harder, with MBSS custom software solutions!" [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
