Posting � Undo on Delete via Set?

[Daniel Weiss]

If I am correct, its seems to me that undo on delete for a posting filed rule dose not undo if the child record is deleted by the parent record. For example in a invoice set, if the items quantity field rule is set to post (and undo on delete) the in stock quantity in the product table, if the item record is deleted on its own it will put the quantity back in to stock, but if the entire invoice is deleted, the in stock quantity is not changed.

This is something I haven�t noticed up till now, manly because on must of my applications I don�t allow the users to delete an invoice, I rather put a cancel button which clears the invoice and restocks the items via a script. (The user can then reuse the blank invoices # for a new order, and there are no missing invoices numbers).
It only came to my attention now when in a receivables set, the parent is the payment # and amount, the child is the payments breakdown per invoice which posts to the paid amount field for the invoice, if the entire payment is deleted the un posting doesn�t work,
Is this a bug or that�s the way it should be?
Thanks
Dan

[Jay Talbott]

RE: Posting � Undo on Delete via Set?

Daniel,

If I understand you correctly, you are deleting the parent, which would make the child "disappear", but it does not undo the posting. This is correct, as deleting the parent does not delete the child unless you have referential integrity selected up in the set. You have only orphaned the child record(s).

Your delete needs to delete the child records first, then delete the parent. This can be done manually or via a script. I am not a big fan of using referential integrity, but in this case, it may need to be used to elegantly delete the child records.

Hope this helps.

Jay Talbott

[Daniel Weiss]

RE: Posting � Undo on Delete via Set?

Thanks Jay
Yes, you are correct but I do use referential integrity and even so, it doesn't undo on delete.
Look at the sample invoicing set shipped with alpha, which uses referential integrity. Deleting an invoice wont effect the stock.
Dan

[whanigsberg]

RE: Posting � Undo on Delete via Set?

There has been some discussion of the interaction between referential integrity and posting rules so you might want to run a search.

One key to understanding what's going on is to investigate the connection between methods and field level events. By chance I had to look into this last weekend.

I had some on_delete code for a child table that I didn't want to execute while deleting the parent record. It was only needed when deleting a child record separately. I was not using referential integrity but was using the range_delete method on the child table.

I needn't have worried. The manual describes range_delete as a high-level method and, sure enough, the trace window showed that the child table's on_delete event does *not* occur when this method is used. This was perfect for my purposes.

Of course, Daniel wants the opposite result. So he could adopt "lower-level" methods which *do* trigger the posting rule.

Since the child table is part of a set he can use table_current(2) to get a pointer to it and fetch through the current range deleting the records one at a time. This would probably trigger the posting rule.

The trace window will show you what's going on.

Bill

[Daniel Weiss]

RE: Posting � Undo on Delete via Set?

Thanks Bill

You are perfectly right, Deleting a record via a script to my knowledge doesn�t trigger any field rules, which I could understand, if your capable to write a script you can add the lines necessary to restock or whatever your aiming for. In fact as I mentioned before I always do it with my invoicing set�s. I have two scripts one for returns of good merchandise and one for damaged good. If the user wants to clear an invoice for any reason, they can use the restock option. I actually wrote a similar script now for the receivables set I mentioned before.
I�m just bringing this to the attention to Alpha, after all if a end user that purchase Alpha without having any knowledge in scripting and would like to use the sample invoice shipped with alpha (with just some minor changes) would run in to problem not knowing why his stock is not acquired.

Do I have a point or am I just picking?

below are the two scripts i use

1)Script for Returns of good merchandise;

password = ui_get_password("Password","Enter the password","","*")
check = lookup("logs.dbf","log = '" + var->password + "'","class")
if check orno"
items.query_create()

items.fetch_first()
while .not. items.fetch_eof()
itm = items.item_code
qty = items.shipped

prod.fetch_first()
while .not. prod.fetch_eof()
pitn = prod.item_code
pqty = prod.Quantity_In_Stock
if pitn = itm
goto found
end if
prod.fetch_next()
end while
found:
prod.change_begin()
prod.Quantity_In_Stock = pqty + qty
prod.change_end(.t.)

items.fetch_next()
end while

items.delete_range()

query.description="for return"
query.order=""
query.options="N"
query.filter = "invoice_no = var->orno"
idx = items.query_create()
idx.drop()
items.close()
prod.close()

Adv_Ref.value = ""
Deposit.value = 0
Ordered_by.value = ""
paidsp.value = 0
po_no.value = ""
sales_rep.value = ""
Ship_Address_1.value = ""
Ship_Address_2.value = ""
Ship_City.value = ""
Ship_Country.value = ""
Ship_State.value = ""
Ship_Tp.value = ""
Ship_Via.value = ""
Ship_Zip.value = ""
Shipping_Cost.value = 0
SALES_TAX_PERCENT.value = 0
PAYMENT_AMOUNT.value = 0
ADJUSTMENT.value = 0
posted_to_customer.value = 0

parentform.commit()
topparent.resynch()
Browse1.Refresh()
done:
end


2)Script for dammeged goods;

password = ui_get_password("Password","Enter the password","","*")
check = lookup("logs.dbf","log = '" + var->password + "'","class")
if check 0
code=UI_OK+UI_STOP_SYMBOL
note = "CHANGES CANNOT BE MADE TO THE CUSTOMER ID FIELD FOR "
note = "AN INVOICE TAHT WAS PARTIALLY OR FULLY PAID OR IF COMMISSION WAS PAID "
note = note + "TO SALESPERSON - REMOVE PAYMENTS and/or COMMISSIONS FIRST."
ui_msg_box("Stop",note, code)
cancel()
end
end if
code = UI_YES_NO+UI_QUESTION_SYMBOL+UI_SECOND_BUTTON_DEFAULT
note = "This option CLEARS the entire Invoice, and the ITEMS ARE NOT RESTOCKED. "
note = note + " Use this for Returned Orders with damaged merchandise. "
note = note +" CONTINUE?"
response = ui_msg_box("EZ Link",note, code)
if response = UI_YES_SELECTED
goto doit
end if
goto done
doit:
orno = INVOICE_NO.value
dim items as p
dim prod as p
dim query.order as c
dim query.description as c
dim query.options as c
dim query.filter as c

items = table.open(a_db_current_path+"invoice items.dbf")
query.description="for return"
query.order=""
query.options="N"
query.filter = "invoice_no = var->orno"
items.query_create()

items.delete_range()
idx = items.query_create()
idx.drop()
items.close()

Adv_Ref.value = ""
Deposit.value = 0
Ordered_by.value = ""
paidsp.value = 0
po_no.value = ""
sales_rep.value = ""
Ship_Address_1.value = ""
Ship_Address_2.value = ""
Ship_City.value = ""
Ship_Country.value = ""
Ship_State.value = ""
Ship_Tp.value = ""
Ship_Via.value = ""
Ship_Zip.value = ""
Shipping_Cost.value = 0
SALES_TAX_PERCENT.value = 0
PAYMENT_AMOUNT.value = 0
ADJUSTMENT.value = 0
posted_to_customer.value = 0

parentform.commit()
topparent.resynch()
Browse1.Refresh()
done:
end

Thanks
Dan

[whanigsberg]

RE: Posting � Undo on Delete via Set?

Dan,

That's interesting code.

You are probably aware of Dr. Wayne's article "Program your own Posting Rules". In it he states that in V4 (as opposed to V3) tables opened with form-level methods will activate posting code. It seems also to say that posting will not cascade. (V5 had not yet been released when the article appeared.)

I conclude the following:
-if you delete a parent record in a set using table_current(1) any posting rule defined for the child table won't execute; but,
-if you address the child table as table_current(2)and delete a record the rule will execute. It would be nice to know whether this is the case and for which methods.

Bill

[Daniel Weiss]

RE: Posting � Undo on Delete via Set?

Thanks again Bill
But first of all I don�t understand what you mean by �(V5 had not yet been released when the article appeared.)� is it out now?
And also I tried this script using current(2) as the table pointer, it still did not restock the deleted quantity, you can try it maybe I�m doing something wrong?
tbl = table.current(2)
query.description="unpost"
query.order=""
query.options="N"
query.filter = "invoice_no = '" + invoice_no.text + "'"
tbl.query_create()
tbl.delete_range()

But anyway that�s not my point, finding the rite script to do the job is not what I�m talking about. I just think if a parent record is deleted (without using any scripts) field rules of the child records should apply, if referential integrity is used. If I�m wrong ill accept.
Dan

[whanigsberg]

RE: Posting � Undo on Delete via Set?

Hi Dan,

"V5 had not yet been released " That was a typo; I should have written V4.

I take your point that you are not looking for the right method as you can already do what you want to do; the issue is the program's default behavior.

I frankly have no idea why they have gone the way they have on this point. They undoubtedly have their reasons but I don't know what they are.

And we both know that the script is not the point but if you open table_current(2) the set already has a query on it so you don't have to run another one (if I correctly interpret your code). And delete_range won't trigger the posting rule but another method would.

Bill

[Daniel Weiss]

RE: Posting � Undo on Delete via Set?

Thanks Bill

I have to thank you for what you have just learned me now, I never paid attention to the fact that by using the open method of table current (2) the set already has a query on it. I simply never tried it without a using a query,

Thanks for the information, you always learn something new.
Dan

[Selwyn Rabins]

RE: Posting � Undo on Delete via Set?

You are correct. If a child record is deleted indirectly (i.e. by deleting a parent, and then cascading the delete to the child), the child's posting rules are not triggered.

If you want to have the child posting rules in place, you should turn off cascade delete in the set design, and then write a script that deletes each of the child records individually.

[MoGrace]

Re: Posting � Undo on Delete via Set?

I bumped this old thread because it was the only one I could find where Selwyn himself gives the answer I was looking for. I want to use a button to delete the records that appear in the browse on a form based on a set. If the user manually deletes the records the post field rule works correctly - but not with this script: (Am I not following his suggestion correctly?)

'Delete all applied payments displayed in the browse
DIM tbl as P
DIM commit_flag as L
DIM n as N = 0
DIM i as N = 0
IF parent.mode_get() <> "VIEW" THEN
	parent.commit()
END IF
tbl = table.get("inv_pmt")
n = browse1.records_get()
IF n = 0 THEN
	END'
END IF
tbl.fetch_first()
i = i+1
while i <= n
	commit_flag = .t. 
	tbl.change_begin([COLOR="Red"]HONOR_FIELD_RULES[/COLOR])
		on error goto ERRMSG
		[COLOR="red"]tbl.delete()[/COLOR]	[COLOR="Navy"]'DOES NOT UNPOST ON DELETE[/COLOR]
		i=i+1
	tbl.change_end(commit_flag)	
end while
if commit_flag = .f. then
	ui_msg_box("Clear Payments - Error","Please delete payments manually",48)
	end
end if
browse1.refresh()
browse2.resynch()
tabbed1.tab_set(2)
END'
ERRMSG: 
commit_flag=.f.
script_play("ERRMSG")
on error goto 0
RESUME NEXT

[Tom Cone Jr]

Re: Posting � Undo on Delete via Set?

I think the honor_field_rules parameter is intended to be passed to the change method as a logical...

so it would be

tbl.change_begin(.t.)

Let us know if that causes the post field rule "undo on delete" to work. I will be pleased if it does, but not surprised if it doesn't. I recall having to adjust the related table field values in my scripts when doing this sort of delete, but maybe Alpha is handling it for us now.

[Stan Mathews]

Re: Posting � Undo on Delete via Set?

I think the honor_field_rules parameter is intended to be passed to the change method as a logical...

so it would be

tbl.change_begin(.t.)

Let us know if that causes the post field rule "undo on delete" to work. I will be pleased if it does, but not surprised if it doesn't. I recall having to adjust the related table field values in my scripts when doing this sort of delete, but maybe Alpha is handling it for us now.

I believe the honor_field_rules is a "system" variable of some sort. Perhaps it might be termed a system constant.

Interactive yields

? honor_field_rules
= .T.

[MoGrace]

Re: Posting � Undo on Delete via Set?

Thanks Tom & Stan but that didn't work either. I tried scripting the unpost routine myself, but since INVO_HDR is a child of INV_PMT, I had trouble getting a pointer to INVO_HDR. Wouldn't fetching the 1:N records displayed in the browse (INV_PMT) automatically point to the 1:1 link for INVO_HDR? I haven't tried form methods yet because I am unsure of the syntax to change the field values and how to fetch the records.

[Stan Mathews]

Re: Posting � Undo on Delete via Set?

but since INVO_HDR is a child of INV_PMT, I had trouble getting a pointer to INVO_HDR.

In a two table set, the parent is

table.current(1)

the child is

table.current(2)