11 Replies Latest reply on Jan 14, 2015 3:25 PM by gilcano

    Using Data Modeling to identify Entities and Attributes in my application


      Hi all, in the application I'm building to keep track of Maintenance Repairs (Work Orders) I’m not sure if I should keep just one table to control the Work Orders.  I want to generate two types of Work Orders, one will be generated for the Tenants and the other will be generated for Contractors (Vendors), PM’s or Inspections.  Obviously in the Tenant's Work Orders the information of the unit (Unit #) and the Name of the Tenant needs to be input (will be pulled from TENANT Table)  but in the second Work Order, the one generated for Contractors, PM’s or Inspections the information about Contractor name (Company Name) and maybe a Company ID must be captured.  All other information is about the same.



      WO_TYPE                                                           TEXT, GENERAL OR CONTRACTOR

      WO_NO                                                               NUMBER, INDEXED, AUTO-ENTER SERIAL

      WO_DATE                                                           DATE, DATE REQUESTED

      WO_TIME                                                            TIME, TIME REQUESTED

      UNIT_NO                                                             TEXT, UNIT NUMBER WHERE TENANT RESIDES

      COMPANY_NAME                                               TEXT, CONTRACTOR NAME

      STATUS                                                              TEXT, OPEN, ON-HOLD, OUTSIDE CONTRACTOR Or CLOSED

      WO_CATEGORY                                                 TEXT, VALUE LIST OF DIFFERENT CATEGORIES

      WO_ISSUE                                                          TEXT, VALUE LIST OF DIFFERENT ISSUES

      ASSIGNED_TO                                                    TEXT, VALUE LIST OF THE EMPLOYEE ASSIGNED

      DATE_ASSIGNED                                                DATE, DATE ASSIGNED

      TIME_ASSIGNED                                                 TIME, TIME ASSIGNED

      DATE_COMPLETED                                             DATE, DATE COMPLETED

      TIME_IN                                                               TIME, TIME THE EMPLOYEE/CONTRACTOR WENT IN

      TIME_OUT                                                           TIME, TIME THE EMPLOYEE/CONTRACTOR WENT OUT

      DISPOSITION                                                     TEXT, DISPOSITION TO SOLVE THE PROBLEM

      NOTES                                                                 TEXT, ANY COMMENTS ABOUT THE WORK ORDER

      PDFCOPY                                                             CONTAINER, WILL HOLD A PDF FILE OF ORIGINAL WORK ORDER


      I recently learned a technique of hidden tab controls which I think is very COOL, using the WO_TYPE will display the “TAB” created for TENANTS if WO_TYPE=’GENERAL’ or display the “TAB” created for CONTRACTORS if WO_TYPE=’CONTRACTORS’.  I don’t think is necessary to create two WORKORDER Tables, one for TENANTS and one for CONTRACTORS.   Any comments will be appreciated.


        • 1. Re: Using Data Modeling to identify Entities and Attributes in my application

          Since the tenant work order and the contractor work order share the same info you can use just one table. Hidden tabs can be used to show the different data but aren't always the best solution. I would use different layouts for the tenant and contractor layouts. A script would load the correct layout based on the user.

          • 2. Re: Using Data Modeling to identify Entities and Attributes in my application

            I agree with Todd. One table is needed for this data as the entity is called "Work Orders". Even through you have different types, they're the same thing. (This is similar to contacts: you can have different types of contacts (phone, email, fax, etc) but contacts is simply that, so all the different types belong together).


            And yes, hidden tabs are cool, but it is easier to use different layouts for each type of Work order. Hidden tabs can drive a developer crazy in having to show the tabs in order to click on the one to edit. I'd just make the template layout with all the elements that are the same and then duplicate it to customize layouts.


            If you use a layout-naming convention, such as WorkOrder_Tenant and WorkOrder_Contractor, your script can use the GoToLayout by Calculation function. There you can concatenate "WorkOrder_" with the type of work order a user will be making. That way you're using one script step with possibly a script parameter or type of user.

            • 5. Re: Using Data Modeling to identify Entities and Attributes in my application

              Thinking about the WORKORDERS table and came with the idea to put the container field PDFCOPY in a separate table called DOCUMENTS and relate this table to the WORKORDERS by the WO_NO key.  Keeping a PDF inside the WORKORDERS table will bloat the table enormously, on the other hand if all the PDF's are in a separate table they will be show/edited when needed.  Since I'm going to produce two types of WO, General for Tenants and Contractors for Companies I can storage everything in this Document Table.   What do you think? This is what I'm thinking


              DOCUMENTS Table

              DocumentID                                        Number, Auto-enter serial

              WO-NO                                               Text, foreign key to relate to WORKORDERS table

              Title                                                     Text,  Optional? Could be General or Contractors

              Document                                            Container, will hold a PDF copy of the work order

              • 6. Re: Using Data Modeling to identify Entities and Attributes in my application

                I like using a separate Documents table… sometimes even a separate file. That can make backups smaller, if your data file changes a lot, but your documents don't. As for things being bloated, that will depend on your storage options. Will this be hosted with FIleMaker Server? External storage can keep your file lean.


                Keep in mind that you can also have other fields auto-enter info about the file in the container using the GetContainerAttribute function.




                • 7. Re: Using Data Modeling to identify Entities and Attributes in my application

                  You probably only need one foreign key field which contains the ID of either the Tenant or the Contractor (or the Inspection).


                  Also, hidden tabs can be useful, but for this I'd use Hidden Objects. Most of the layout will be the same, right? Except for two or three fields? Then use the "Hide object when..." option in the Inspector.

                  • 8. Re: Using Data Modeling to identify Entities and Attributes in my application

                    Hi gilcano,


                    In the title of your post you mention 'data modelling' to identify Entities and Attributes. 


                    It sounds to me that the Tenant Work Order is really a 'request' for work to be done and that the other type of Work Order is for the people doing the work i.e. the Contractor does the work, an Inspector inspects the work done, etc.


                    The data modelling question I would ask is "is there a relationship between the two types? (and what is the relationship)"


                    If one Tenant Work Order can be related to one or more Contractor Work Orders (maybe the work isn't all done by one Contractor) you may have a one-to-many relationship between the two types and this would be difficult to place in one table, you'd need an associated table to hold the instances of Work Orders that are related to each other.


                    There may be a one-to-many in terms of of One Tenant Work Order is related to (at least) one Contractor Work Order and one Inspector Work Order (assuming all work is inspected) and for big jobs even a PM Work Order.


                    I think all of the answers given to your question are valid but from a data modelling point of view it depends on what your 'business rules' are as to what data model (and implementation of that model in FM) makes sense for your needs.


                    I hope that helps

                    • 9. Re: Using Data Modeling to identify Entities and Attributes in my application

                      OK let me explain, A tenant can submit a "request" which is really a Work Order for our company, then after acknowledge we determine if the job can be done by "us" (maintenance workers) or if a "contractor" is needed.  That's why in the "status field" of the Table WORKORDERS we use a value list as follow:  OPEN, ON-HOLD, OUTSIDE CONTRACTOR, & CLOSED.


                      On the other hand I would like to produce a Work Order as a result of an inspection, i.e.  If I'm inspecting the basement and find a leak on one of the main pipes or risers that deliver the Domestic or Heating water then I need to call a "plumber".  This plumbing company will have their own service ticket or work order but I want to produce my own to keep records.  Another example is the company that deals with our HVAC units, every time one unit malfunctions I need to make a service call, etc. 


                      Also PM Work Orders can be trigger as a result of a scheduled maintenance system.  Right now is nothing in place but a manual paper filling forms that is going out of control, I have too many binders of Tenant Work Orders, Contractors service tickets and Work Orders that are almost impossible to track.  The main problem is when "someone" misplace a Work Order or a Contractor ticket.  The company I work for have 24 Building with over 3,500 apartments.  The idea is to make this application by Buildings and then have it running is a FM Server in the Central Office.  Every building have a Windows PC already installed and in the Company Network so I think that could at least is something we can benefit of.  



                      • 10. Re: Using Data Modeling to identify Entities and Attributes in my application

                        Yes David, most of the LAYOUT will be the same, the only fields that will change are the  Tenant Name and Tenant Unit if WO_Type = General, Company Name if WO_Type = Contractor and can have also and Inspection type if we need to.


                        I was checking the "Hide Object when..." concept in a video called CONTROL OBJECT VISIBILITY and it's pretty powerful and simple, you don't need complex formulas, functions or scripts codes to achieve results.  I'll use this concept.


                        • 11. Re: Using Data Modeling to identify Entities and Attributes in my application


                          I was watching  a FileMaker training video Idea to Ipad - Document Management and it's very interesting, there they talk about keeping the documents in another Table.  Yes the idea is to run the Application in a FileMaker Server as I mentioned before the company I work for have 24 Buildings with over 3,500 apartments so we will need to storage a lot of documents.