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.
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.
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.
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.
Phil: Based on your comment I made the following changes
- Added MacrTable::AcftIdFk and linked it with AircraftTable::Id
- 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.
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