Copy Last Years Sales

[forskare]

Re: Function - How many reasons....

please try this i am sure it will work for you
just make sure that estabdate contains date value

dim curYr as C
curYr = year(convert_type(estabdate,"D"))

Hi Ahmed,

This does not work. It throws the error message that the variable, "estabdate" is not found.

Thanks,

[forskare]

Re: Function - How many reasons....

Hi Robin,

I copied the script in it's entirety. First time through, it said there was a missing End While command. I added it. 2nd time through, it didn't like the line: on error goto ERRTRAP. I commented it out. The 3rd time through, The system cannot find the file specified: TLC_3copy.DBF. (I left off the full path) Grrrrrrr.

[GGandhi]

Re: Function - How many reasons....

'Date Created: 16-Mar-2015 02:20:06 PM
'Last Updated: 21-Mar-2015 06:16:20 PM
'Created By  : kenn
'Updated By  : Govindan

debug(1)


dim tbl_a as p
dim tbl_b as p

tbl_a = table.open("tlc_3apps")
tbl_b = table.open("tlc_3apps")
dim curYr as n
curYr = year(date())
tbl_a.fetch_first()

while .not. tbl_a.fetch_eof()
	if year(tbl_a.estabdate) = curYr-1
		tbl_b.enter_begin(.t.)
		tbl_b.Customerid = tbl_a.Customerid
		tbl_b.Svcsiteid  = tbl_a.Svcsiteid
		tbl_b.Service  = tbl_a.Service
		tbl_b.Svc_Descrip = tbl_a.Svc_Descrip
		tbl_b.Svc_Zip  = tbl_a.Svc_Zip
		tbl_b.Sale  = tbl_a.Sale
		tbl_b.Exempt = tbl_a.Exempt
		tbl_b.Comborate  = tbl_a.Comborate
		tbl_b.Discount  = tbl_a.Discount
		tbl_b.Disc_Note = tbl_a.Disc_Note
		tbl_b.enter_end(.t.)
	end if
		tbl_a.fetch_next()
	
end while

tbl_b.close()
tbl_a.close()

here is the updated code for you using your tables and it works here.
see if it does for you.
you had many mistakes. code was running around everywhere but not doing anything.

edit: see this screencast.
http://screencast.com/t/lqS9DKuWi9K

[MoGrace]

Re: Function - How many reasons....

Ken,
Did you duplicate the table as I did? The script as I posted would not have worked otherwise. Which is no doubt what the errors were about. I don't see the point in copying the records to the same table - is there one?

[madtowng]

Re: Function - How many reasons....

Hi Ken,

I won't try to guess what you're doing, but the problem here is really just a matter of timing.
You are trying to use the value for tbl_a.estabdate before you open the table.
If you move the curYr = year(tbl_a.estabdate) statement to a position after you open the table, you will find that error is no longer a problem.

I see other issues like GGhandi did, but I believe you will pose those questions later.
I have attached a couple images to help illustrate.

tbl_a_isnotopenyet.PNG
tbl_a_isnowopen.PNG

[forskare]

Re: Function - How many reasons....

Hi Robin,

The customer purchases from year to year are, with few exceptions, the same. Thus, copying certain data from the previous year to the current year, saves a huge amount of data entry.

[madtowng]

Re: Copy Last Years Sales

Hi Ken,

I cheat a lot. It looks like you want an operation to Copy last years sales into a 2nd table.
The code shown below can be pasted into a script, that can be attached to button on a DT form.

Not sure if I'm late to the party, but I think this answers the initial question.

'In case the destination table name is stored as a relative file name, or uses an alias
'in the drive/path specification, use the filename_decode() function to convert the
'filename to an absolute filename.
copy_db_name = filename_decode("\users\ken\desktop\tlc 3-19-15\last_year_sales.dbf")

a_tbl = table.open("tlc_3apps")
ON ERROR GOTO ERROR21032015224246376


DIM a5_operation_order as C
a5_operation_order = "recno()"
query.order = a5_operation_order
DIM a5_operation_filter as C
a5_operation_filter = "year(Estabdate)=year(date())-1"
query.filter = a5_operation_filter
query.options = "I"
query.description = "Temporary Query"
i_indx = a_tbl.query_create()

copy.set = ""
copy.db = copy_db_name
copy.dd = .F.
copy.delete_o_dd = .F.
copy.records = .T.
copy.fields = 10
copy.field1 = "Customerid"
copy.field2 = "Svcsiteid"
copy.field3 = "Service"
copy.field4 = "Svc_Descrip"
copy.field5 = "Svc_Zip"
copy.field6 = "Sale"
copy.field7 = "Exempt"
copy.field8 = "Comborate"
copy.field9 = "Discount"
copy.field10 = "Disc_Note"

a_tbl.copy()


GOTO CONTINUE21032015224246376
ERROR21032015224246376:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Copy Operation"+crlf()+error_text_get())
END
CONTINUE21032015224246376:
a_tbl.close()
'Add the table just created by the Copy operation to the Database..
file_add_to_db(copy_db_name)

[GGandhi]

Re: Function - How many reasons....

did the code in post #16 work for you at all?

[forskare]

Re: Function - How many reasons....

did the code in post #16 work for you at all?

Yes, it works with just the table that I sent to you. However, it doesn't work with the app. I'm trying to isolate the problem but as you know, that can be a tedious task. I will you you informed.

[forskare]

Re: Copy Last Years Sales

Hi Greg,

I need to copy last years sales to the same table. My customer's customers are repeat customers which order the same services every year. the ability to batch copy all services/sales from the last year to the current year saves a huge amount of data entry time. That said, your code will have it's place because we will only want to keep only so many years in the current table with the other years to be archived. Just how may years to keep current hasn't been determined. So no, you're not late to the party. Thanks for your input.

[madtowng]

Re: Copy Last Years Sales

Ken,

It's easy enough to import last year's sales back into the primary table.

The code below does it, although I don't have it checking for duplicate records.


Gregg

'In case the destination table name is stored as a relative file name, or uses an alias
'in the drive/path specification, use the filename_decode() function to convert the
'filename to an absolute filename.
copy_db_name = filename_decode("\users\ken\desktop\tlc 3-19-15\last_year_sales.dbf")

a_tbl = table.open("tlc_3apps")
ON ERROR GOTO ERROR21032015224246376


DIM a5_operation_order as C
a5_operation_order = "recno()"
query.order = a5_operation_order
DIM a5_operation_filter as C
a5_operation_filter = "year(Estabdate)=year(date())-1"
query.filter = a5_operation_filter
query.options = "I"
query.description = "Temporary Query"
i_indx = a_tbl.query_create()

copy.set = ""
copy.db = copy_db_name
copy.dd = .F.
copy.delete_o_dd = .F.
copy.records = .T.
copy.fields = 10
copy.field1 = "Customerid"
copy.field2 = "Svcsiteid"
copy.field3 = "Service"
copy.field4 = "Svc_Descrip"
copy.field5 = "Svc_Zip"
copy.field6 = "Sale"
copy.field7 = "Exempt"
copy.field8 = "Comborate"
copy.field9 = "Discount"
copy.field10 = "Disc_Note"

a_tbl.copy()


GOTO CONTINUE21032015224246376
ERROR21032015224246376:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Copy Operation"+crlf()+error_text_get())
END
CONTINUE21032015224246376:
a_tbl.close()
'Add the table just created by the Copy operation to the Database..
file_add_to_db(copy_db_name)


' Now import least year's sales back into the main table

DIM Append as P

a_tbl = table.open("tlc_3apps")
append.t_db = "last_year_sales"
ON ERROR GOTO ERROR2203201511448739


append.m_key = ""
append.t_key = ""
append.m_filter = ""
append.t_filter = ""
append.type = "All"
append.m_count = 10
append.m_field1 = "CUSTOMERID"
append.m_exp1 = "@LAST_YEAR_SALES->CUSTOMERID"
append.m_field2 = "SVCSITEID"
append.m_exp2 = "@LAST_YEAR_SALES->SVCSITEID"
append.m_field3 = "SERVICE"
append.m_exp3 = "@LAST_YEAR_SALES->SERVICE"
append.m_field4 = "SVC_DESCRIP"
append.m_exp4 = "@LAST_YEAR_SALES->SVC_DESCRIP"
append.m_field5 = "SVC_ZIP"
append.m_exp5 = "@LAST_YEAR_SALES->SVC_ZIP"
append.m_field6 = "SALE"
append.m_exp6 = "@LAST_YEAR_SALES->SALE"
append.m_field7 = "EXEMPT"
append.m_exp7 = "@LAST_YEAR_SALES->EXEMPT"
append.m_field8 = "COMBORATE"
append.m_exp8 = "@LAST_YEAR_SALES->COMBORATE"
append.m_field9 = "DISCOUNT"
append.m_exp9 = "@LAST_YEAR_SALES->DISCOUNT"
append.m_field10 = "DISC_NOTE"
append.m_exp10 = "@LAST_YEAR_SALES->DISC_NOTE"
append.t_count = 0

a_tbl.append()


GOTO CONTINUE2203201511448739
ERROR2203201511448739:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Append Operation"+crlf()+error_text_get())
END
CONTINUE2203201511448739:
a_tbl.close()
'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

[Al Buchholz]

Re: Copy Last Years Sales

Kenn

Looks like 2 threads on the same subject.

http://www.alphasoftware.com/alphafo...w-many-reasons

Yes No Maybe?

If Yes, I'll merge them. It's getting confusing to follow..

[forskare]

Re: Copy Last Years Sales

Hello Al,

I started this one for the copy. As I went along, I started the other one to try to resolve the error message. You may merge them, That's OK with me.

[MoGrace]

Re: Function - How many reasons....

N/t

[forskare]

Re: Function - How many reasons....

A huge thank you to all who made suggestions and provided guidance for accomplishing this task. I am very, very much appreciative of each person's contribution. This is a recap of the task at hand and the conclusion of the effort.

My need: My customer has 5,000 customers. Their customers remain pretty steady year after year and order the same service/s year after year, anywhere from 1 to as many as 10 services.Thus, entering all the sales data for several thousand records consumes a lot of time, a lot of time!! This code looks at a date field which is displayed as the year only, subtracts 1 year and then copies all the sales for that year to the same table so the customer. Not every field in the record is copied but only the fields where the data remains the same. So, with a 1 button push, all of the pertinent data for each sale of for each customer are copied, thereby avoiding several hundred hours of repetitive data entry each year. In reality, it saves the expense of an additional staff person.

This is the code. You will need to use your table names and field names. You can use as many fields as you want/need. Hopefull, this may be of held to others.

'debug(1)


dim tbl_a as p
dim tbl_b as p

tbl_a = table.open("yourtablename")
tbl_b = table.open("yourtablename")

dim curYr as n
curYr = year(date())

tbl_a.fetch_first()

while .not. tbl_a.fetch_eof()

if year(tbl_a.yourdatefield) = curYr-1
tbl_b.enter_begin(.t.)
tbl_b.Yourfieldname = tbl_a.Yourfieldname
tbl_b.Yourfieldname = tbl_a.Yourfieldname
tbl_b.Yourfieldname = tbl_a.Yourfieldname
tbl_b.Yourfieldname = tbl_a.Yourfieldname
tbl_b.Yourfieldname = tbl_a.Yourfieldname
tbl_b.Yourfieldname = tbl_a.Yourfieldname
tbl_b.Yourfieldname = tbl_a.Yourfieldname
tbl_b.Yourfieldname = tbl_a.Yourfieldname
tbl_b.Yourfieldname = tbl_a.Yourfieldname
tbl_b.Yourfieldname = tbl_a.Yourfieldname
tbl_b.enter_end(.t.)
end if
tbl_a.fetch_next()

end while

tbl_b.close()
tbl_a.close()

msgbox("Result", "All the relevant data of last years sales was copied as new record.")