Creating Project Database in relation to TMK and Client
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,