1 Reply Latest reply on May 6, 2011 11:25 AM by philmodjunk

    Searching Multiple Fields in a Find

    mk4482

      Title

      Searching Multiple Fields in a Find

      Post

      I am using FMP 11 Advanced on a FMP 11 Server.  I have a "work order" database in which jobs that come in are assigned to specific techs.  When tech 1 finishes their specific function, they re-assign the work order to tech #2 (from a drop-down list) to complete their specific function, and then on to tech #3, etc.  Up until now, I have had one "tech assigned" field which made the "find pending jobs" for a particular tech easy.  I have created more tech assigned fields (tech 1, tech 2, tech 3, tech 4) so I can keep track of when a tech is assigned the work order and when the next tech gets the re-assignment.  When the first tech finishes their assignment, they will reassign the work order to another tech using the tech 2 field, and when that tech finishes their assignment, they will reassign the work order to another tech using the tech 3 field, etc.  Not all work orders will use all 4 tech fields.

      My problem is in writing a script so that I can find current pending work orders per tech.  So how would I write (I am not a programmer) "if tech 4 field is blank use tech 3 field (assuming tech 1 and tech 2 fields are used), if tech 4 field and tech 3 fields are blank use tech 2 field, if tech 4 field and tech 3 field and tech 2 fields are blank, use tech 1 field? 

      I hope this make sense.  Thanks!

        • 1. Re: Searching Multiple Fields in a Find
          philmodjunk

          Sounds like you need several tech records that you can all assign to the same work order record and show a specific sequence to the assigned techs. This should be done with separate records instead of a group of fields on the same record. As you have just discovered, that approach can really complicate how you work with your data.

          WorkOrder----<AssignedTechs>-----Techs

          Workorder::WorkOrderID = AssignedTechs::WorkOrderID

          Techs::TechID = AssignedTechs::TechID

          You can use a portal to assigned techs on your work order layout to list all assigned teches for that work order. A number field defined in AssignedTechs can be used to keep them in the order you need them to be. (And a portal to AssignedTechs on the Techs layout can list all assigned workorders for that tech.)

          This is called a many to many relationship. You may want to examine this demo that matches contracts to companies in a many to many relationship to see how it can be set up: 

          http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html