How do you save and clear a yearly database records when using Runtime Plus?

[Patd]

Greetings,

When a client needs to save his or her yearly created records in the database, and then needs to clear that application for next year's usage, how is that done to set that up in Alpha Five V10.5 while the client is using the Runtime Plus with the application? I thank you for your help.

Regards,

Pat

[Clunes]

Re: How do you save and clear a yearly database records when using Runtime Plus?

Assumming the table has only a years records, I would create an action script attached to a menu item or button that:

1. Backs up the table to a zip file. (this can be restored if required)
2. Zaps the table concerned and compresses the table.

Given the potential danger of getting it wrong, I would recommend a proper backup of files before doing this as well

[MikeC]

Re: How do you save and clear a yearly database records when using Runtime Plus?

Pat,
How would you want it to be done?

So many various ways and all really depends on the above answer.

If you devise a way in the full development application, then whether runtime or not doesn't even enter into the equation unless it is a networked situation......


EDIT : am wondering if even a necessary thing to do---are the number of records expected to be so large that to have just a simple query for whatever year is wanted is going to take too long??

[Patd]

Re: How do you save and clear a yearly database records when using Runtime Plus?

Hi Mike,

I thank you for your response. The application would not be on a network just a workstation computer. I would like to be able to save the yearly records either as a "Save As" file or Zip file and then been able to clear the database for the new year so they could start over for the new year, each new year.

Could this be done if a person click's on a button? If yes, what is the code for this to work?

The amount of records is only up to 500 each year.

Again, thank you.

Regards,

Pat

Pat,
How would you want it to be done?

So many various ways and all really depends on the above answer.

If you devise a way in the full development application, then whether runtime or not doesn't even enter into the equation unless it is a networked situation......


EDIT : am wondering if even a necessary thing to do---are the number of records expected to be so large that to have just a simple query for whatever year is wanted is going to take too long??

[DaveM]

Re: How do you save and clear a yearly database records when using Runtime Plus?

Only a suggestion, but:

If records only total a few thousand, I would never clear the old records.

Properly written, a query as Mike suggested would never show the records other than the year that one would want to work with.

I have 12 years of data in my tables, but only see the date ranges I wish to see or all of it if I want.

Reports and other can be filtered/queried to a date range(current year?).

Not to my liking but, You could also save the whole app to another folder with a year designation and then zap the tables you have for further use by way of a little xbasic code.



.

[MikeC]

Re: How do you save and clear a yearly database records when using Runtime Plus?

If something you feel is necessary then the easiest would be as Mark indicated....use what Alpha gives you pretty much via Action Script (AS). One note though about the restore Mark mentioned....if this is something wanted, then for sure make the prior action to be a current backup so that once you have restored past data you can return to the current data (Mark also alluded to this).

[CALocklin]

Re: How do you save and clear a yearly database records when using Runtime Plus?

First, I concur with MikeC and DaveM - probably not even necessary. I, too, have a customer with 12 years or more of data that, AFTER zipping, totals to something like 28 meg of data (.dbf and .fpt files only) and they aren't having any problems.

IMPORTANT: Have you considered the fact that your customer's end-of-year may not be a clean end-of-year? Their customers probably don't care about when their end-of-year is. Unless it's a "ya gotta pay first" situation, their customers will pay up when they feel like it.

I do have one customer that insisted on clearing everything at the end of their fiscal year. It ended up being custom scripts and lots of "pre-clearing" work because customers don't tend to pay up precisely at the end of the fiscal year (if at all). We usually spend a total of about 8-12 man-hours to clean everything up before closing out the year.

And, yes, I did say "we" because they spend the first 8 hours or so getting everything as ready as they can then they call me in to help them finish. In reality, they could do it themselves but they just don't feel confident about it. I find that this attitude is fairly common - some customers just feel better if the "expert" does the "hard" stuff. (I don't think it's that hard but apparently they do.)

The basic steps are:
- Make sure all "orders" (they aren't really orders in my case but that's really not important) and payments are entered.
- Print all necessary end-of-year reports. (Some may also get done after the "write-off" step.)
- Make a backup in case something goes wrong in the next steps.
- Write off anything that you (the customer) decides will never get paid.
- Determine which customers' end of year balance data needs to get carried over to the next year and which customers are to be removed. Mark them accordingly. (Remember 0 is also a balance so this means all customers that will be carried over to the next year.)
- Make another backup. This is the official "end of year" backup.
- Remove all the "line item" data and double check to be sure all customers and balances are correct. (They don't have a lot of customers. Maybe 150.)

[DaveM]

Re: How do you save and clear a yearly database records when using Runtime Plus?

I do have one customer that insisted on clearing everything at the end of their fiscal year. It ended up being custom scripts and lots of "pre-clearing" work because customers don't tend to pay up precisely at the end of the fiscal year (if at all). We usually spend a total of about 8-12 man-hours to clean everything up before closing out the year.

In the type of business I deal with, It is very important to have an end of month and an end of year as well as end of fiscal year. How would one do this?

It is all done by way of date to date manipulation. If each main record has a date in it so it can be drawn into any report needed thgat depends on a date.

I have to track when sales are made, disbursements(like sales tax, vendor payments) are made, payments are taken. each category has to be exact and within a time period.

There is no way to cleanly start over that would be feasible.

Learn to set up queries, filters and so forth and life will be a lot easier for you and your user.

About 20 years ago, I set up a payroll program in alpha DOS that was renewable every year. I have never had to touch it and it is still working today. The users put in the new tax rates each year and go on with life. All the data for 20 years is still there. They rarely see anything except the current payroll period or year. That one, I did good. If only all of them came out like that!

.

[RDOUGHTY]

Re: How do you save and clear a yearly database records when using Runtime Plus?

i don't have the experience of the heavy hitters here, but i agree and use the system of filters on a database that's been running since 1996, its easier and much safer, I'm currently redoing my entire app in v10.5 and since it will be completely new design, my idea was to simply move the folders to a new sub folder "archive/application/" and then put all those db's in a read only status, whole process takes seconds and only requires basic under standing of windows, I'm pretty sure it could be scripted and built in your app. this would allow user if needed to go back and update or carry to new year any information using copy/paste.

[DaveM]

Re: How do you save and clear a yearly database records when using Runtime Plus?

I just posted this in another thread. It will give an idea how to accomplish. Sorry for the length, but that way it is whole and one can see how it works.

It is a bunch of queries, etc in one button.

dim dopath as c
dopath = a5.Get_Exe_Path()
vmenuitems = <<%a%
Active Customers
Pending Customers
Sold Customers
Sold Customers by Last Name
Sold Customers By Last Name with Good Address
Sold Businesses
Ordered Vehicles
Dead Deals
Special Sorting
Find Text in field
%a%
a_command = ui_popup_menu(vmenuitems)
select case
'=====================================================================================================
case a_command = "Active Customers"
 dim shared numbs as n
 numbs = A5_get_records_in_query("names","scode=var->scode.and.status='A'")
 if numbs <= 0 then
  ui_msg_box("None Found","There are no Active Customers Found")
  goto end
 end if 
 tbl=table.current()
 query.description = "Active"
 query.order = "date"
 query.filter = "scode=var->scode.and.status='A'"
 query.options = "N"
 tbl.query_create()
 parent.fetch_last()
 topparent.Resynch()
 topparent.Refresh_Layout()
'=====================================================================================================
case a_command = "Sold Customers"
 dim shared numbs as n
 numbs = A5_get_records_in_query("names","scode=var->scode.and.status='S'")
 if numbs <= 0 then
  ui_msg_box("None Found","There are no Sold Customers Found")
  goto end
 end if 
 tbl=table.current()
 query.description = "Sold"
 query.order = "date"
 query.filter = "scode=var->scode.and.status='S'"
 query.options = "N"
 tbl.query_create()
 parent.fetch_last()
 topparent.Resynch()
 topparent.Refresh_Layout()
  
'=====================================================================================================
case a_command = "Sold Customers By Last Name with Good Address"
 dim shared numbs as n
 numbs = A5_get_records_in_query("names","scode=var->scode.and.status='S'")
 if numbs <= 0 then
  ui_msg_box("None Found","There are no Sold Customers Found")
  goto end
 end if 
 tbl=table.current()
 query.description = "Sold Customers"
 query.order = "buyer+fname"
 query.filter = "scode=var->scode.and.status='S'.and.nomail<>'N'"
 query.options = "N"
 tbl.query_create()
 parent.fetch_first()
 topparent.Resynch()
 topparent.Refresh_Layout()
'=====================================================================================================
case a_command = "Sold Customers by Last Name"
 dim shared numbs as n
 numbs = A5_get_records_in_query("names","scode=var->scode.and.status='S'")
 if numbs <= 0 then
  ui_msg_box("None Found","There are no Sold Customers Found")
  goto end
 end if 
 tbl=table.current()
 query.description = "Sold Customers"
 query.order = "buyer"
 query.filter = "scode=var->scode.and.status='S'"
 query.options = "N"
 tbl.query_create()
 parent.fetch_first()
 topparent.Resynch()
 topparent.Refresh_Layout()
'=====================================================================================================
case a_command = "Sold Businesses"
 dim shared numbs as n
 numbs = A5_get_records_in_query("names","scode=var->scode.and.status='S'")
 if numbs <= 0 then
  ui_msg_box("None Found","There are no Sold Business Units Found")
  goto end
 end if 
 tbl=table.current()
 query.description = "By Business"
 query.order = "fname+buyer"
 query.filter = "scode=var->scode.and.status='S'"
 query.options = "N"
 tbl.query_create()
 topparent.Resynch()
 topparent.Refresh_Layout()
'=====================================================================================================
case a_command = "Ordered Vehicles"
 dim shared numbs as n
 numbs = A5_get_records_in_query("names","scode=var->scode.and.status='O'")
 if numbs <= 0 then
  ui_msg_box("None Found","There are no Order Customers Found")
  goto end
 end if 
 tbl=table.current()
 query.description = "Order Customers"
 query.order = "acct"
 query.filter = "scode=var->scode.and.status='O'"
 query.options = "N"
 tbl.query_create()
 parent.fetch_last()
 topparent.Resynch()
 topparent.Refresh_Layout()
'=====================================================================================================
case a_command = "Dead Deals"
 dim shared numbs as n
 numbs = A5_get_records_in_query("names","scode=var->scode.and.status='D'")
 if numbs <= 0 then
  ui_msg_box("None Found","There are no Dead Deals Found")
  goto end
 end if 
 tbl=table.current()
 query.description = "Dead Deals"
 query.order = "date"
 query.filter = "scode=var->scode.and.status='D'"
 query.options = "N"
 tbl.query_create()
 parent.fetch_last()
 topparent.Resynch()
 topparent.Refresh_Layout()
'=====================================================================================================
case a_command = "Special Sorting"
 'Custom Sort dialog in current form at parent level.
 topparent.QuickSortMultiFld()
'=====================================================================================================
Case a_command = "Find Text in field"
 dim pObj as p 
 pObj = topparent.this 
 dim TextToLocate as c 
 TextToLocate = ui_get_text("Locate What","Select text to locate")
 if TextToLocate = "" then 
   end 
 end if 
 if TextToLocate = "" then 
  ui_msg_box("Error","You must specify non-blank text to Locate.",UI_STOP_SYMBOL)
 end
 end if 
 dim tbl as p 
 tbl = pObj.table_get()
 dim locateFlag as l 
 locateFlag = tbl.fetch_loc_next(TextToLocate)
 if locateFlag = .t. then 
  pObj.resynch()
 else
  ui_msg_box("Note","Text not found.",UI_INFORMATION_SYMBOL)  
 end if 
'=====================================================================================================
case a_command = "Pending Customers"
 dim shared numbs as n
 numbs = A5_get_records_in_query("names","scode=var->scode.and.status='P'")
 if numbs <= 0 then
  ui_msg_box("None Found","There are no Pending Sales Found")
  goto end
 end if 
 tbl=table.current()
 query.description = "Pending"
 query.order = "date"
 query.filter = "scode=var->scode.and.status='P'"
 query.options = "N"
 tbl.query_create()
 parent.fetch_last()
 topparent.Resynch()
 topparent.Refresh_Layout()
'=====================================================================================================
 
end:
end select
'%code%

Modify it or use it as a guide to make your own?

Hope it helps.

.

[Patd]

Re: How do you save and clear a yearly database records when using Runtime Plus?

Hi Mark,

I thank you for your helpful suggestion as it worked out for me. Again, thank you.

Regards,

Pat

Assumming the table has only a years records, I would create an action script attached to a menu item or button that:

1. Backs up the table to a zip file. (this can be restored if required)
2. Zaps the table concerned and compresses the table.

Given the potential danger of getting it wrong, I would recommend a proper backup of files before doing this as well

[Patd]

Re: How do you save and clear a yearly database records when using Runtime Plus?

Greetings,

I thank you all for your helpful suggestions. Again, thank you and enjoy!

Regards,

Pat