CREATE SQL VIEW "DISPATCH" AS ; SELECT VAL(Customer.Code) AS ValCode, InvHead.*, OrdHead.Location,OrdHead.Entered, OrdHead.PriorityOrder, ; OrdHead.CustomerPO, OrdHead.DateRequired, OrdHead.Comm1, OrdHead.Comm2, OrdHead.Comm3, OrdHead.Terms, ; Customer.NAME, Customer.Code, Customer.CreditLmt,Customer.EINNumber, Customer.SpecialDisc, ; Customer.DollarsUse, OrdHead.CustomerID, Customer.Country AS CustCountry, ; (SELECT SUM(OrdSize.Ordered) FROM OrdSize WHERE OrdSize.OrderID=OrdHead.OrderID) AS TotOrdered, ; (SELECT SUM(OrdSize.Allocated) FROM OrdSize WHERE OrdSize.OrderID=OrdHead.OrderID) AS TotAllocated, ; (SELECT SUM(OrdSize.Shipped) FROM OrdSize WHERE OrdSize.OrderID=OrdHead.OrderID) AS TotShipped, ; .F. AS ok, RdyToShip.RdyToShip, ; (SELECT SUM(InvProd.ActualQty) FROM InvProd INNER JOIN InvHead ON InvProd.InvoiceNo=InvHead.InvoiceNo ; WHERE InvHead.OrderID=OrdHead.OrderID) AS Shipped, OrdHead.sbo as SpecialOrd, ; ALLTRIM(STR(OrdHead.OrderID))+IIF(OrdHead.sbo,"B"," ") AS DispOrdNo, Customer.OnHold ; FROM frantisi!InvHead ; INNER JOIN frantisi!OrdHead ON InvHead.OrderID = OrdHead.OrderID ; INNER JOIN frantisi!rdytoship ON Invhead.invoiceno = Rdytoship.invoiceno ; INNER JOIN frantisi!Customer ON Customer.customerid = OrdHead.customerid ; WHERE ((OrdHead.daterequired <= (?ldDateReqd) OR InvHead.picked <> ({})) ; AND InvHead.dispatchdate = ({})) AND OrdHead.location = (?lnLocation) ; ORDER BY 1, OrdHead.priorityorder DESC, InvHead.invoiceno DBSetProp('DISPATCH', 'View', 'UpdateType', 1) DBSetProp('DISPATCH', 'View', 'WhereType', 1) DBSetProp('DISPATCH', 'View', 'FetchMemo', .T.) DBSetProp('DISPATCH', 'View', 'SendUpdates', .T.) DBSetProp('DISPATCH', 'View', 'UseMemoSize', 255) DBSetProp('DISPATCH', 'View', 'FetchSize', 100) DBSetProp('DISPATCH', 'View', 'MaxRecords', -1) DBSetProp('DISPATCH', 'View', 'Tables', 'frantisi!Invhead') DBSetProp('DISPATCH', 'View', 'Prepared', .F.) DBSetProp('DISPATCH', 'View', 'CompareMemo', .T.) DBSetProp('DISPATCH', 'View', 'FetchAsNeeded', .F.) DBSetProp('DISPATCH', 'View', 'FetchSize', 100) DBSetProp('DISPATCH', 'View', 'Comment', "") DBSetProp('DISPATCH', 'View', 'BatchUpdateCount', 1) DBSetProp('DISPATCH', 'View', 'ShareConnection', .F.) *!* Field Level Properties for DISPATCH * Props for the DISPATCH.valcode field. DBSetProp('DISPATCH.valcode', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.valcode', 'Field', 'Updatable', .F.) DBSetProp('DISPATCH.valcode', 'Field', 'UpdateName', 'valcode') DBSetProp('DISPATCH.valcode', 'Field', 'DataType', "N(9,2)") * Props for the DISPATCH.orderid field. DBSetProp('DISPATCH.orderid', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.orderid', 'Field', 'Updatable', .F.) DBSetProp('DISPATCH.orderid', 'Field', 'UpdateName', 'orderid') DBSetProp('DISPATCH.orderid', 'Field', 'DataType', "I") * Props for the DISPATCH.invoiceno field. DBSetProp('DISPATCH.invoiceno', 'Field', 'KeyField', .T.) DBSetProp('DISPATCH.invoiceno', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.invoiceno', 'Field', 'UpdateName', 'frantisi!Invhead.Invoiceno') DBSetProp('DISPATCH.invoiceno', 'Field', 'DataType', "I") * Props for the DISPATCH.invoicedate field. DBSetProp('DISPATCH.invoicedate', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.invoicedate', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.invoicedate', 'Field', 'UpdateName', 'frantisi!Invhead.invoicedate') DBSetProp('DISPATCH.invoicedate', 'Field', 'DataType', "D") * Props for the DISPATCH.dispatchdate field. DBSetProp('DISPATCH.dispatchdate', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.dispatchdate', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.dispatchdate', 'Field', 'UpdateName', 'frantisi!Invhead.dispatchdate') DBSetProp('DISPATCH.dispatchdate', 'Field', 'DataType', "D") * Props for the DISPATCH.discount field. DBSetProp('DISPATCH.discount', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.discount', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.discount', 'Field', 'UpdateName', 'frantisi!Invhead.discount') DBSetProp('DISPATCH.discount', 'Field', 'DataType', "N(5,2)") * Props for the DISPATCH.surcharge field. DBSetProp('DISPATCH.surcharge', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.surcharge', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.surcharge', 'Field', 'UpdateName', 'frantisi!Invhead.SURCHARGE') DBSetProp('DISPATCH.surcharge', 'Field', 'DataType', "N(5,2)") * Props for the DISPATCH.tax field. DBSetProp('DISPATCH.tax', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.tax', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.tax', 'Field', 'UpdateName', 'frantisi!Invhead.tax') DBSetProp('DISPATCH.tax', 'Field', 'DataType', "N(5,2)") * Props for the DISPATCH.freight field. DBSetProp('DISPATCH.freight', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.freight', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.freight', 'Field', 'UpdateName', 'frantisi!Invhead.freight') DBSetProp('DISPATCH.freight', 'Field', 'DataType', "Y") * Props for the DISPATCH.brokerage field. DBSetProp('DISPATCH.brokerage', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.brokerage', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.brokerage', 'Field', 'UpdateName', 'frantisi!Invhead.brokerage') DBSetProp('DISPATCH.brokerage', 'Field', 'DataType', "Y") * Props for the DISPATCH.shipvia field. DBSetProp('DISPATCH.shipvia', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.shipvia', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.shipvia', 'Field', 'UpdateName', 'frantisi!Invhead.shipvia') DBSetProp('DISPATCH.shipvia', 'Field', 'DataType', "I") * Props for the DISPATCH.fobpoint field. DBSetProp('DISPATCH.fobpoint', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.fobpoint', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.fobpoint', 'Field', 'UpdateName', 'frantisi!Invhead.fobpoint') DBSetProp('DISPATCH.fobpoint', 'Field', 'DataType', "I") * Props for the DISPATCH.picked field. DBSetProp('DISPATCH.picked', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.picked', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.picked', 'Field', 'UpdateName', 'frantisi!Invhead.Picked') DBSetProp('DISPATCH.picked', 'Field', 'DataType', "D") * Props for the DISPATCH.currency field. DBSetProp('DISPATCH.currency', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.currency', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.currency', 'Field', 'UpdateName', 'frantisi!Invhead.Currency') DBSetProp('DISPATCH.currency', 'Field', 'DataType', "I") * Props for the DISPATCH.posted field. DBSetProp('DISPATCH.posted', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.posted', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.posted', 'Field', 'UpdateName', 'frantisi!invhead.posted') DBSetProp('DISPATCH.posted', 'Field', 'DataType', "L") * Props for the DISPATCH.location field. DBSetProp('DISPATCH.location', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.location', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.location', 'Field', 'UpdateName', 'frantisi!ordhead.location') DBSetProp('DISPATCH.location', 'Field', 'DataType', "I") * Props for the DISPATCH.entered field. DBSetProp('DISPATCH.entered', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.entered', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.entered', 'Field', 'UpdateName', 'frantisi!ordhead.entered') DBSetProp('DISPATCH.entered', 'Field', 'DataType', "T") * Props for the DISPATCH.priorityorder field. DBSetProp('DISPATCH.priorityorder', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.priorityorder', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.priorityorder', 'Field', 'UpdateName', 'frantisi!ordhead.priorityorder') DBSetProp('DISPATCH.priorityorder', 'Field', 'DataType', "L") * Props for the DISPATCH.customerpo field. DBSetProp('DISPATCH.customerpo', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.customerpo', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.customerpo', 'Field', 'UpdateName', 'frantisi!ordhead.customerpo') DBSetProp('DISPATCH.customerpo', 'Field', 'DataType', "C(16)") * Props for the DISPATCH.daterequired field. DBSetProp('DISPATCH.daterequired', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.daterequired', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.daterequired', 'Field', 'UpdateName', 'frantisi!ordhead.daterequired') DBSetProp('DISPATCH.daterequired', 'Field', 'DataType', "D") * Props for the DISPATCH.comm1 field. DBSetProp('DISPATCH.comm1', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.comm1', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.comm1', 'Field', 'UpdateName', 'frantisi!ordhead.comm1') DBSetProp('DISPATCH.comm1', 'Field', 'DataType', "C(60)") * Props for the DISPATCH.comm2 field. DBSetProp('DISPATCH.comm2', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.comm2', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.comm2', 'Field', 'UpdateName', 'frantisi!ordhead.comm2') DBSetProp('DISPATCH.comm2', 'Field', 'DataType', "C(60)") * Props for the DISPATCH.comm3 field. DBSetProp('DISPATCH.comm3', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.comm3', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.comm3', 'Field', 'UpdateName', 'frantisi!ordhead.comm3') DBSetProp('DISPATCH.comm3', 'Field', 'DataType', "C(60)") * Props for the DISPATCH.terms field. DBSetProp('DISPATCH.terms', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.terms', 'Field', 'Updatable', .F.) DBSetProp('DISPATCH.terms', 'Field', 'UpdateName', 'terms') DBSetProp('DISPATCH.terms', 'Field', 'DataType', "I") * Props for the DISPATCH.name field. DBSetProp('DISPATCH.name', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.name', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.name', 'Field', 'UpdateName', 'frantisi!customer.name') DBSetProp('DISPATCH.name', 'Field', 'DataType', "C(30)") * Props for the DISPATCH.code field. DBSetProp('DISPATCH.code', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.code', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.code', 'Field', 'UpdateName', 'frantisi!customer.code') DBSetProp('DISPATCH.code', 'Field', 'DataType', "C(6)") * Props for the DISPATCH.creditlmt field. DBSetProp('DISPATCH.creditlmt', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.creditlmt', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.creditlmt', 'Field', 'UpdateName', 'frantisi!customer.creditlmt') DBSetProp('DISPATCH.creditlmt', 'Field', 'DataType', "N(6)") * Props for the DISPATCH.einnumber field. DBSetProp('DISPATCH.einnumber', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.einnumber', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.einnumber', 'Field', 'UpdateName', 'frantisi!customer.einnumber') DBSetProp('DISPATCH.einnumber', 'Field', 'DataType', "C(10)") * Props for the DISPATCH.specialdisc field. DBSetProp('DISPATCH.specialdisc', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.specialdisc', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.specialdisc', 'Field', 'UpdateName', 'frantisi!customer.specialdisc') DBSetProp('DISPATCH.specialdisc', 'Field', 'DataType', "N(5,2)") * Props for the DISPATCH.dollarsuse field. DBSetProp('DISPATCH.dollarsuse', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.dollarsuse', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.dollarsuse', 'Field', 'UpdateName', 'frantisi!customer.dollarsuse') DBSetProp('DISPATCH.dollarsuse', 'Field', 'DataType', "I") * Props for the DISPATCH.customerid field. DBSetProp('DISPATCH.customerid', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.customerid', 'Field', 'Updatable', .F.) DBSetProp('DISPATCH.customerid', 'Field', 'UpdateName', 'customerid') DBSetProp('DISPATCH.customerid', 'Field', 'DataType', "I") * Props for the DISPATCH.custcountry field. DBSetProp('DISPATCH.custcountry', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.custcountry', 'Field', 'Updatable', .F.) DBSetProp('DISPATCH.custcountry', 'Field', 'UpdateName', 'custcountry') DBSetProp('DISPATCH.custcountry', 'Field', 'DataType', "I") * Props for the DISPATCH.totordered field. DBSetProp('DISPATCH.totordered', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.totordered', 'Field', 'Updatable', .F.) DBSetProp('DISPATCH.totordered', 'Field', 'UpdateName', 'totordered') DBSetProp('DISPATCH.totordered', 'Field', 'DataType', "I") * Props for the DISPATCH.totallocated field. DBSetProp('DISPATCH.totallocated', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.totallocated', 'Field', 'Updatable', .F.) DBSetProp('DISPATCH.totallocated', 'Field', 'UpdateName', 'totallocated') DBSetProp('DISPATCH.totallocated', 'Field', 'DataType', "I") * Props for the DISPATCH.totshipped field. DBSetProp('DISPATCH.totshipped', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.totshipped', 'Field', 'Updatable', .F.) DBSetProp('DISPATCH.totshipped', 'Field', 'UpdateName', 'totshipped') DBSetProp('DISPATCH.totshipped', 'Field', 'DataType', "I") * Props for the DISPATCH.ok field. DBSetProp('DISPATCH.ok', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.ok', 'Field', 'Updatable', .F.) DBSetProp('DISPATCH.ok', 'Field', 'UpdateName', 'ok') DBSetProp('DISPATCH.ok', 'Field', 'DataType', "L") * Props for the DISPATCH.rdytoship field. DBSetProp('DISPATCH.rdytoship', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.rdytoship', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.rdytoship', 'Field', 'UpdateName', 'Frantisi!RdyToShip.rdytoship') DBSetProp('DISPATCH.rdytoship', 'Field', 'DataType', "I") * Props for the DISPATCH.shipped field. DBSetProp('DISPATCH.shipped', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.shipped', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.shipped', 'Field', 'UpdateName', 'Frantisi!Shipped.shipped') DBSetProp('DISPATCH.shipped', 'Field', 'DataType', "I") * Props for the DISPATCH.specialord field. DBSetProp('DISPATCH.specialord', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.specialord', 'Field', 'Updatable', .F.) DBSetProp('DISPATCH.specialord', 'Field', 'UpdateName', 'specialord') DBSetProp('DISPATCH.specialord', 'Field', 'DataType', "L") * Props for the DISPATCH.dispordno field. DBSetProp('DISPATCH.dispordno', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.dispordno', 'Field', 'Updatable', .F.) DBSetProp('DISPATCH.dispordno', 'Field', 'UpdateName', 'dispordno') DBSetProp('DISPATCH.dispordno', 'Field', 'DataType', "C(11)") * Props for the DISPATCH.onhold field. DBSetProp('DISPATCH.onhold', 'Field', 'KeyField', .F.) DBSetProp('DISPATCH.onhold', 'Field', 'Updatable', .T.) DBSetProp('DISPATCH.onhold', 'Field', 'UpdateName', 'frantisi!customer.onhold') DBSetProp('DISPATCH.onhold', 'Field', 'DataType', "L")Any ideas on how to speed this up, would be greatly appreciated.