Program your own Posting Rules

by Dr. Peter Wayne

When you define a posting rule for a table, then Alpha Five will change a related entry in a second table, following the instructions you have detailed in your posting rule. You have the choice of asking Alpha Five to add to, subtract from, or replace the contents of the field in the second table. If you like, you can tell Alpha to "roll back" the post if the new record is later deleted.

A common use of posting is a sales and inventory application. Items are sold from stock, and as sales are recorded, the on-hand quantity in the inventory table is reduced. In such a straightforward application, Alpha Five's built-in posting rule works perfectly well, and there is no need to write your own posting rule.

For this article, we'll begin with two simple tables, stock.dbf and sales_from_stock.dbf:

We want to decrement the On_Hand field in stock as sales are entered in sales_from_stock. This is easily done through the posting tab in Field Rules for sales_from_stock. The Posting tab is filled out as follows:

In this example, Alpha Five's posting rule will work just fine. However, there are some limitations to posting, which may force you to write your own posting rules in Xbasic. These limitations are:

• Posting can be reversed when a record is deleted, but there is no simple way to undelete a record in Alpha Five. If you choose to mark a record rather than delete one to indicate an invalid record, Alpha Five's posting rules will not roll back your post.
• Posting is not always reliable if the source table and the target table are in a set together.
• If you create, delete, or modify records through an Xbasic script, Alpha Five's posting rule will not execute. This behavior will be different under A5 if the table is accessed with a form-attached table pointer, such as table.current(). If the table is opened with a new table pointer, then the posting rules, along with other field rules, will be ignored by an Xbasic script.
• Similar to the last limitation, posting rules do not cascade. That is, in our example, if a posting rule were in effect for a field in the stock table, it would not activate. Posting rules only are activated when the source table is interactively modified.

We can write our own posting rule in Xbasic that will help overcome some of these limitations. We will still not be able to cascade posting rules, but at least we can use whatever script we develop to post to one table as a model to post to another.

The first posting rule we will write will be for the OnSaveRecord event of the sales_from_stock table. Make sure that you choose sales_from_stock as the container and OnSaveRecord as the event:

Figure 1. Choose Sales_from_stock as the container and OnSaveRecord as the event for the script.

Next, select Code, Convert to Xbasic, and type this into the Xbasic window:

dim inventory as p
dim item as c
dim sold as n
dim tbl as p
dim rec as n

tbl=table.get("sales_from_stock")
item=tbl.item
sold=tbl.sold
inventory=table.open("stock")
inventory.index_primary_put("item")
rec=inventory.fetch_find(item)
if rec<0 then
	' no matching inventory item found
	ui_msg_box("No matching stock entry found for",item,UI_STOP_SYMBOL)
	inventory.close()
	end 
end if
inventory.change_begin()
	inventory.on_hand=inventory.on_hand-sold
inventory.change_end(.t.)
inventory.close()

Script 1. OnSaveRecord script for sales_from_stock - 1st attempt.

Do not forget: you now have to remove the original posting rule for the sold field.

If you make a few entries in the stock table, and then make a new entry in the sales_from_stock table, you will see that Script 1 will properly subtract the amount in sold from the on_hand value. But wait - what happens when you change a record in sales_from_stock? This script does not properly adjust for modified records.

When a record is changed, we want to do 2 operations in succession:

1. add back the old value of sold
2. then subtract the updated value of sold

We already have the script to do #2 in the OnSaveRecord script. We only have to add a "reverse post" script to the OnChangeRecord event:

dim oldval as n
dim inventory as p
dim item as c
dim rec as n
dim tbl as p

tbl=table.get("sales_from_stock")
inventory=table.open("stock")
inventory.index_primary_put("item")
item=tbl.item
rec=inventory.fetch_find(item)
if rec<0 then
	'unable to find matching item in stock table
	inventory.close()
	end 
end if
oldval=tbl.sold
inventory.change_begin()
	inventory.on_hand=inventory.on_hand+oldval
inventory.change_end(.t.)
inventory.close()

Script 2. OnChangeRecord script to "reverse post" sales_from_stock.

This script was up for almost 4 years before Daniel Weiss, a member of the NY Alpha User Group, pointed out that if the user presses "escape" after beginning a change, the OnChangeRecord event still fires, resulting in a "reverse post" even though the change has been cancelled.

The solution is simple in concept: record the old value of the sold field in the OnChangeRecord event, but wait to execute the reverse post until the OnSaveRecord event. If the change is abandoned by the user, the OnSaveRecord will never be triggered. Here are the modified scripts:

dim shared oldval as n
dim shared entry_type as c
tbl=table.get("sales_from_stock")
oldval=tbl.sold
entry_type="change" 

Script 3. Modified OnChangeRecord script to obtain "old value of sold" from sales_from_stock.

dim inventory as p
dim item as c
dim sold as n
dim tbl as p
dim rec as n
dim shared entry_type as c
dim shared oldval as n

tbl=table.get("sales_from_stock")
item=tbl.item
sold=tbl.sold
inventory=table.open("stock")
inventory.index_primary_put("item")
rec=inventory.fetch_find(item)
if rec<0 then
   ' no matching inventory item found
   ui_msg_box("No matching stock entry item found for ",item)
   inventory.close()
   end
end if
inventory.change_begin()
	select 
		case entry_type="enter"
			inventory.On_hand=inventory.On_hand-sold
		case entry_type="change"
			inventory.On_hand=inventory.On_hand-sold+oldval
	end select
inventory.change_end(.t.)
inventory.close()

Script 4. Modified OnSaveRecord script.
We also have to reset the "entry_type" variable when a new entry is made. This is done in the OnEnterRecord event:

dim shared entry_type as c
entry_type="enter"

Script 5. OnEnterRecord script.

Additions, Modifications - what about Deletions?

What do we do about deleted records? At first thought, you might think of placing Script 2, the "reverse post" script, in the OnDeleteRecord event. That seems reasonable but it won't work, because at the time that the OnDeleteRecord script is actuated, the record pointer is undefined. That is, when OnDeleteRecord is executing, there is no meaning to tbl.sold and tbl.item in Script 2.

If we can't use OnDeleteRecord, then what can we use? Here is one case in which we use the Can event instead of the On event to obtain the field values. We can take the entire text of Script 2 and copy it to the CanDeleteRecord event.

And Don't Forget Marking

Marking is a separate operation from deletion in A5v3. Marking and unmarking has no effect on Alpha Five's built-in posting rules, but we can make marking and unmarking obey our custom posting rules. We simply have to copy Script 2 to the OnMarkRecord event, and copy Script 1 to the OnUnMarkRecord event.

And now, to get fancy

We just saw with Marking how we can add functionality that is missing in Alpha Five's built-in posting rules. We can ask our posting script to notify us when we are running low on an item. We just have to add 3 lines to Script 4:

dim inventory as p
dim item as c
dim sold as n
dim tbl as p
dim rec as n
dim shared entry_type as c
dim shared oldval as n

tbl=table.get("sales_from_stock")
item=tbl.item
sold=tbl.sold
inventory=table.open("stock")
inventory.index_primary_put("item")
rec=inventory.fetch_find(item)
if rec<0 then
   ' no matching inventory item found
   ui_msg_box("No matching stock entry item found for ",item)
   inventory.close()
   end
end if
inventory.change_begin()
	select 
		case entry_type="enter"
			inventory.On_hand=inventory.On_hand-sold
		case entry_type="change"
			inventory.On_hand=inventory.On_hand-sold+oldval
	end select
inventory.change_end(.t.)

if inventory.on_hand<=inventory.reorder_level then
	ui_msg_box("You are running low on ",item,UI_ATTENTION_SYMBOL)
end if

inventory.close()

Script 6. Modification of Script 4 to warn of low inventory. The red lines are the modification.

You can even verify that there is adequate stock before allowing the sale to be recorded. The best place to put this code is on the CanWriteField event for the sold field. If we place a cancel() statement in the CanWriteField script, it will prevent the field entry from being recorded:

dim inventory as p
dim item as c
dim sold as n
dim tbl as p
dim rec as n

tbl=table.get("sales_from_stock")
item=tbl.item
sold=val(a_field_value)
inventory=table.open("stock")
inventory.index_primary_put("item")
rec=inventory.fetch_find(item)
if rec<0 then
	' no matching inventory item found
	cancel()
	ui_msg_box("No matching stock entry found for",item,UI_STOP_SYMBOL)
	inventory.close()
	end 
end if
if inventory.on_hand<sold then
	cancel()
	ui_msg_box("You do not have enough stock of",item,UI_STOP_SYMBOL)
end if

Script 7. CanWriteField script for the sold field.

As always, remember that it is important to place the cancel() statement before any user interface statements, such as ui_msg_box(). If the ui_msg_box() precedes the cancel() statement, then Alpha Five might be confused as to which window has focus and which window is the intended recipient of the cancel() statement.

You also need a similar script to that in Script 7 for the table's CanUnMarkRecord event, since you want to block the unmarking of a record if it results in a sale that exceeds the quantity on hand for that item. I won't give you that script, but it's a pretty straightforward adaptation of Script 7.
Download the tables used in this article.

8/28/98; corrected 4/21/02 - pkw

Don't forget, we need your feedback to make this site better!

Return to home