I'd avoid a script for this. Instead I'd base my value list on a Company Name field in your CountDB Table. I'd update the table to make sure it has one record for each company in my value list. If you have a very large number of company names in WorkersDB you can extract the values as a text file and import it into the CountDB Table. Here's how:
Define a calculation field in your workersDB table: ValueListItems ( get(fileName) ; "CompanyList")
Set it to return text.
Now place this field on a layout where you can see it. If you click in it, you should see all your company names. Copy all the text and paste it into a Word Processor or Text editor and save it as a text file. Now use Import Records | File to import the text (select tab or csv file format) into your CountDB table.
PS. I'd also put a serial number field in CountDB to uniquely identify each company and use it instead of the company name to link the two tables (You'll want to replace company name in WorkersDB with a new matching number field.) Companies do change their names from time to time and this name change will mess up your data if you use the name as your key.
Thanks for the advice. I tried a bit around - the problem is, that I am using the companyList for two different tables (WorkersDB but also RoomsDB). Every worker has a company, every room is rented by a company, and the workers have a spot in a room (but not every worker in that room is always working for that company). Now I haven t found a way to connect these to Tables with the CountDB without causing a mess. Maybe there is a better way of doing that. Any idea.
PS The countDB is not used a lot, just once in a while to get an overview. Regarding the size of the tables:
About 10000 workers, 500 Rooms and 100 different companies at the moment.
The countDB is not used a lot, just once in a while to get an overview.
The simple way to get this overview is to produce a report from the Workers table, sub-summarized by Company. Use a summary field to count the records. If you delete the body part of the layout and leave just the sub-summary part, you'll get a list of companies and their respective worker counts.
Everytime we give advice, the information that isn't posted can easily change the validity of our suggestions. In this case, however, I still would recommend that you set up a table where one record=1 company and you have a unique serial number ID as your primary key in place of the company's name. (Think what happens to your database when "Acme Databases" reorganizes and changes its name to "Pinnacle Data Systems" or some such.)
You have a many to many relationship between companies and rooms. The fact that some workers are assigned to rooms that belong to a different company shouldn't be a problem as you can refer to a relationship between the worker and company tables to identify their employer.
I'd probably set up the following tables and relationships:
Workers with WorkerID as a Primary Key
Companies with CompanyID as a Primary Key
Rooms with RoomID as a Primary Key
Room_Company a Join table between Companies and Rooms
Workers::WorkerID--=--Companies::CompanyID //Shows what company employees each worker
Rooms_Company::CompanyID--=--Rooms::CompanyID //Identifies the company to which a room is assigned
Rooms_Company::RoomID--=--Rooms::RoomID //Identifies the rooms assigned to a company
This assumes that a worker is only assigned to one room. If you have multiple room assignments for a given worker, you'd need one more Join table for that as well.
You have a many to many relationship between companies and rooms.
Doesn't look that way to me:
every room is rented by a company
Good catch. If so, then scratch the join table.
Hi Thanks a lot for your help (both of you!)
Now it works
I first tried the "easy" way with the report but I can see the advantage of the bit more complex solution.
Nice that there is so much help out there