How to make a table unique and local to each user?

[brett s]

In a multi-user A5 application I have an "extract" table that holds data that a user populates with a selection of records from a customer table. The customer table is, being part of the application master tables, shared by all users of my application of course but I want each extract table to be unique for each user. That is to say each user has their own extract table to populate with customer records however they wish. (They might, for example, each build up an extract table for a particular marketing campaign they are running).

So my question is how do I name the extract tables? I cannot give them just one name such as "ExtractTable.dbf" because if I did this the table would end up being shared by all users, which is not what I want. I could generate a unique name for each user's extract tables but if I did this the name would have to be created at run time (wouldn't it?) and then I could not define A5 forms, reports, labels etc. because the table only exists at application run time.

Does anyone have a really good solution to this problem? The solution should provide all my application users their own extract table but yet still allow me to develop A5 sets, forms, reports, labels, etc. against it. And hopefully I only need to do develop these once, not once for every user!.

Many thanks.

[Jim Chapman]

Hello Brett,

I think the approach you should consider would be to simply create a single shared table and then only allow a given user to view, manipulate and report on the records they have added. I would think this could be easily accomplished by having a UserID field in the table/s in question. The other advantage to using this approach is that a given user could use any computer on the network.

That being said, you can certainly do what you are asking for, but it might be a slippery slope. Couple of approaches:

A while back we had a couple of articles in the Alpha Newsletter about using Shadow tables as a redirection device. This might be something to consider.

You could create a 'template table' with all the forms, reports, etc that you would want each individual user's table to have. Now the first time that a newly installed application called for the user's table, create it via xbasic by duplicating the 'template table' in the users private path. This approach would allow the app to be portable, with the caveat of having to recreate the user's individual table on each new system that the app was ported to.

The down side to having an individual table for each user would be the issue of updates and improvements. If you wanted to make a change or say add a report after the system was in use you'd have to have a well thought out approach to getting this done. Also if a users hard drive went south, you'd loose his local data if it wasn't backed up, and you'd have a different backup for each user (in the sense of their individual table). Also you'd run into some real head scratchers if you wanted these individual tables to be part of sets with tables that were in the shared database on the server. All this is doable but my guess is it will cause you more work and headaches than it is worth.

All in all, you'll save yourself a ton of work and make the app much easier to maintain and update if you just have a shared table and limit what each user can see to their own records. This is a very common technique using a simple index and queries. The stuff you do all the time in other contexts.

Good luck,
Jim

[Steve Workings]

Jim's message is worth reading a couple times.

In the event you still want seperate tables, I've done this pretty simply for a couple customers over the years.

Build your table with reports and whatever else you need. Then create a routine that copies it to the local drive and populates it there. The result is the same table for each user, but seperately populated.

Another variation I've used is to copy the table (and its dictionaries as needed) to another name. You can use variables such as api_getusername() to create multiple copies of the same table, e.g. Temp_Steve.dbf, Temp_Jim.dbf, etc. You'd probably want to delete the copies when you're done with them.

[Finian Lennon]

In general I agree with Jim and Steve, but would disagree with the suggestion that the tables should be created on the local machine.

I have subfolders "Workstations" and "Users" in the main application folder, with template tables and files in the top level of each. When a new user or machine logs into the system, a new folder is created for that machine or user in the appropriate location and the tables and files are copied there. They are then easy to access via xbasic and, from my point of view more important, all the application files remain in a single location for easy support, analysis, archiving and backup.

Finian

[whanigsberg]

Although the advantages of Finian's approach are obvious it will sometimes be thwarted by network security constraints restricting the user from creating or deleting files.

While I understand that it is possible to selectively relax these constraints I also appreciate the difficulty of convincing MIS departments to do so. If you work in a single organization it is practical but if you hope to install your application in multiple organizations you are taking on a new career.

[Peter.Greulich]

To chime in w. the experts, I'll throw my 2-cents in. In my company we have electronic time sheets - a.k.a. an Alpha table in the company database. When each users logs into the db, their user_id and user _level is assigned to global variables. When they open the timesheet table, they only see their own records for the current week. They can also click a ui_popup that displays all of the their own previous weeks and view their time entered. The whole thing uses a query based upon the user_id + w/e date. Conceptually it is very simple and works beautifully.

See the attached screen shot.

[Finian Lennon]

I sympathize with Bill's difficulties with intransigent MIS departments. Make them take responsibility for the user machine backups and they may be more cooperative. Or take responsibility for supporting the application and therefore have to get access to every user machine in order to investigate issues that may arise. That will change a mind or two.

I'm guessing that in the vast majority of cases getting appropriate access for users within the application folders should not be a problem.

Finian

[brett s]

Many thanks, guys. Some really good suggestions here. I will print all this off and read it carefully, then decide how I'll do it. Many thanks.