Thanks,

I was using the more novice way to create views by using the gadgets in the DBC container, then creating the remote view as a way to get the free table into the DBC without making it unavailable to SBT. Your code method should be better. Also, I did use gendbc to get similar code from my views.

As for multi-companies, you can easily make the approach work with multiple companies by setting up the SQL statement as a string with substitution, and then substituting other definitions as needed. Here's an example.



***************** View setup for SOTRANVIEW ***************
*** This program creates a local view from two remote views,
*-- Icitem, and either socurrentview or sohistoryview.
*-- By defining this local view, it's easy to switch between
*-- a history view and a current view
*-- Steve DeMartino
*-- 05/25/99
*-- For now, a global variable gc_sotranview_switch will serve to control
*-- the choice.
*-- Also, lc_thedatabase controls the database that contains the remote views, so that
*-- the remote views, as well as this one, can be held in
*-- a temporary private database container, allowing multiple instances
*-- of different SBT companies, while still providing the advantatges of the DBC.

local lc_selectline, lc_thedatabase
public gc_sotranview_switch

*Set the current or history view depending upon the value of the property history.

if this.history
gc_sotranview_switch = "Sohistoryview"
else
gc_sotranview_switch = "Socurrentview"
endif

lc_thedatabase = this.dbcontainer

*AS SELECT Icitemview_a.*, Socurrentview_b.sono, Socurrentview_b.ordate, Socurrentview_b.shipdate, Socurrentview_b.custno, Socurrentview_b.ponum, Socurrentview_b.ornum, Socurrentview_b.terr, Socurrentview_b.sostat, Socurrentview_b.sotype, Socurrentview_b.price, Socurrentview_b.qtyord, Socurrentview_b.qtyshp, Socurrentview_b.salesmn, Socurrentview_b.sono_line, Socurrentview_b.disc FROM commissioncalc!icitemview Icitemview_a INNER JOIN commissioncalc!socurrentview Socurrentview_b ON Icitemview_a.item = Socurrentview_b.item


lc_selectline = " AS SELECT " + ;
"Icitemview_a.*, " + (gc_sotranview_switch) + "_b.sono, " + ;
(gc_sotranview_switch) + "_b.ordate, " + ;
(gc_sotranview_switch) + "_b.shipdate, " + ;
(gc_sotranview_switch) + "_b.lckdate, " + ;
(gc_sotranview_switch) + "_b.custno, " + ;
(gc_sotranview_switch) + "_b.ponum, " + ;
(gc_sotranview_switch) + "_b.ornum, " + ;
(gc_sotranview_switch) + "_b.terr, " + ;
(gc_sotranview_switch) + "_b.sostat, " + ;
(gc_sotranview_switch) + "_b.sotype, " + ;
(gc_sotranview_switch) + "_b.price, " + ;
(gc_sotranview_switch) + "_b.extprice, " + ;
(gc_sotranview_switch) + "_b.cost, " + ;
(gc_sotranview_switch) + "_b.qtyord, " + ;
(gc_sotranview_switch) + "_b.qtyshp, " + ;
(gc_sotranview_switch) + "_b.salesmn, " + ;
(gc_sotranview_switch) + "_b.sono_line, " + ;
(gc_sotranview_switch) + "_b.disc FROM " + (lc_thedatabase) + "!icitemview Icitemview_a INNER JOIN " + (lc_thedatabase) + "!" + ;
(gc_sotranview_switch) + " " + (gc_sotranview_switch) + "_b ON Icitemview_a.item = " + ;
(gc_sotranview_switch) + "_b.item"

CREATE SQL VIEW "SOTRANVIEW" ;
&lc_selectline

DBSetProp('SOTRANVIEW', 'View', 'UpdateType', 1)
DBSetProp('SOTRANVIEW', 'View', 'WhereType', 3)
DBSetProp('SOTRANVIEW', 'View', 'FetchMemo', .T.)
DBSetProp('SOTRANVIEW', 'View', 'SendUpdates', .F.)
DBSetProp('SOTRANVIEW', 'View', 'UseMemoSize', 255)
DBSetProp('SOTRANVIEW', 'View', 'FetchSize', 100)
DBSetProp('SOTRANVIEW', 'View', 'MaxRecords', -1)
*DBSetProp('SOTRANVIEW', 'View', 'Tables', 'commissioncalc!icitemview,commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW', 'View', 'Tables', "'" + (lc_thedatabase) + "!icitemview," + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW', 'View', 'Prepared', .F.)
DBSetProp('SOTRANVIEW', 'View', 'CompareMemo', .T.)
DBSetProp('SOTRANVIEW', 'View', 'FetchAsNeeded', .F.)
DBSetProp('SOTRANVIEW', 'View', 'FetchSize', 100)
DBSetProp('SOTRANVIEW', 'View', 'Comment', "")
DBSetProp('SOTRANVIEW', 'View', 'BatchUpdateCount', 1)
DBSetProp('SOTRANVIEW', 'View', 'ShareConnection', .F.)

*!* Field Level Properties for SOTRANVIEW
* Props for the SOTRANVIEW.item field.
DBSetProp('SOTRANVIEW.item', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.item', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.item', 'Field', 'UpdateName', 'commissioncalc!icitemview.item')
DBSetProp('SOTRANVIEW.item', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!icitemview.item'")
DBSetProp('SOTRANVIEW.item', 'Field', 'DataType', "C(15)")
* Props for the SOTRANVIEW.itmdesc field.
DBSetProp('SOTRANVIEW.itmdesc', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.itmdesc', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.itmdesc', 'Field', 'UpdateName', 'commissioncalc!icitemview.itmdesc')
DBSetProp('SOTRANVIEW.itmdesc', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!icitemview.itmdesc'")
DBSetProp('SOTRANVIEW.itmdesc', 'Field', 'DataType', "C(60)")
* Props for the SOTRANVIEW.comcode field.
DBSetProp('SOTRANVIEW.comcode', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.comcode', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.comcode', 'Field', 'UpdateName', 'commissioncalc!icitemview.comcode')
DBSetProp('SOTRANVIEW.comcode', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!icitemview.comcode'")
DBSetProp('SOTRANVIEW.comcode', 'Field', 'DataType', "C(2)")
* Props for the SOTRANVIEW.sono field.
DBSetProp('SOTRANVIEW.sono', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.sono', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.sono', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.sono', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.sono', 'Field', 'DataType', "C(10)")
* Props for the SOTRANVIEW.ordate field.
DBSetProp('SOTRANVIEW.ordate', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.ordate', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.ordate', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.ordate', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.ordate', 'Field', 'DataType', "D")
* Props for the SOTRANVIEW.shipdate field.
DBSetProp('SOTRANVIEW.shipdate', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.shipdate', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.shipdate', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.shipdate', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.shipdate', 'Field', 'DataType', "D")
* Props for the SOTRANVIEW.lckdate field.
DBSetProp('SOTRANVIEW.lckdate', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.lckdate', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.lckdate', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.lckdate', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.lckdate', 'Field', 'DataType', "D")
* Props for the SOTRANVIEW.custno field.
DBSetProp('SOTRANVIEW.custno', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.custno', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.custno', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.custno', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.custno', 'Field', 'DataType', "C(6)")
* Props for the SOTRANVIEW.ponum field.
DBSetProp('SOTRANVIEW.ponum', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.ponum', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.ponum', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.ponum', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.ponum', 'Field', 'DataType', "C(20)")
* Props for the SOTRANVIEW.ornum field.
DBSetProp('SOTRANVIEW.ornum', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.ornum', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.ornum', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.ornum', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.ornum', 'Field', 'DataType', "C(10)")
* Props for the SOTRANVIEW.terr field.
DBSetProp('SOTRANVIEW.terr', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.terr', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.terr', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.terr', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.terr', 'Field', 'DataType', "C(2)")
* Props for the SOTRANVIEW.sostat field.
DBSetProp('SOTRANVIEW.sostat', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.sostat', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.sostat', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.sostat', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.sostat', 'Field', 'DataType', "C(1)")
* Props for the SOTRANVIEW.sotype field.
DBSetProp('SOTRANVIEW.sotype', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.sotype', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.sotype', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.sotype', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.sotype', 'Field', 'DataType', "C(1)")
* Props for the SOTRANVIEW.price field.
DBSetProp('SOTRANVIEW.price', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.price', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.price', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.price', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.price', 'Field', 'DataType', "N(15,5)")
* Props for the SOTRANVIEW.cost field.
DBSetProp('SOTRANVIEW.cost', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.cost', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.cost', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.cost', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.cost', 'Field', 'DataType', "N(15,5)")
* Props for the SOTRANVIEW.extprice field.
DBSetProp('SOTRANVIEW.extprice', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.extprice', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.extprice', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.extprice', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.extprice', 'Field', 'DataType', "Y")
* Props for the SOTRANVIEW.qtyord field.
DBSetProp('SOTRANVIEW.qtyord', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.qtyord', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.qtyord', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.qtyord', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.qtyord', 'Field', 'DataType', "N(15,6)")
* Props for the SOTRANVIEW.qtyshp field.
DBSetProp('SOTRANVIEW.qtyshp', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.qtyshp', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.qtyshp', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.qtyshp', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.qtyshp', 'Field', 'DataType', "N(15,6)")
* Props for the SOTRANVIEW.salesmn field.
DBSetProp('SOTRANVIEW.salesmn', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.salesmn', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.salesmn', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.salesmn', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.salesmn', 'Field', 'DataType', "C(4)")
* Props for the SOTRANVIEW.sono_line field.
DBSetProp('SOTRANVIEW.sono_line', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.sono_line', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.sono_line', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.sono_line', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.sono_line', 'Field', 'DataType', "C(14)")
* Props for the SOTRANVIEW.disc field.
DBSetProp('SOTRANVIEW.disc', 'Field', 'KeyField', .F.)
DBSetProp('SOTRANVIEW.disc', 'Field', 'Updatable', .T.)
*DBSetProp('SOTRANVIEW.disc', 'Field', 'UpdateName', 'commissioncalc!' + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.disc', 'Field', 'UpdateName', "'" + (lc_thedatabase) + "!'" + (gc_sotranview_switch))
DBSetProp('SOTRANVIEW.disc', 'Field', 'DataType', "N(7,3)")