1 Reply Latest reply on May 27, 2016 11:56 AM by erolst

    Conditional Weekly List

    ChadBarnard

      I currently have a layout where I list out which crews are doing which projects for the current week.

       

      When I had only one trip attached to a job, everything was fine, but now the company wants to track multiple trips for one project.  I now have the followed fields that show up on the job record:

       

      AssignedCrewFirstTrip
      CrewStatusFirstTrip
      InstallDateFirstTrip

      CompletionDateFirstTrip

       

      I have the same fields for the second and third trips, and I'm using a drop-down to determine which fields to display at any given time.  All of that makes sense and works fine while on the individual job record.

       

      How do I set up a search that will display a list of the appropriate jobs w/ the appropriate crew names depending on the week?  For instance some jobs will need to reference first trip crews and some jobs will need to reference second trip crews.

       

      I figured that I would have to create a different field (AssignedCrewComposite) to be used to insert the appropriate crew name by way of a Set Field calculation.  In conjunction with an If/Else If/ Else statement, I tried to do this, but it only enters in the crew name for the first record - all of the remaining records in the list are blank.

       

      Thanks in advance for any assistance.

        • 1. Re: Conditional Weekly List
          erolst

          ChadBarnard wrote:

          I now have the followed fields that show up on the job record:

           

          AssignedCrewFirstTrip
          CrewStatusFirstTrip
          InstallDateFirstTrip

          CompletionDateFirstTrip

           

          I have the same fields for the second and third trips, and I'm using a drop-down to determine which fields to display at any given time. All of that makes sense and works fine while on the individual job record.

          It may look like it makes sense, but in the long run this is hard to maintain and brings (as you have experienced) certain problems.

           

          You should create a related ProjectTrip table where you only need one generic set of these fields:

           

          AssignedCrew

          CrewStatus

          InstallDate

          CompletionDate

          [Trip# ?]

           

          and can add as many trips as you like for any project.

           

          There you can also search by whatever criteria you like … crew name, duration, project (from the Project table), and, of course, date.

          1 of 1 people found this helpful