Multi Company Accounts

[sarper dayanik]

I have searched high and low for an answer without a satisfactory answer. I would like to keep data tables (same structure around 20 tables) in diferent folders (e.g. \Company1\data\, \Company2\data\, \Company3\data\, \Company4\data\ etc..).

When user starts the main application they will be presented with a list of companies they would like to work with. Is there a way I can set default data path to any of these folders mentioned above before starting any of the forms, reports etc. This will reduce alot of maintanance in terms of application side not so much about the data structure side.

This app will run on a multi environment

I am new to Alpha5v6 but quite good at Visual Dbase which borland does not support any more and it is a dodle to do this with borland's data engine.

Also is there any articals for ins and outs of installation procedures etc.

[Scott Emerick]

RE: Multi Company Accounts

Sarper,

Since A5 loads the tables and data dictionaries on start up, you need to have a method of switching data based on the company. However, like QuickBooks, a default data set is loaded on start up. If you want to prevent a default companies data from showing on start up, then you would need to create what I call a starter application which would supply the user with a menu that would in turn start the actual application for that company. Doing this would require a separate application for each company.

Now, if you take the data switching approach, you would only need one application and just repopulate your tables with the correct data. As long as all the tables for each company are identical, its just a matter of "Backing up the .dbf and .ftp files in a zip file in the companies directory(or even just the company name for the file in the same directory) and restoring the selected companies data files from their zip file.

I hope this gives you a starting point, since a step by step would take a long time to explain. Perhaps others will help you along the way if you need step by step help.

Scott

[Peter.Greulich]

RE: Multi Company Accounts

Unless there is some compelling reason to use different folders w. identical structures I would suggest you use one database only in one folder. Identify your companies by the appropriate query, etc. It may save you big headaches in the long run.

[Scott Emerick]

RE: Multi Company Accounts

I second Peter's comments.

My post was only answering your request of keeping separate tables.

[Jerry Gray]

RE: Multi Company Accounts

Sarper,

I have done some research on the same idea but have not tried to implement anything.

In the help files, the Using Addin Variables to Create a Multi-Database Application is one place to look.

The other which may work better for you deals with keeping all of your data in a single folder and shadowing the database to the correct "Company". Go to: Tutorials . Look for Shadow Table. There are 2 TurboDemo's which may help.

If you find a solution that works for you, you might share your findings.

Good Luck,
Jerry Gray

[mronck]

RE: Multi Company Accounts

There is a possibility to do so.
If, that is, you do not only keep the data into seperate directories but also the application itself.
You can make a small starter database where you store directory paths.
You can then, on several buttons, add those directory paths to the command "Start another database" which is available at action scripting.

Success !

Marcel Onck
Alpha Guild Member
The Netherlands

[Jerry Gray]

RE: Multi Company Accounts

Marcel, good to hear from you.

The idea is that you have multiple sets of data but only 1 set of code. I've attached a diagram which should explain it better than I can with words.

In another world, I would have used file equations to point to the appropriate set of data, i.e.

For Alpha Company:
File Invoice_Header.dbf = C:\MyApp\Alpha\Invoice_Header.dbf
File Invoice_Items.dbf = C:\MyApp\Alpha\Invoice_Items.dbf
etc
Run MyProgram.exe

For Beta Company:

File Invoice_Header.dbf = C:\MyApp\Beta\Invoice_Header.dbf
File Invoice_Items.dbf = C:\MyApp\Beta\Invoice_Items.dbf
etc
Run MyProgram.exe

I tried using the Alias function with the following entry:
[Invoice_Header.dbf]|c:\myapp\Invoice_Header.dbf Alpha continued to access the copy located in the folder with the application.

It is an old way of doing things, but it worked great. The last time I used this, I had 18 different sites for which I maintained data and only 1 set of code. This was not in a Windows environment.

Al Buchholz, you can chime in anytime :")

Jerry Gray

[sarper dayanik]

RE: Multi Company Accounts

Of course there is a compelling reason for not using queries and filter for all different companies and different years (e.g accounts application). I dont think it is a wise move, the way you have suggested. Secondly I don't think there is a solution for this in Alpha5v6 otherwise one of Alpha personel would have jumped in and answered this question.

[Peter.Greulich]

RE: Multi Company Accounts

Hhhmmm...

Officially, Alpha does not "monitor" this board. So your conclusion is incorrect.

You may be right - separate folders for separate companies. But if you ask me, that is a huge maintenance headache.

I don't know why you feel queries won't work. Sure they will. You just have to set it up properly w. a good db design. If you use the table query method, no one can accidentally (or deliberately) drop the query (unless you let them):

e.g.
This:tables:Forms.filter_expression="Company_ID='"+vCompany_ID+"' "
This:tables:Forms.order_expression="Item"
This:tables:Forms.query()

[Scott Emerick]

RE: Multi Company Accounts

Jerry,

While your method will work I suspect that you would kill the portability of the application since the paths to the tables are hard coded.

Using the method I mentioned above retains the portability. It would also allow you to "Switch data" on selected tables and retain data in lookup tables and such.

The other plus to my approach is that there is no maintenance nightmare because your application remains as one, its just the data that changes.

Again, I refer to QuickBooks where all they do is switch data.

Thats just my 2 cents.

Scott

[Jerry Gray]

RE: Multi Company Accounts

Scott,

Thanks for the input, that might be the best way to go.

Jerry

[Jim Chapman]

RE: Multi Company Accounts

Hello Sarper,

Several good approaches have already been suggested but just to add my 2 cents worth, this is very doable. If there is a compelling reason to seperate the company data into seperate directories, then there are several ways to go.

As has already been suggested, I think the easiest approach would be to create a startup directory, and a startup database. Make all the company directories sub-directories of the startup. This will maintain portability as long as you don't hard code any path statement, but use relative path statements. Your Startup database would simply be a menuing system to goto the various company databases. The drawback of this approach is that you would have a lot of duplication of forms, reports, scripts, operations, etc. This really isn't too big of a deal as you could have a development database, and any changes made could be distributed to the various company databases via a file copy operation.

Another approach would be to have one 'master' database in the startup directory that would be a shadow database of the various company data repositories. To switch between the various company's data you'd have to do a shadow create or refresh command which would cause a delay while data dictionaries were copied to the shadow database, switching company data. If you didn't switch back and forth much during the course of a day, then this shouldn't be much of a deal. But you'd still have all the database object duplication unless:

using this 'master' shadowed database you could re-direct the shadow tables. There are a couple of articles in the newsletter and in the demo's on this site that discuss this. A shadowed table is simply a one record table that contains the path to the 'real' table. Although Alpha protects a shadowed table (rightly so) you can sneak around behind and change it.

There are a couple of other approaches (Xdialog forms, dummy table to support forms populated via variables, and dummy transfer tables, etc) but they'd be a lot of work.

And there is nothing wrong with Scott's idea of zipping and copying.

Good luck,
Jim

[Ronald Anusiewicz]

RE: Multi Company Accounts

Actually, Selwyn did recommended using seperate tables years ago.

Get a hold of a copy of "Application programming in Alpha Five Version 5" by Dr. Peter Wayne.

Chapter 30: Programming for Multiple Data Sets.

Ron

[Tom Henkel]

RE: Multi Company Accounts

Just to add to Jim's ideas. We use a master menu database that "calls" numerous other databases. As our "system" is so big, having all of the tables, sets, etc. in one database would be a maintenance nightmare. We have our own Security login functions which determine who you are, and where you are allowed to go.

I posted an article in the alpha forum a while ago that layed out how we went about using a "menu" database to "call" other databases and return back. This is not a task to be taken lightly, or by the timid. As you are opening and closing databases, you need to consider how to retain global variables and such. (use addin.variables()).

Just so you know, YES, it can be done, YES, there sometimes is a need for it. BUT... you do need to be VERY careful in your design and coding.

Tom

[Mike Vance]

RE: Multi Company Accounts

I put this in the Wish List a month or so ago
668
Subject: Data files in different folders
Author: Mike Vance
Date: 05-18-2005 9:58 AM

It is very easy to set up a new company in MYOB and other accounting software - MYOB uses only one file for the database like Access.

I want to be able to copy the default data files (tables) after creating a new folder - then allow my user to select which folder to us. Dir_get and Dir_put go some way towards this. Mike