6 Replies Latest reply on Sep 16, 2014 9:55 AM by philmodjunk

    Comparing field values in a calculation

    b24mike

      Title

      Comparing field values in a calculation

      Post

      I'm trying to get a value displayed in a portal based on comparisons of values in 2 different fields in 2 different tables.  The value displayed comes from table 1 and will only be displayed when Table 1's field -1 value is the same as Table-2's field-1 value and Table 1's field-2 value is the same as Table 2's field-2 value.

      I'm trying to display a number assigned to a report when the date and aircraft number coming from 2 different tables correspond with the date and aircraft number in the second table.  The second table is the one that has the report number to be displayed.

      I'm assuming that I'll need some sort of a loop or case structure in the calculation when dealing with the third table.

      Example: Table 1 consists of a date and tail number for a mission flown in a portal.  Table 2 contains the date, tail number and report number for a plane shot down. When entering data into the Table 1 portal, I would like the report field to be automatically populated after entering the aircraft details.

      I'm new to FileMaker but have worked for years as a software engineer.

        • 1. Re: Comparing field values in a calculation
          philmodjunk

          I can't tell from your description whether you have 3 tables or two.

          Do you have table 1, table 2 and a "report" table?

          Or just two tables with a "report  field" defined in table 2?

          If you just need to link table 1 to table 2, this is very straight forward as you can use both tail number and date fields as match fields to match a record in table 1 to a record in table 2.

          • 2. Re: Comparing field values in a calculation
            b24mike

            PhilModJunk:

            I have an aircraft table listing tail numbers and a missions table that has the dates each airplane flew. A third table contains the loss reports by date and tail number.  This third table also has the report number which I want to have displayed through a portal. I've attached my relationship design.  In the JoinMissionsAircraftTable is the field (circled in blue) called Lost that will display the value circled in red in the MACRsTable.  The portal displays data primarily from the JoinMissionsAircraftTable and I would like to add the MacrNo field from the MACRsTable.

            I'm very new to working with FileMaker and Guy Stevens has been a big help in getting me this far.  Any assistance you could lend would be greatly appreciated.

             

            • 3. Re: Comparing field values in a calculation
              b24mike

              This is a shot of myportal where I enter my values.  The next to the last line in the left portal (42-7534) is the aircraft in question.  On 12/20/1943, the Patterson crew flying that plane was shot down.  A Missing Air Crew Report (MACR) number 6343 was created.  What I was hoping to do was to automate the insertion of the MACR number when the plane is added to the mission list.  The MACR table contains all 109 aircraft lost during the war.

               

              • 4. Re: Comparing field values in a calculation
                philmodjunk

                And on what table occurrence will the layout where you want this portal be based?

                It looks like a task that would be greatly simplified if you copied AirCraftTable::ID into a field in the MACRsTable so that you could link directly by your primary key. That would let you make a new occurrence of MACRsTable and link it to JoinMissionsAircraftTable.

                 

                • 5. Re: Comparing field values in a calculation
                  b24mike

                  Phil:   Based on your comment I made the following changes

                         
                  1. Added MacrTable::AcftIdFk and linked it with AircraftTable::Id
                  2.      
                  3. The resulting value is to be displayed in the portal with the MissionsTable layout pulling values from MacrTable::MacrNo and populating the MACR field for the appropriate aircraft in the JoinMissionsAircraftTable::MACR (see portal image above).

                  I'm a little confused on how all this works.

                   

                   

                  • 6. Re: Comparing field values in a calculation
                    philmodjunk

                    I don't see where you did this:

                    That would let you make a new occurrence of MACRsTable and link it to JoinMissionsAircraftTable.

                    And keep in mind that you have to set up a method that puts the correct value into MacrTable::AcftIdFk