Self-composing views¶
Sage 300 uses the concept of views to provide a simplified interface for accessing data. The view layer sits between the user interfaces, like screens and imports, and the database. The views are where Sage implements and enforces business logic.
Extender provides access to views in Python through the View
object.
A view is usually backed by exactly one database table and provides access
to a single record at a time. Sage allows views that share a unique identifier
to be composed with one another.
When two views are composed, when the unique key field changes in one view, it is changed in all other composed views as well. This makes accessing related data much easier: no need to seek twice.
Consider the case where a script must set the optional field MYFIELD
on a
detail line to HASX
if any of the lot numbers in an OE Order start with X
.
Working with Extender View
objects and without composition:
# Open the order header and seek to the order
oe0520 = openView("OE0520")
if not oe0520: # If the open failed, return.
return 1
rc = oe0520.recordClear()
if rc != 0: # If the record clear failed.
return rc
o = oe0520.order(1)
if o != 0: # If setting view order fails.
return o
pu = oe0520.put("ORDNUMBER", "ORD453")
if pu != 0: # If setting the key field fails.
return pu
r = oe0520.read()
if r != 0: # If reading the record fails.
return r
# Get the order unique ID to lookup the detail lines.
orduniq = oe0520.get("ORDUNIQ")
# Open the order details and seek to the beginning
oe0500 = openView("OE0500")
if not oe0500:
return 1
rc = oe0500.recordClear()
if rc != 0:
return rc
br = oe0500.browse("", 1)
if br != 0:
return br
# In preparation for reading lots and optional fields, open the views.
oe0507 = openView("OE0507") # Order detail lots
oe0501 = openView("OE0501") # Order detail optional fields
if not (oe0507 and oe0501):
return 1
# For each detail line in the order
while(oe0500.fetch() == 0):
# Seek the lot view to the lots for this line.
linenum = oe0500.get("LINENUM")
rc = oe0507.recordClear()
if rc != 0:
return rc
br = oe0507.browse(
'ORDUNIQ = "{}" AND LINENUM = "{}"'.format(orduniq, linenum))
if br != 0:
return br
# Look at each lot associated with the line
while(oe0507.fetch() == 0):
# Get the lot number
lotnumf = oe0507.get("LOTNUMF")
# Check the condition
if lotnumf and lotnumf.startswith("X"):
# Try to read the optional field
rc = oe0501.recordClear()
if rc != 0:
return rc
# The index requires order, line, and optional field keys.
po = oe0501.put("ORDUNIQ", orduniq)
pl = oe0501.put("LINENUM", linenum)
pf = oe0501.put("OPTFIELD", "MYFIELD")
if po != 0 or pl != 0 or pf != 0:
continue
r = oe0501.read()
if r != 0:
# The field doesn't exist yet, create it.
rg = oe0501.recordGenerate()
if rg != 0:
return rg
po = oe0501.put("ORDUNIQ", orduniq)
pl = oe0501.put("LINENUM", linenum)
pf = oe0501.put("OPTFIELD", "MYFIELD")
pv = oe0501.put("VALUE", "HASX")
if po != 0 or pl != 0 or pf != 0 or pv != 0:
return 1
ins = oe0501.insert()
if ins != 0:
return ins
else:
# The field does exist, check it is correct.
if oe0501.get("VALUE") != "HASX":
# It isn't set correctly, update it.
pv = oe0501.put("VALUE", "HASX")
if pv != 0:
return pv
up = oe0501.update()
if up != 0:
return up
It is a bit of a mouthful. Most of the code is opening, setting up, and
seeking views. At every step, we need to search for the orduniq
and/or
linenum
. This is where composition helps: it eliminates the a lot
of the repetitive work by automatically filling in the unique keys for composed
views.
Views can be composed at runtime. We can compose the views we are
working with as they all share the ORDUNIQ
key.
Let’s try again but this time manually compose the views we need.
# Open the views
oe0520 = View("OE0520") # Order Header
oe0500 = View("OE0500") # Order Details
oe0507 = View("OE0507") # Order Detail Lot Numbers
oe0501 = View("OE0501") # Order Detail Optional Field
if not (oe0520 and oe0500 and oe0507 and oe0501):
return 1
# Compose them all together.
c20 = oe0520.compose(oe0500, None, None, None, None, None)
# The arguments to compose can be a bit like a shell game...
c00 = oe0500.compose(oe0520, oe0501, None, None, None, oe0507)
c07 = oe0507.compose(oe0500)
c01 = oe0501.compose(oe0500)
# Make sure the composing was successful for all views
if c20 != 0 or c00 != 0 or c07 != 0 or c01 != 0:
return 1
rc = oe0520.recordClear()
if rc != 0: # If the record clear failed.
return rc
o = oe0520.order(1)
if o != 0: # If setting view order fails.
return o
pu = oe0520.put("ORDNUMBER", "ORD453")
if pu != 0: # If setting the key field fails.
return pu
r = oe0520.read()
if r != 0: # If reading the record fails.
return r
# Now a magical thing has happened, the detail view is ready to
# read the lines for this order.
rc = oe0500.recordClear()
if rc != 0:
return rc
# For each detail line in the order
while(oe0500.fetch() == 0):
# The fetch causes the optional field and lot views to filter out
# all but records for the current line.
rc = oe0507.recordClear()
if rc != 0:
return rc
# Look at each lot associated with the line
while(oe0507.fetch() == 0):
# Get the lot number
lotnumf = oe0507.get("LOTNUMF")
# Check the condition
if lotnumf and lotnumf.startswith("X"):
# Because the ORDUNIQ and LINENUM are set implicitly through
# composition, use the setOptionalField helper from the
# Extender View class
so = oe0501.setOptionalField("MYFIELD", "HASX")
if not so:
return so
That is much better. However, you need to know how to compose these things. There are thousands of views in Sage and not all views can be composed with all others. Composition is also uni-directional: the Order Headers view is composed with the Order Details but the Order Details must also be composed with the Order Header!
Consider the OE Order Header. In the example above, the header is only partially composed. It can be composed with up to five other views, each of which can be composed with many others. Fully composing the OE Header View involves opening and composing 13 other views, a total of 26 lines.
The extools.view.ExView()
class is self-composing, so you don’t need
to worry about opening and checking the views or playing the shell game with
compose arguments.
# ExViews use exceptions, wrap it all in a try and provide helpful
# output if an error is encountered.
try:
# Open the Order Header view
oe0520 = ExView("OE0520")
# Fully compose it, creating a new property for each related view
oe0520.compose()
# Set the order to search by ORDNUMBER
oe0520.order(1)
# Seek to the order we want
oe0520.seek_to(ORDNUMBER="ORD453")
# For each detail line in the order
for line in oe0520.oe0500.lines():
# For each lot in the detail line
for lot in line.oe0507.lines():
# Get the lot number
lotnumf = lot.get("LOTNUMF")
# Check the condition
if lotnumf and lotnumf.startswith("X"):
# Use the setOptionalField helper from the ExView class
oe0501.setOptionalField("MYFIELD", "HASX")
except ExViewError as e:
showMessage("Failed to set MYFIELD: {}".format(e))
return 1
The call to extools.view.ExView.compose()
introspects the view to
find the other views that it can be composed with. It then opens them all with
the correct indexing and composes them with one another. Each composed view is
set as a property of the parent view so you can access them easily.
In the manual compose example, the views OE0500
, OE0501
, OE0507
,
and OE0520
were composed together. The “compose tree” for those views is:
OE0520
|_ OE0500
|_ OE0520
|_ OE0501
| |_ OE0500
|_ OE0507
|_ OE0500
Composing the OE0520
ExView
creates the following properties on the
exview
instance:
exview = ExView("OE0500")
exview.compose()
OE0520 exview
|_ OE0500 exview.oe0500
|_ OE0520 exview.oe0500.oe0520 (back to self)
|_ OE0501 exview.oe0500.oe0501
| |_ OE0500 exview.oe0500.oe0501.oe0500 (back to parent)
|_ OE0507 exview.oe0500.oe0507
|_ OE0500 exview.oe0500.oe0507.oe0500 (back to parent)
Note that because views are often composed bi-directionally, each composed view has a property that links back to its parent.