3 Replies Latest reply on Jul 6, 2011 9:18 AM by philmodjunk

    Help for a newbie - Setting up a simple work order solution



      Help for a newbie - Setting up a simple work order solution


      Hey all, I'm brand new to Filemaker and this my first post on the forum, hoping someone can help me out as I'm a bit stuck.  I run a subcontracting firm doing residential Audio / Visual installs all over the city.  I am attempting to create a simple Work Order solution that will eventually be hosted on FileMaker Server and accessed in the field using iPads and FileMaker Go.  I'm having a lot of fun learning the software (I'm currently using an older version, Filemaker 8), and building this solution is a good way to get my feet wet.

      My first question is a conceptual one related to Database design.  In order to implement this solution, I have created the following tables:

      - Clients
      - Projects
      - Employees
      - Daily Work Orders
      - Daily Work Order Times

      I understand some of the fundamentals when it comes to Database Design, an I know that Many-to-Many relationships are generally a bad thing.  This is why I have created separate "Daily Work Order" and "Daily Work Order Times" tables (hereafter referred to as DWO and DWO_Times respectively).  Spefically, I saw a potential Many-to-Many relationship between DWO's and Employees (i.e. One DWO can have many employees, and one Employee can appear on many DWO's). Hence, I created the DWO_Times table to sit in between them. So the DWO_Times table has a foreign key to the Employees table, as well as a foreign key to the DWO table.

      Does this sound to you guys like I am understanding the concept correctly? Just want to make sure I'm on the right track. Assuming I am, it gets to my next question. 

      I would like to create a Layout that I am calling "Daily Work Order Detail".  This will be a form that is filled out at the office by either me or my receptionist.  I have attached a screenshot for reference.  I would like a Checkbox set in the middle of the form to include all of my employees names, so that we can check off which installers will need to go to the jobsite.  Sounds simple enough, but I am having a little trouble with it. 

      I drop a field into my Layout, and in the Specify Field dialouge, under related tables, I select the Employees Table, and the EmployeeLastName field.  I then double-click on the field to bring up the "field control / setup" screen.  Select "checkbox" under Display As, and set the Display Values From option to a Value list that i created which populates from the EmployeeLastName field. All of that seems to go fine (SEE SCREENSHOT).  Except now when I go into Browse mode, I cannot select any of the checkboxes.

      I am wondering if this is because I do not have a direct relationship beteen the DWO table and the Employee table? But the two tables are related in a sense, via the DWO_Times table I referred to above.

      Can anyone point me in the right direction? I've been stuck on this for quite some time, and I'm not sure where to turn at this point.

      Thanks in advance!



        • 1. Re: Help for a newbie - Setting up a simple work order solution

          Your basic table structure and relationships for this is sound. You'll just need an interface that works with it. I don't see any simple way to use a single field formatted with a checkbox group of employees that will work here for you. It could be done, but would require a lot of uncessary scripting that we can avoid with a different approach here.

          On your DWO layout, add a portal to DWO_Times. In your relationship between these two occurrences, enable "allow creation of records via this relationship." In your portal, add the Dailty Work Order Times::FK_EmployeeID field and format it with a drop down list or pop up menu of Employee IDs in field 1 and their names in field two.

          Now you can assign employees to a given work order by finding/creating the work order record on this layout, then select each employee from the drop down/pop up on a different row of your portal.

          • 2. Re: Help for a newbie - Setting up a simple work order solution

            Thank you so much for your response Phil, I really appreciate it.  So I've spent a little time tonight tinkering around with your suggestion and I think I've got it, but I it's prompted a couple more questions... hope you don't mind!

            First - After dropping the portal to DWO_Times onto my DWO layout, it asks me which fields I would like to add.  By default the DWO_Times table is selected, and  FK_EmployeeID shows up as an available option.  Should I select this one? Or the Employee_ID from the Employees Table? Does it matter? Are the two one-in-the-same since there is a relationship between them?

            Next, I go to format the field that I have just added to my portal (which in this case is DWO_Times::FK_EmployeeID)  I feel like I'm struggling to understand this part of it, conceptually.  I double-click on the field.  I leave the "Display Data From" portion at its defaults (which in this case would be DWO_Times Table::FK_EmployeeID).  I select "Drop Down List" from the Control Style menu, then I go to Define Value List.  I then create a new list that I am calling EmployeeID_WithLastName. Here's where I get a little confused.  In the "Use Values From First Field" I select DWO_Times::FK_EmployeeID.  But for the end-user, that ID is just a number and won't be very helpful.  So, as you suggested I would like to add the employee last name too.  However, EmployeeLastName does not exist as a field in the DWO_Times table.  Can I select the field from the Employees table as my second option? In other words, can you have fields from 2 different tables contained in one drop down menu?

            I seem to be getting some strange behaviour when I do this, and I'm not sure I'm understanding it correctly. 

            Not sure if my explanation is clear enough for you to glean any useful info from, hopefully it's not too confusing.

            Any advice would be greatly appreciated.  Thanks again,


            • 3. Re: Help for a newbie - Setting up a simple work order solution

              Select FK_EmployeeID from the DWO_Times table occurrence. You'll need this field in order to link employees to your current work order record. This is the field to format as either a drop down list or pop up menu. Having selected that field, you can then add other fields from Employees to display names, job title or anything else from that table that will be useful. When you select an employee in the DWO_TImes::EmployeeID field, these other fields will then display information from that employee's record in the Employee table.

              In your value list, you should specify fields from the Employees table--not DWO_Times. The Employee table is the place where you store your employee data--including names and ID numbers so this value list should list values from this table.