Can update transaction table field during append

[Greg Fong]

I can't seem to update a transaction table field during an append operation.
I have a button on a form which transfers a selection of records from a sales_order set to an invoice set. The user selects the items he wants to invoice by setting the INVOICE_FLG field to .T. and runs the procedure.
Once the transfer is completed the append procedure is supposed to go back to the transaction table and stuff the current date into the INV_SENT field. But for some reason I get the following message

"ERROR
Script:OnPush line:86
tbl.append()
Variable append.t_filter expression-argument is incorrect data type"


THE FOLLOWING IS MY LITTLE PROCEDURE

dim shared vinvoice as n
dim shared vsales_order as n
dim shared vdate as d
dim shared tbl as p
dim shared t_db as p

'set system date to variable
vdate=date()

'set the current sales order number in the sales order form to variable
vsales_order=:sales_order:Sales_Ord.value

BROWSE2.Activate()

BROWSE2.new_record()

'sets current date value to invoice table
:sales_order:browse2:inv_date.value=vdate

'sets new invoice number to variable
vinvoice=:sales_order:browse2:invoice.value

BROWSE2.commit()
tbl=table.get("invoice_detail")

append.t_db = "sales_ord_detail.dbf"
append.m_key = "str(ITEM_NUM,16,0)"
append.t_key = "str(ITEM_NUM,16,0)"
append.m_filter = ""
append.t_filter = "SALES_ORD= VAR->vsales_order.AND.INVOICE_FLG=.T..AND.ISBLANK(Inv_Sent)"
append.type = "Unique, replace existing"
append.m_count = 12
append.m_case1 = "EITHER"
append.m_field1 = "Sales_Ord"
append.m_exp1 = "@SALES_ORD_DETAIL->SALES_ORD"
append.m_case2 = "EITHER"
append.m_field2 = "Item_Num"
append.m_exp2 = "@SALES_ORD_DETAIL->ITEM_NUM"
append.m_case3 = "EITHER"
append.m_field3 = "Partnumber"
append.m_exp3 = "@SALES_ORD_DETAIL->PARTNUMBER"
append.m_case4 = "EITHER"
append.m_field4 = "Partnum"
append.m_exp4 = "@SALES_ORD_DETAIL->PARTNUM"
append.m_case5 = "EITHER"
append.m_field5 = "Partname"
append.m_exp5 = "@SALES_ORD_DETAIL->PARTNAME"
append.m_case6 = "EITHER"
append.m_field6 = "Quantity"
append.m_exp6 = "@SALES_ORD_DETAIL->QUANTITY"
append.m_case7 = "EITHER"
append.m_field7 = "Price"
append.m_exp7 = "@SALES_ORD_DETAIL->PRICE"
append.m_case8 = "EITHER"
append.m_field8 = "Creditcharge"
append.m_exp8 = "@SALES_ORD_DETAIL->CREDITCHARGE"
append.m_case9 = "EITHER"
append.m_field9 = "Status"
append.m_exp9 = "@SALES_ORD_DETAIL->STATUS"
append.m_case10 = "EITHER"
append.m_field10 = "Back"
append.m_exp10 = "@SALES_ORD_DETAIL->BACK"
append.m_case11 = "EITHER"
append.m_field11 = "Comment"
append.m_exp11 = "@SALES_ORD_DETAIL->COMMENT"
append.m_case12 = "EITHER"
append.m_field12 = "Taxable"
append.m_exp12 = "@SALES_ORD_DETAIL->TAXABLE"

'TRANSACTION TABLE UPDATE SECTION
append.t_count = 1
append.t_case1 = "APPENDED"
append.t_field1 = "@Sales_Ord_Detail->Inv_Sent"

'PUTS DATE INTO TRANSACTION TABLE Why won't this work?
append.t_exp1 = "var->vdate"

tbl.append()

I think I've tried every possible combination to get the append.t_exp1 to accept the date value. Please, please,please help

[Tom Cone Jr]

RE: Can update transaction table field during appe

Greg,

your filter expression uses the isblank() function. This function takes a character string argument.


isblank(fieldname) won't work

isblank('fieldname') should work



By the way, I and others have had mixed results using isblank() with date type fields. If Inv_Sent is a date field then you might want to express the filter differently. This has been discussed in several recent message threads.

-- tom

[Greg Fong]

RE: Can update transaction table field during appe

Thanks so much for the help, I could have still been at this all morning.
I can't believe that I blew away 4 hours on the wrong part of my code when the error message quite clearly pointed to the general area of the problem (my filter). But then the error code also pointed me to the wrong line why is that?

Do you know of a thread that covers when to use and not to use commas of both types? I know that it is one of those DUHHHH? problems that crop up all the time but perhaps someone can sort of summarise a diagnostic approach to solving problems of getting values from one place and putting it somewhere else. IT might be a great tool to put into the new Hints section.

With regard to the field type, you are right it is a date field but I can't think of another way to filter for a blank date field.

Regards and thanks once again

[Tom Cone Jr]

RE: Can update transaction table field during appe

Try

if cdate(inv_sent) = ""


or


if len(cdate(inv_sent) = 0


or


inv_sent = {}

[Tom Cone Jr]

RE: Can update transaction table field during appe

Concerning the line number, the error probably isn't picked up until Alpha Five tries to evaluate the filter expression. That probably doesn't happen until the end of the script when you actually run the append()

-- tom