4 Replies Latest reply on May 16, 2012 9:25 AM by philmodjunk

    Nested If Statement

    ShanaEichleberger

      Title

      Nested If Statement

      Post

      I want to pull a number from another time but I have two criteria that must be met. 

       

      Right now i have 

      If (Daily Reports::Jobnumber=Daily Vehicle Reports::Job Number1; If (Daily Reports::Date=Daily Vehicle Reports::Date; Daily Vehicle Reports::JOb Site Mileage Average;0);0)

      But its not returning the results.

      I just want the Job Site Mileage Average to pull if the job number and dates match

        • 1. Re: Nested If Statement
          philmodjunk

          This appears to be another form of the same issue you posted about here: http://forums.filemaker.com/posts/1c6e1a9a27?start=1&stop=10#194777

          My comments and questions there also apply here. In particular: What relationship have you defined between these two tables?

          In addition:

          Please describe your Daily Reports and Daily Vehicle Reports tables.

          What data is stored in each?

          What is the purpose to each table?

          • 2. Re: Nested If Statement
            Jason Wood

            There is nothing wrong with the nest, although you could simplify it by just using the word "and".

            If (Daily Reports::Jobnumber=Daily Vehicle Reports::Job Number1 and Daily Reports::Date=Daily Vehicle Reports::Date;

            Daily Vehicle Reports::JOb Site Mileage Average;

            0)

            This won't fix your problem because it's doing the exact same thing. The problem lies with your data. Try displaying these fields on a layout with the same context as the calculation field to see if the data is coming through as you expect.

            • 3. Re: Nested If Statement
              ShanaEichleberger

              The Relationship is between the Daily Reports::jobnumber and Daily Vehicle Reports::jobnumber 1.

               

              Daily Reports have all the information that is associated with a given job such has who was on the job their start and end times, what materials they used, the weather that day and etc.

               

              Daily Vehicle Reports are associated with a give vehicle and document mileage and what job sites were visited. 

              The purposes of the tables are to collected data from the team leaders and then associate the data with the building they are working on, or the vehicle reports are assign to the vehicle. 

              And most importantly the data is then associated with a job cost sheet by job number. This sheet contains total number of hours for a given job (this could be over multiple days depending on how large the job is), Labor cost is collected. I would like the mileage to be totaled, Any equipment cost is collect. Then once all the numbers are collected its subtracted from the quote price to see if we lose or made money.

              • 4. Re: Nested If Statement
                philmodjunk

                You appear to have this relationship:

                Daily Reports----<Daily Vehicle Reports

                Daily Reports::jobnumber = Daily Vehicle Reports::jobnumber 1

                Daily Vehicle Reports are associated with a given vehicle and document mileage and what job sites were visited.

                Which would explain why you have multiple Jobnumber fields as one record in Daily Vehicle Reports might then apply to more than one Job. But this relationship as it is currently designed does not support linking to multiple Job records in Daily Reports.

                This part of your calculation: Daily Reports::Date=Daily Vehicle Reports::Date; Daily Vehicle Reports::JOb Site Mileage Average

                Implies that you create a new record for each job for each day. Is this correct?

                If so, the following relationship would match by Date and by Job Number:


                Daily Reports::jobnumber = Daily Vehicle Reports::jobnumber 1 AND
                Daily Report::Date = Daily Vehicle Reports::Date

                And then if Daily Vehicle Reports::JOb Site Mileage Average is a summary field, placing this field on your Daily Report layout will display the average mileage for the set of records in Daily Vehicle Reports that are related to this Daily Reports table. But please note that this only links by the Jobnumber in Jobnumber 1. It ignores any data in Jobnumber 2, jobnumber 3, etc.