Copy Last Years Sales

[Al Buchholz]

Re: Copy Last Years Sales

For those following along at home this can also be done with an append operation and improve the performance.

Yes a table can append to itself.

[GGandhi]

Re: Copy Last Years Sales

if year(tbl_a.yourdatefield) = curYr-2

is this correct?

[forskare]

Re: Copy Last Years Sales

is this correct?

Yes, NO, Yes, Errrr, No! I changed it to '2' because there were no 2014 records, only 2013. I changed the script above to '1'. Thanks for pointing this out.

[forskare]

Re: Copy Last Years Sales

For those following along at home this can also be done with an append operation and improve the performance.

Yes a table can append to itself.

How is this done? There is no way to select the fields. I seems to want to append the entire table. Even if there was a way to select the fields, it would need the ability to select a filter as well.

[Al Buchholz]

Re: Copy Last Years Sales

How is this done? There is no way to select the fields. I seems to want to append the entire table. Even if there was a way to select the fields, it would need the ability to select a filter as well.

Yes the append auto fills the fields that match.

You can delete the lines that are not needed.

There is a select records option.

Working up an example now.

[Al Buchholz]

Re: Function - How many reasons....

tlc_3apps_set.zip

unzip this set and add to your database.

There is an operation for the set that appends only the fields you've shown in your code and has a select on it.

The set has one table in it, the tlc-3apps table, and the set was used to ease the installation of this into an existing database.

kenn1.JPG

To remove the fields you don't want to append, highlight the fields you don't want to append and delete them from the append list.

You can also delete the Estabdate field from the list

The select records is shown and could/should be changed to be a dialog prior to running the operation. But I'm trying to get you rolling on the right track.

An append operation is faster because it runs a C++ routine to do the work rather than xbasic.

See notes from Ira about C++ vs xbasic

[madtowng]

Re: Copy Last Years Sales

Ken,

If you look for the line with "' Now import least year's sales back into the main table" in post #56,
You'll find append code underneath it.

I never considered appending data to/from the same table in a single append operation, but I'm guessing
it shouldn't be too difficult to append fewer fields.

[Al Buchholz]

Re: Copy Last Years Sales

Ken,

If you look for the line with "' Now import least year's sales back into the main table" in post #56,
You'll find append code underneath it.

I never considered appending data to/from the same table in a single append operation, but I'm guessing
it shouldn't be too difficult to append fewer fields.

Gregg

Yes the append code is there but only seen if you scroll through it.

And the copy isn't needed to get the records initially.

Good point about the records might already be there and therefore duplicated. That option isn't handled in any of the solutions provided.

But the append would handle it if the option to append unique only is used. Of course you need to id what makes a record unique.

That's Kenn's job.

[madtowng]

Re: Copy Last Years Sales

Ok...a little reworking.
The code below now appends to/from the same table.
I added a bit to automatically use the last year in the table, as long as that year is less than the current year.

Special note to AL ... Thanks for the heads up of being able to append to the same table. That saves a lot of time once you figure out the filter :)

DIM Append as P

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

if tablemax("tlc_3apps","1=1","year(Estabdate)") >= year(date()) then
end
end if
yearcount = year(date()) - tablemax("tlc_3apps","1=1","year(Estabdate)")

append.m_key = ""
append.t_key = ""
append.m_filter = ""
append.t_filter = "year(Estabdate) = year(date())-yearcount"
append.type = "All"
append.m_count = 10
append.m_field1 = "CUSTOMERID"
append.m_exp1 = "@TLC_3APPS->CUSTOMERID"
append.m_field2 = "SVCSITEID"
append.m_exp2 = "@TLC_3APPS->SVCSITEID"
append.m_field3 = "Service"
append.m_exp3 = "@TLC_3APPS->Service"
append.m_field4 = "Svc_Descrip"
append.m_exp4 = "@TLC_3APPS->Svc_Descrip"
append.m_field5 = "Svc_Zip"
append.m_exp5 = "@TLC_3APPS->Svc_Zip"
append.m_field6 = "Sale"
append.m_exp6 = "@TLC_3APPS->Sale"
append.m_field7 = "Exempt"
append.m_exp7 = "@TLC_3APPS->Exempt"
append.m_field8 = "Comborate"
append.m_exp8 = "@TLC_3APPS->Comborate"
append.m_field9 = "Discount"
append.m_exp9 = "@TLC_3APPS->Discount"
append.m_field10 = "Disc_Note"
append.m_exp10 = "@TLC_3APPS->Disc_Note"
append.t_count = 0

a_tbl.append()


GOTO CONTINUE23032015164914265
ERROR23032015164914265:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Append Operation"+crlf()+error_text_get())
END
CONTINUE23032015164914265:
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

[forskare]

Re: Copy Last Years Sales

Al,
That is way cool. It works great. How did you know to create a 1 table set (I wouldn't of had a clue) instead of using the table? What is significant about using the set vs the table. I see the way to gain access to the fields is to check the different field names radio 'o'.

Gregg,
Thanks for your code. You whip through that like a spoon through whipped cream. (Yum)

Al & Greg,

You guys have given me some new insights and something to play with for awhile.

Thanks sooooo much.

[Al Buchholz]

Re: Copy Last Years Sales

The set has one table in it, the tlc-3apps table, and the set was used to ease the installation of this into an existing database.

The use of a single table set has nothing to do with the operation. You can build this operation on the table.

A single use set has some advantages that include sending objects to another database with out being confused about which file to send without sending the data.

I've also learned to use a utility of set that allows you to point to another table. Right Click the set, Utilities, Repair Set. It works through the set definition table by table and allows you to point to another table or keep what you have.

Al,
That is way cool. It works great. How did you know to create a 1 table set (I wouldn't of had a clue) instead of using the table? What is significant about using the set vs the table.

I see the way to gain access to the fields is to check the different field names radio 'o'. (Please clarify - I'm confused about this.)

The single table set is a by-product of using a lot of passive linked tables that by their design rebuild the structure files and wipe out any objects in them. But the set is completely separate and is untouched.

I believe it was Selwyn who prompted their use.

To repeat (again), the single set is not needed for the append to work. But does make it easier to integrate into a database.

I would do as Gregg has shown and use the code to build a function along with some other options to avoid duplication of data.

[forskare]

Re: Copy Last Years Sales

Hi Al,

<quote>I see the way to gain access to the fields is to check the different field names radio 'o'. (Please clarify - I'm confused about this.)</quote>

When creating the operation, there is the option, fields have or do not have the same names. If the same name option is selected, it bypasses the field matching step. In order to select certain fields, as opposed to all fields, the fields have different names option must be selected.

After giving this process some thought, I decided to try a three operation process. First, I duplicated the table and then removed all the data from the new table. Then I created three (3) operations.

1. Append all records from Table A to the new table, Table B.
2. delete all records in Table A
3. Append/copy the data in selected fields from Table B to Table A, using the ask var to select the records for the correct year.

I created 3 buttons on a form, one for each operation. This works great. Now, I need to try putting all 3 operations on one button push. I'm not sure if this will work because the operation is complete a message box pops up which requires user action. If it does work that way, that's OK. The advantage is still using one button instead of 3.

[Al Buchholz]

Re: Copy Last Years Sales

<quote>I see the way to gain access to the fields is to check the different field names radio 'o'. (Please clarify - I'm confused about this.)</quote>

When creating the operation, there is the option, fields have or do not have the same names. If the same name option is selected, it bypasses the field matching step. In order to select certain fields, as opposed to all fields, the fields have different names option must be selected.

Reading between the lines, you are using the genie to create the operation. From the images I have shown, you can see I 'm not using the genie.

After giving this process some thought, I decided to try a three operation process. First, I duplicated the table and then removed all the data from the new table. Then I created three (3) operations.

1. Append all records from Table A to the new table, Table B.
2. delete all records in Table A
3. Append/copy the data in selected fields from Table B to Table A, using the ask var to select the records for the correct year.

I created 3 buttons on a form, one for each operation. This works great. Now, I need to try putting all 3 operations on one button push. I'm not sure if this will work because the operation is complete a message box pops up which requires user action. If it does work that way, that's OK. The advantage is still using one button instead of 3.

Explain in user terms what you are trying to accomplish.

Looks to me like you want the new records and not the old records that are there originally.

[forskare]

Re: Copy Last Years Sales

The table is for keeping track of customer services/sales. Each year, the customer has the option of signing up for the previous years services. Each of the 5,000 customers will purchase anywhere from 1 to 10 services. Obviously some data/fields are repetitive every year and some are not. The question: what is the best way to avoid repetitive data entry, saving a huge amount of staff time? Based on the above code, I am able to append the data from various fields to the same table and using your append operation, the processing time is cut from about 3.5 minutes to less than 30 seconds.

As the years go by, the amount of screen space it takes to display the customer's services increases and there is no need to show the past years services other than to view them on an as needed basis. Using the append to the same table every year will soon create this problem; too many records to view when not needed. The best option that I came up with is to append all records (every field) to a like table which will be basically, used as an archive table. Now the records in the Sales table, must be purged and then the append from the archived table to the Sales table will create the new records needed for the new year. This is a 3 step process.

As I was typing this, the thought occurred, instead of purging all records and then appending, it would be easier to simply remove the data from selected fields and change the Estabdate field value to reflect the new year. An arbitrary date could be used (say 1/1/15 for this year) or the Date() function could be used. The problem using the date function becomes, the year is the process is run. If in 2014, +1 will need to be included. If in 2015, then the +1 is not needed. Thus, an 'ask var' can be used to ask what year the new records are for.

Conclusion: I will work at purging the data from selected fields and changing the Estabdate field to reflect the new year. That will eliminate one part of the operation and make the overall operation smoother and quicker.

Hopefully, I've explained this sufficiently.

[Al Buchholz]

Re: Function - How many reasons....

That sounds like an append to the archive and then an update operation to change the date and to blank/zero out some fields.

easy-peasy

It's always good to write out the thoughts. It usually forces me to think clearly.

It's also the magic of the messageboard.....