6 Replies Latest reply on Apr 17, 2014 1:47 PM by philmodjunk

    Creating Project Database in relation to TMK and Client

    Kaspoon808

      Title

      Creating Project Database in relation to TMK and Client

      Post

           Aloha!

           I have been tasked with creating a database to bring our small firm into the digital age. There will be literally thousands of entries made to modernize our Project Directory. Yeah, its a huge leap for us. Unfortunately, I am the guy who needs to set this up. I have a very basic knowledge of FMPro and am currently trying to increase that knowledge. I'm currently using FM Pro 13. 

           Every project has:

           A. Project Number - a unique number based on year and order of creation, for example: "14-001" being the first project of 2014.

           B. Tax Map Key(s) = unique number that identifies each individual parcel of land. That number is a 9 digit number series. that is created by combining the Island, Zone, Section, Plat and Parcel. example 0-0-0-000:000. These are critical as the State of Hawaii, various County Offices and other records use these numbers to identify parcels. 

           C. Client - each client may have several persons of contact.

           D. Employee - person assigned to Project

           E. Field Book - Book number and page of field work.

           F. Description - A short paragraph describing Project

           G. Date - Date Project was created.

           A Project may be related to multiple TMK's
           A Client may be related to multiple Projects
           An Employee may be related to multiple Projects

           I have created tables; TaxMapKey, Projects, Clients, Employee, Join_Projects_Clients, Join_Projects_TaxMapKey, Join_Projects_Employee. The appropriate ForeignKeys have been made and I have done my best to relate the tables correctly, however, i am having a few issues.

           1. The TMK table is probably the most problematic. The sum of the parts, as stated above, needs to be a unique number. No redundancies. I've created a calculation field to combine the parts. how can i attribute an existing TMK and a new TMK with a Project? 

           2. Upon assigning a Client to a Project, i am getting redundant data in the Client::ClientName field. Is there a way to create a new Client or choose an existing client from the same field?

           3. The same issue is happening with Employee::EmployeeName

           Any help or links with these issues would be truly appreciated. 

           Mahalo Nui Loa,

           John

            

        • 1. Re: Creating Project Database in relation to TMK and Client
          philmodjunk

               TMK = "Tax Map Key"?

               

                    The sum of the parts, as stated above, needs to be a unique number.

               Sum of exactly which parts?

               However you answer those questions, I strongly recommend that you set up primary key fields that are auto-entered serial numbers, not values calculated from other fields in your database or the year. You can define such fields in your tables if your users require them, but don't use them as the match fields to foreign keys in related tables. (In some cases, it may make sense to use an auto-entered get ( UUID ) instead of a serial number.)

               

                    how can i attribute an existing TMK and a new TMK with a Project?

               To answer that, you'll need to explain what a TMK is and how it relates to records in the projects tables. What does one record in TMK represent? How will use use the data in that TMK table as you manage your projects?

               

                    Upon assigning a Client to a Project, i am getting redundant data in the Client::ClientName field. Is there a way to create a new Client or choose an existing client from the same field?

               Possibly. There are also ways to select a client such that all existing clients are listed in a value list or in a selection portal. If the needed client is not so listed, then the user knows to click a button to perform a script for adding a new client.

               You may find it helpful to examine the design of the following demo file. It's created in an older file format but it illustrates a many to many relationship between "contacts" and "events". If you think of contacts as "employees" and events as "projects" (or "clients" and "projects"), it can serve as a model of several different ways to work with such a many to many relationship and its join table. You can use Open from FileMaker 13's File menu to open this file and produce a copy converted to the newer file format:

          https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                

          • 2. Re: Creating Project Database in relation to TMK and Client
            Kaspoon808
                 PhilModJunk,
                  
                 Thank you for the response!
                  
                 I tried to explain what a TMK (Tax Map Key) is in requirements of my projects. I apologize if i didnt explain it well.
                  
                 "Tax Map Key(s) = unique number that identifies each individual parcel of land. That number is a 9 digit number series. that is created by combining the Island-Zone-Section-Plat-Parcel (PARTS). example 0-0-0-000:000 (ALL PARTS TOGETHER). These are critical as the State of Hawaii, various County Offices and other records use these numbers to identify parcels. " Basically its like an address of the property. It is a serial number, of sorts. One record in the TMK table represents one (1) specific piece or parcel of land. However its not something that we can make up. The parcel of land is already given this number by the Tax Office. I need to relate those specific Tax Map Keys (specific parcels of land) to my project. Each project deals with one or more Tax Map Keys.
                  
                 Maybe it would help with a scenario:

                 Client "A" calls and gives us two parcels of land to survey. Tax Map Keys 1-9-1-057:005 and 1-9-1-057:006. I assign an employee to the project and we carry out the work. 
                  
                 I've downloaded the many to many demo: I'll attempt to understand it. Thank you again for your time and effort!
                  
                 Mahalo!
                  
                  
            • 3. Re: Creating Project Database in relation to TMK and Client
              philmodjunk

                   So you can relate projects to TMK like this:

                   Projects----<TMK

                   Projects::__pkProjectID = TMK::_fkProjectID

                   Each record in TMK would store a different Tax Map Key in a text field. A given project record can link to as many TMK records as you need.

                   But a given TMK record, could, conceivably, be linked to more than one project. Thus you may want to set up a many to many relationship to support that possibility:

                   Projects----<Project_TMK>----TMK

                   Projects::__pkProjectID = Project_TMK::_fkProjectID
                   TMK::__pkTMKID = Project_TMK::_fkTMKID

                   Please note that the acutal TMK code is NOT used as a match field in any of these relationships.

                   Whether you need this many to many relationship will depend on what other data you might need to record in a given TMK record. If you need to add a physical description, GPS coordinates, the acreage or some other such details for each plot, then the need for a many to many relationship seems a more likely option. If you only need to list the TMK codes for a given project, on the other hand, then there's no real need for the join table.

                   A portal to Project_TMK can be used to select existing TMK records for a given project. A button on the same layout can perform a script that adds a new TMK record and also creates the needed record in Project_TMK with the needed IDs to link the current project to the newly added TMK record.

              • 4. Re: Creating Project Database in relation to TMK and Client
                Kaspoon808

                     PhilModJunk,

                     I got my tables set up as explained and it works great. thank you for your help! Your Demo file helped a ton too. I already have a solution that requires your "Diminishing Returns" setup. It will make things easy with your demo to follow.

                     Mahalo!
                     John

                      

                • 5. Re: Creating Project Database in relation to TMK and Client
                  Kaspoon808

                       Aloha, Again...

                       I have another issue. I select my existing clients and employees from a drop-down list but the name doesn't display (update) until i click the body/header/footer anywhere outside the client portal, but not another field.

                       the list was built like this:

                       -Portal showing records from Project_Client join table

                       -Field 1 is a drop-down list using values from Project_Client::ClientIdFk also displaying Client::ClientName showing only value of the latter. Find mode entry is disabled.

                       -Field 2 is an edit box showing Client::ClientName. browse mode entry is disabled

                       Field 1 is hidden below field 2. when user selects existing client from drop down list (Field 1) Field 2 does not update its display until you click outside the portal but not in another field. Any Ideas on the reason? 

                       Thank you for any help you can offer. 

                  • 6. Re: Creating Project Database in relation to TMK and Client
                    philmodjunk

                         That mouse click commits the record and that is needed before the relationships can kick in and update what data appears in the name field. You can use a script trigger on the drop down list field to perform a script that commits records to avoid the need to click the layout background.

                         But your value list really should be set up to list ID from the Client table, not the project_client join table. Otherwise, you only get a list of those clients already selected in the join table instead of a list of all clients.