Custom Tables for Item Mappings¶
The screen script solution does what we need but is limited in that the item mappings are hard-coded. The user cannot add new mappings themselves and if there are many then keeping them in the source becomes unwieldy.
Enter Extender’s Custom Tables. They make it easy to store, retrieve, and update tables specific to your customization in the database.
Assume for now that each Item will only ever map to one other. We can define
a database table, OEADDLNE.VIITMMAP
, to allow the user to manage the
mappings.
Designing the Table Schema¶
Before creating a table it needs a schema. The correct fields and keys (which are also indexes) need to be identified. Every item only maps to one other, we can use the trigger item as a key.
When thinking about keys, think about how you access the data. If you always use two fields together to retrieve from a table, be sure to add a compound key on those fields.
We need the same fields as we had view script parameters, so the following should do:
Table name: VIITMMAP
Fields:
- TRIGITEM (str): item that triggers the new line
- NEWITEM (str): the item to insert
- QTYORDERED (bcd): the quantity ordered
Keys:
- (TRIGITEM, )
Creating the Table Schema¶
The table schema will be embedded in the module file. You have two options for creating it: interactively with the Custom Tables tool or writing it by hand in the modules file.
Using Custom Tables¶
This is the best way to start. Once you get the hang of it you can craft them by hand.
To use the Custom Tables tool, start by creating a module:
- Navigate to Extender -> Setup -> Modules
- Insert a new row using the module name (
OEADLNE
)
Define the custom table:
- Navigate to Extender -> Setup -> Custom Tables
- Set the table name to
OEADDLNE.VIITMMAP
- Define the fields. Use
accpacViewInfo
or the extender enquiries to find the correct field names, finder information, and description lookups. - Add the key in the
Keys
tab. - Save the table.
Now that the table is defined you can export the module from the Modules screen and the table definition will be included.
Crafting by Hand¶
After a while you’ll be able to write table definitions by hand. To start, this is what is automatically generated on module export:
[MODULE]
id=OEADDLNE
name=OEADDLNE
desc=Add an OE Order Line after a particular item is added.
company=2665093 Ontario Inc
version=0.1.0
website=https://2665093.ca
[TABLE]
name=OEADDLNE.VIITMMAP
dbname=VIITMMAP
[FIELD1]
field=TRIGITEM
datatype=1
size=24
mask=%24C
desc=Trigger item number
ftable=IC0310
ffield=ITEMNO
lookup=FMTITEMNO
[FIELD2]
field=NEWITEM
datatype=1
size=24
mask=%24C
desc=Item number to insert
ftable=IC0310
ffield=ITEMNO
lookup=FMTITEMNO
[FIELD3]
field=QTYORDERED
datatype=6
size=10
decimals=5
desc=Quantity
[KEY1]
KEYNAME=TRIGITEM
FIELD1=TRIGITEM
allowdup=0
Simply add the SCRIPT
block and we have a full module.
Adding Entries to the Table¶
Adding entries is easy using the Extender -> Setup -> Custom Table Editor.
Simply open the editor, open the OEADDLNE.VIITMMAP
table, and start adding.
For now, try to add the item we know about, A1-103/0 -> A1-105/0@1.
Adding the Lookup to the Script¶
Now we just need to replace out hard coded values with a lookup from our custom
table. Custom tables are accessed through the view layer. Instead of opening
them based on the View ID (i.e. VI0107
), always access them by module
qualified table name. There is no guarantee that your table will always have
the same roto, so don’t use it.
Because TRIGITEM
is our key field, we will use it to look up the mapping.
The lookup will go something like this:
view = openView("OEADDLNE.VIITMMAP")
view.recordClear()
view.put("TRIGITEM", itemno)
r = view.read()
Extender functions return 0 on success, so if r
is 0 then there is a
mapping for itemno
and the view has read it in. Any other return
indicates that itemno
is not a trigger item and no action is required.
from accpac import *
class OeAddLineUI(UI):
"""A UI customization that monitors the order details for a trigger
item being inserted and adds a new line."""
def __init__(self):
super().__init__()
# Open the data source
self.adsOEORDD = self.openDataSource("adsOEORDD")
# Open the custom table.
self.viitmmap = openView("OEADDLNE.VIITMMAP")
# Assign the onAfter callback to the *function*
self.adsOEORDD.onAfterInsert = self.adsOEORDDonAfterInsert
self.show()
def get_trigger(self, itemno):
"""Get the trigger information for this item number from the table.
:param itemno: item number to loopkup trigger information for
:type itemno: str
:rtype: list
:returns:
- If the item number is not in the map table: []
- if the item number is in the table: [newitem, qtyordered]
"""
rc = self.viitmmap.recordClear()
pt = self.viitmmap.put("TRIGITEM", itemno)
r = self.viitmmap.read()
if r != 0 or pt != 0 or rc != 0:
return []
return [self.viitmmap.get("NEWITEM"),
self.viitmmap.get("QTYORDERED"), ]
def adsOEORDDonAfterInsert(self, result):
"""After updating, if the item is "A1-103/0", insert a new line."""
# Check if the item matches an item in the custom table map :
# trigger will be [newitem, qty] if it does, [] (False) if it
# doesn't.
trigger = self.get_trigger(me.get("ITEM"))
if trigger:
# Generate a new record in the view
rc = me.recordClear()
rg = me.recordGenerate()
if rc != 0 or rg != 0:
showMessageBox("Failed to generate new line.")
return
# Populate the record
pi = me.put("ITEM", trigger[0])
pq = me.put("QTYORDERED", trigger[1])
if pi != 0 or pq != 0:
showMessageBox("Failed to put values in new line.")
return
# Save the record
sv = me.insert()
if sv != 0:
showMessageBox("Failed to save new line.")
return None
def main(*args, **kwargs):
ui = OeAddLineUI()
Done. The custom table is now intergated with the script. The user can add and manage as many mappings as they’d like with the custom table editor.
That’s all for now, just a few things to close out…