3 Replies Latest reply on May 16, 2012 8:38 AM by philmodjunk

    Portal Filtering

    ShanaEichleberger

      Title

      Portal Filtering

      Post

      I am very new to filemaker and am having an issue. I would like to filter a portal but I need it to check four different fields. 

      I have a table in which its a vehicle report and each trip log as a job number assigned to it. I would like to pull the related trip logs to another table by the job numbers matching. However I need to check four different fields for the job number.

      My code right now is: 

      Case(Daily Vehicle Reports::Job Number1=Job Cost Sheets 4::Jobnumber;) or

      Case(Daily Vehicle Reports::Job Number2=Job Cost Sheets 4::Jobnumber;) or

      Case(Daily Vehicle Reports::Job Number3=Job Cost Sheets 4::Jobnumber;) or

      Case(Daily Vehicle Reports::Job Number4=Job Cost Sheets 4::Jobnumber;)

       

      My relationship is set up with Job Cost sheets 4 related to Job Number1, Job Number2, Job Number3, Job Number4.

       

      But this will not filter like my other portals.

        • 1. Re: Portal Filtering
          philmodjunk

          Try this expression:

          Daily Vehicle Reports::Job Number1=Job Cost Sheets 4::Jobnumber or

          Daily Vehicle Reports::Job Number2=Job Cost Sheets 4::Jobnumber or

          Daily Vehicle Reports::Job Number3=Job Cost Sheets 4::Jobnumber or

          Daily Vehicle Reports::Job Number4=Job Cost Sheets 4::Jobnumber

          Your Case functions were returning null as you had no value specified for when the expressions evaluated as true. You may want to look up If and Case functions in FileMaker Help.

          However, the presence of multiple Job Number fields in the same record suggest that you may not have the best table design for your database.

          • 2. Re: Portal Filtering
            ShanaEichleberger

            This expression didn't return any items to the portal.

            I have it set up this way since my technicians will be filling out this form and I want to pull any mileage associated with the job number to a job cost sheet to breakdown the out of pocket cost per job we perform. They fill the form out by vehicle they use each day. So have having separate record for each travel wouldn't work for the situation. On top of this if four job sites were traveled we would want the mileage to be split up evenly hence why they all need to be on one form. 

            • 3. Re: Portal Filtering
              philmodjunk

              This expression didn't return any items to the portal.

              Did you have a matching job number entered into at least one of the four job number fields?

              If you don't use any portal filter at all, what do you see in your portal?

              What is the relationship that you have defined for your portal?

              They fill the form out by vehicle they use each day. So have having separate record for each travel wouldn't work for the situation.

              I don't see why they can't from what you describe. Relationships can link travel records to vehicle records and also to Job records--each in a different related table.