What are you trying to update in the view back to the base tables? An
edit of a col or two?
What about the computed answers your generating in your Select? Is
one of them set to update and it shouldn't be?
On Nov 12, 2007 9:09 AM, MB Software Solutions
<[EMAIL PROTECTED]> wrote:
> 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.
