7 Replies Latest reply on Jun 22, 2009 10:39 AM by RobMartin

    Need some help on adding fields up

    RobMartin

      Title

      Need some help on adding fields up

      Post

      Hi

      I have a table (WorkersDB) with the name of the person and the company he is working for. For every new person added to the list, I just have a field with the company, which can be selected from a value list ("CompanyList") or a new company name can be entered - CompanyList is defined as "use values from field company".

       

      Now I have created a table (CountDB) to sum up who many people work for each company. My problem is now, that new added companies have to be added manually to this list - I would like to have a script checking the value List "CompanyList" and checking if all these entries are resembled by one record in the CountDB and if not create one record.

       

      Thanks for any advice

       

      Robert 

        • 1. Re: Need some help on adding fields up
          philmodjunk
            

          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.

          • 2. Re: Need some help on adding fields up
            RobMartin
              

            Hi 

            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.

            Thanks

            Robert.

             

            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.

             

            • 3. Re: Need some help on adding fields up
              comment_1
                

              RobMartin wrote:

              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.


              • 4. Re: Need some help on adding fields up
                philmodjunk
                  

                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:

                 

                Tables:

                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

                 

                 

                Relationships:

                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.

                 

                • 5. Re: Need some help on adding fields up
                  comment_1
                    

                  PhilModJunk wrote:
                  You have a many to many relationship between companies and rooms.
                  Doesn't look that way to me:

                  RobMartin wrote:
                  every room is rented by a company

                   



                  • 6. Re: Need some help on adding fields up
                    philmodjunk
                       Good catch. If so, then scratch the join table.
                    • 7. Re: Need some help on adding fields up
                      RobMartin
                        

                      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

                       

                      Best

                      Robert