Re: Copy Last Years Sales
I had a script to blank out the fields. After changing it to the appropriate fields, I it a try. Fixed a couple errors and tried again. It took only 6 seconds to blank out or purge the data from the fields in 47,000 records. One thing I thought of is to also include the SALEID field as well as these are to be new records. Actually, this table probably doesn't need a SALEID field. All data in this table is linked to the parent and child tables by CUSTOMERID and SVCSITEID. Any reports where data from this table only, can be based on the record number.
AS a LOL note. I have no memory of where this script came from. It is much to complicated for me to have written it. Someone must have written it for me some time ago because the table name was a bit different. the more I think about this, I must have created it with action scripting because the creator's name is my computer name. This is the script:
'Operation is based on current filter and order of current table.
'Get the current table's filter and order expression
dim mru as p
mru = mru_query("tlc_3apps")
current_filter = mru.filter
current_order = mru.order
current_flags = mru.flags
'Check to see if current filter/order is valid in the context of "tlc_3apps"
if current_filter <> "" then
if eval_valid(current_filter,"tlc_3apps") =.f. then
a5_Operation_Warning() 'Display warning if current selection is meaningless
current_filter = ".t."
end if
end if
if current_order <> "" then
if eval_valid(current_order,"tlc_3apps") = .f. then
current_order = ""
end if
end if
a_tbl = table.open("tlc_3apps")
ON ERROR GOTO ERROR2601201507123682
DIM a5_operation_filter as C
a5_operation_filter = current_filter
query.filter = a5_operation_filter
DIM a5_operation_order as C
a5_operation_order = current_order
query.order = a5_operation_order
query.options = "I"+current_flags
query.description = "Temporary Query"
a_tbl.query_create("N")
update.fields = 10
update.field1 = "Applydate"
update.expr1= "NULL_VALUE()"
update.field2 = "TECH"
update.expr2 = "NULL_VALUE()"
update.field3 = "TECHLICNO"
update.expr3 = "NULL_VALUE()"
update.field4 = "TECHINTLS"
update.expr4 = "NULL_VALUE()"
update.field5 = "DATEPAID"
update.expr5 = "NULL_VALUE()"
update.field6 = "CHK"
update.expr6 = "NULL_VALUE()"
update.field7 = "PAID"
update.expr7 = "NULL_VALUE()"
update.field8 = "PAIDINT"
update.expr8 = "NULL_VALUE()"
update.field9 = "BALDUE"
update.expr9 = "NULL_VALUE()"
update.field10 = "SALEID"
update.expr10 = "NULL_VALUE()"
'update.field11 = "YEAR_CHG"
'update.expr11 = "NULL_VALUE()"
'update.field12 = "PPD"
'update.expr12 = "NULL_VALUE()"
'Prompt for confirmation before running the Operation.......
dim rec_count as n
rec_count = a5_get_records_in_query("tlc_3apps",a5_operation_filter,-1,.f.)
message_text = rec_count + " record(s) from 'tlc_3apps' will be updated."+crlf(2)+ "OK to proceed?"
operation_result=ui_msg_box("Update Operation",message_text,UI_OK_CANCEL+ UI_FIRST_BUTTON_DEFAULT+ UI_INFORMATION_SYMBOL)
If operation_result <> ui_ok_selected then
end
end if
a_tbl.update()
GOTO CONTINUE2601201507123682
ERROR2601201507123682:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Update Operation"+crlf()+error_text_get())
END
CONTINUE2601201507123682:
a_tbl.close()
'Display a dialog box showing the results of the Operation
'(If last parameter is blank, button to show result is not displayed).
dim args as sql::arguments
if eval_valid("arguments") then
if typeof(arguments) = "P" then
args = arguments
end if
end if
a5_update_op_result(a_records_processed, a_records_violated,"tlc_3apps",a5_operation_filter,args)
'If the Operation is run from within a Form or Browse, then refresh the window
if is_object(topparent.this) then
if topparent.Class() = "form" .or. topparent.class() = "browse" then
topparent.Refresh_layout()
end if
end if
I had a script to blank out the fields. After changing it to the appropriate fields, I it a try. Fixed a couple errors and tried again. It took only 6 seconds to blank out or purge the data from the fields in 47,000 records. One thing I thought of is to also include the SALEID field as well as these are to be new records. Actually, this table probably doesn't need a SALEID field. All data in this table is linked to the parent and child tables by CUSTOMERID and SVCSITEID. Any reports where data from this table only, can be based on the record number.
AS a LOL note. I have no memory of where this script came from. It is much to complicated for me to have written it. Someone must have written it for me some time ago because the table name was a bit different. the more I think about this, I must have created it with action scripting because the creator's name is my computer name. This is the script:
'Operation is based on current filter and order of current table.
'Get the current table's filter and order expression
dim mru as p
mru = mru_query("tlc_3apps")
current_filter = mru.filter
current_order = mru.order
current_flags = mru.flags
'Check to see if current filter/order is valid in the context of "tlc_3apps"
if current_filter <> "" then
if eval_valid(current_filter,"tlc_3apps") =.f. then
a5_Operation_Warning() 'Display warning if current selection is meaningless
current_filter = ".t."
end if
end if
if current_order <> "" then
if eval_valid(current_order,"tlc_3apps") = .f. then
current_order = ""
end if
end if
a_tbl = table.open("tlc_3apps")
ON ERROR GOTO ERROR2601201507123682
DIM a5_operation_filter as C
a5_operation_filter = current_filter
query.filter = a5_operation_filter
DIM a5_operation_order as C
a5_operation_order = current_order
query.order = a5_operation_order
query.options = "I"+current_flags
query.description = "Temporary Query"
a_tbl.query_create("N")
update.fields = 10
update.field1 = "Applydate"
update.expr1= "NULL_VALUE()"
update.field2 = "TECH"
update.expr2 = "NULL_VALUE()"
update.field3 = "TECHLICNO"
update.expr3 = "NULL_VALUE()"
update.field4 = "TECHINTLS"
update.expr4 = "NULL_VALUE()"
update.field5 = "DATEPAID"
update.expr5 = "NULL_VALUE()"
update.field6 = "CHK"
update.expr6 = "NULL_VALUE()"
update.field7 = "PAID"
update.expr7 = "NULL_VALUE()"
update.field8 = "PAIDINT"
update.expr8 = "NULL_VALUE()"
update.field9 = "BALDUE"
update.expr9 = "NULL_VALUE()"
update.field10 = "SALEID"
update.expr10 = "NULL_VALUE()"
'update.field11 = "YEAR_CHG"
'update.expr11 = "NULL_VALUE()"
'update.field12 = "PPD"
'update.expr12 = "NULL_VALUE()"
'Prompt for confirmation before running the Operation.......
dim rec_count as n
rec_count = a5_get_records_in_query("tlc_3apps",a5_operation_filter,-1,.f.)
message_text = rec_count + " record(s) from 'tlc_3apps' will be updated."+crlf(2)+ "OK to proceed?"
operation_result=ui_msg_box("Update Operation",message_text,UI_OK_CANCEL+ UI_FIRST_BUTTON_DEFAULT+ UI_INFORMATION_SYMBOL)
If operation_result <> ui_ok_selected then
end
end if
a_tbl.update()
GOTO CONTINUE2601201507123682
ERROR2601201507123682:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Update Operation"+crlf()+error_text_get())
END
CONTINUE2601201507123682:
a_tbl.close()
'Display a dialog box showing the results of the Operation
'(If last parameter is blank, button to show result is not displayed).
dim args as sql::arguments
if eval_valid("arguments") then
if typeof(arguments) = "P" then
args = arguments
end if
end if
a5_update_op_result(a_records_processed, a_records_violated,"tlc_3apps",a5_operation_filter,args)
'If the Operation is run from within a Form or Browse, then refresh the window
if is_object(topparent.this) then
if topparent.Class() = "form" .or. topparent.class() = "browse" then
topparent.Refresh_layout()
end if
end if
Comment