Multi Company Accounts

[Mike Vance]

There was a thread 'Multi Company Accounts' back in A5 Version 6 which discussed various methods of having a master application in one directory with sub folders for each company's set of accounts using thesame application tables and sets.

For example using shadow tables or zip and copy to save having multiple copies of the same application to maintain when an update is developed.

Now we are into Version 8 I wonder whether anyone has any further thoughts on this topic. Mike Vance

[DaveM]

Re: Multi Company Accounts

Mike,

This can as a part of your application and it is all in one app with one set of tables. No company sees the other companie's data and further, no sales person sees another sales person'd data within each company. You can also set it up so a bookeeper sees only the data for their job, a receptionist sees only his/her data. It can all be done in one app with one set of tables. All files are in one folder.

There have been several discussions about this from time to time.

If you need ideas on how to do that?

Dave

[martinwcole]

Re: Multi Company Accounts

I suspect a main issue would be record count

My largest client has 2 companies and each company has several offices.

the customer number is 9 digits
the first 2 digits are the company(allowing for up to 99 companies)
the second 2 digits are offices(allowing for up to 99 offices)
and the last 5 digits are for the client(allowing for up to 99,999) clinets per office

010100001 is company 1, office 1, client 1
020900985 is company 2, office 9, client 985

this system has been in use since 1999.

in the autoexec, when they log on, I determine the company and office and store then in constants. On the forms, based on the constants I can conditionally display different titles, etc.

Since this customer has only about 26,000 clients, and at the outside 50,000 records in any of the child tables, it works well. But if they had say 250,000 clients, I would most likely split them up.

(Although, just for the record, I know of one developer that has a very very large app, that has as of about 6 months ago 2.6 million records in one table that is used on 50 workstations where over 1,000 new orders are logged in every day - he is still in V5. I shuddered when I saw the tables, but with his hardware - which is ultra modern/fast/etc - it works!)

[DaveM]

Re: Multi Company Accounts

the customer number is 9 digits
the first 2 digits are the company(allowing for up to 99 companies)
the second 2 digits are offices(allowing for up to 99 offices)
and the last 5 digits are for the client(allowing for up to 99,999) clinets per office

I do this quite differently:

1 I had not very good experiences with concatenating companies, offices and employees into a field. I use an autoincrement character(8 spaces) "acct" field just to keep records together. Each record(customer) inherits the company code and so does any child record explicit to that record.

I have 2 digits for company in the company info that will allow up to 99 companies and a 8 digit field for the user code.

Each person/office using the software( salesperson, manager ) has a user code, and is assigned to a company(they could be in there 2 times if needed with different user codes) and they a have a permissions setting of 1 to 9 with 9 being the highest.
Each form/report/browse/etc is set to only show the needed for that individual/company.

This is all accessed on startup by way of autexec and startup form logins that prompts for user names and passwords.

I guess you could carry this much further, but was as far as I needed it.

If your tables are set up right and your sets are right, there should be no problem with large numbers of records.

Dave

[Mike Vance]

Re: Multi Company Accounts

Dave and Martin - thanks for replying. Here's a scenario of what I want to achieve.

My application is used by an accountant who has many different clients. Each client can have many different sets of accounts for various trading years e.g. Fred2005, Fred2006, Fred2007. Each of these sets of accounts must be kept in different folders or in a backup zip file which can be restored when needed to work on.

The 'Master' application is accounts.adb which is installed into a folder c:\accounts. It has many tables and sets. The same application is used for each set of accounts - it is updated from time to time.

Unfortunately with Alpha you cannot separate the data files from the application ones so it is a maintenance nightmare when accounts.adb is updated if the new version has to be distributed to every folder. If backup zip files are used which have an old copy of the accounts.adb application, when restored these will overwrite the latest master application.

This is where I need some ideas on how best to achieve a work around the maintenance problem. It would be great if a Shadow database could be used and refreshed but the application is one one computer only, not a network.
There are various ways of using file.copy_folder , copy_folder, file.zip and file.unzip routines but the maintenance problem would still exist unless a second master folder were used.

Any ideas appreciated. Mike

[DaveM]

Re: Multi Company Accounts

Mike,

I don't know how a shadow would help you, but you can have a folder on one computer for your data files and a seperate folder with the shadow that runs off those files.

Dave

[DaveM]

Re: Multi Company Accounts

Each of these sets of accounts must be kept in different folders or in a backup zip file which can be restored when needed to work on.

Why do they have to be in seperate folders? They just need to have different account/who&year/etc numbers and shold be able to reside in the same tables. Much simpler in the long run.

All(to the best of my knowledge) major accounting packages do multiple companies and in the same tables.

Dave

[Peter.Greulich]

Re: Multi Company Accounts

Mike,

I don't see why you can't keep all the data "mixed" in one set of tables - provided that you use a scenario like described by Martin or Dave, and you create a mechanism to allow exporting of client data sets as needed. It's really only a matter of querying. I don't see what shadowing would do for you if you were on a network with respect to the problem that you describe.

[Mike Vance]

Re: Multi Company Accounts

Thanks Dave & Peter

Re the Shadow I mentioned this because it was discussed in the earlier thread as a possibility. I agree with your comments however.

MYOB uses the scenario I described but there is only one .dat file in their applications, not lots of tables and sets.

I have not yet tried to proceed along the lines Dave and Martin mentioned. This does seem the way to go. Any further tips along those lines would be appreciated.

Thanks. Mike

[Ton Spies]

Re: Multi Company Accounts

Hi Mike,

Did you read something about the CSDA-utility tool Ira Perlow made?
It's cheap and with that tool you can make a backup of only datafiles.
Do a search and you'll find what you need or contact him for a script.
It's really cheap but.... if you have it you don't want to miss it/ can't without it.

http://www.csda1.com/products.html

He just finished it for version 8.
Recommended!

Regards
Ton

[Mike Vance]

Re: Multi Company Accounts

Thanks to everyone for sorting me out. I had a mental block and had convinced myself that I needed lots of different folders. Having experimented with the methods suggested by Martin, Dave and Peter I can now see that one set of tables is the obvious way ahead for me.

Ton - thanks for your recommendation. Ira's CSDA utility sounds great. I'm going to get that.

This kind of support is fantastic and demonstrates why Alpha is such a great product. Thanks again. Mike