2 Replies Latest reply on Nov 6, 2014 9:08 AM by mcemond

    Portal showing schedule filtered by current record

    mcemond

      Title

      Portal showing schedule filtered by current record

      Post

      Hi! I have a table workOrder that is related to another table crewAssignment. Relevant fields:

      workOrder::workOrderStartDate (date)
      workOrder::property
      crewAssignment::schedule (date)
      crewAssignment::crewName

      When I am on the workOrder layout, creating a new work order, I enter a date for workOrderStartDate and I have a portal on the layout based on crewAssignment. In this portal I would like to show crewName, filtered by schedule where schedule matches workOrderStartDate. So basically I choose a date that I'd like to start the work and a list generates that shows me who is already assigned to work orders that day (and where - workOrder::property).

      So far my attempts at scripting an effective filter for the portal have failed. 

      Any help would be great. Thanks!

      Mike

        • 1. Re: Portal showing schedule filtered by current record
          philmodjunk

          Why would you use any filter at all?

          This relationship:

          WorkOrder::WorkOrderStartDate = crewAssignment::Schedule

          can be the relationship on which you base your portal and then the portal will list all crewAssignment records where the value in Schedule is the same as WorkOrderStartDate. You won't be able to show the location assigned to for each record unless you then add a new relationship to a different occurrence of work order. But I don't see the right fields to set up a correct relationship for that.

          You'd need this relationship to set up the crewAssignment records for a specific work order:

          WorkOrder::__pkWorkOrderID = crewAssignment::_fkWorkOrderID

          with such fields and values in place, you can have this set of relationships and table occurrences:

          WorkOrder|Location------<CrewAssignment|Schedule>------WorkOrder ----< crewAssignment

          WorkOrder::__pkWorkOrderID = crewAssignment::_fkWorkOrderID
          WorkOrder::workOrderStartDate  = CrewAssignment|Schedule::Schedule
          WorkOrder|Location::__pkWorkOrderID = crewAssignment|Schedule::_fkWorkOrderID

          With those relationships, you can put a portal to crewAssignment on your WorkOrder layout to show the crewAssignment records specific to the current work order. you can also put a portal to crewAssignment|Schedule on the same layout to show all crewAssignment records where Schedule = workOrderStartDate. You can add the location field from WorkOrder|Location to the portal row in this second portal to show the location specified for that crewAssignment record's work order.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Portal showing schedule filtered by current record
            mcemond

            Thank you! Your suggestions helped me get this working.