Have you thought of using a separate table for Issues and relating the Issue to the Inspection using an Inspection_id key?
That way, you can have any number of issues per Inspection (not limited to 10) and use a Portal of Issues on the Inspection layout.
Well -- I never thought I'd use portals, so I didn't really read that part of the book closely (The Missing Manual). I also didn't think of putting the issues in a separate table as the issue always and only pertains to the one inspection. From all the posts I've read here and the book I thought that a separate table would only be needed if the contents of the table applied to more than one other table. In fact, in the first database I did I started out with nine tables, but on advice from this forum I found that I really only needed four. But then, I didn't use portals in that one.
Guess I'll go back and read it more closely.
Perhaps you can look at it the other way around. Since there is only one Inspection record, you could create a table for Inspections with that one record. Then create a table of Issues and use a layout of it instead. The Issues layout would show one issue per record but you can add the basic Inspection fields at the top of this layout.
You're gonna make me think, aren't ya.
Hurts doesn't it?
Well, I must be doing something basically wrong, but danged if I can figure out what.
I restructured into two tables: Inspections and Issues.
I connected InspectionsPK to IssuesFK.
I put a button on the Inspections layout to go to the Issues layout.
I created a portal on the bottom of the Inspection layout to the Issues table and selected the fields to display. But, nothing happens!
Now, I think the problem is that the new issues are not being tied to the inspection PK, but I can't figure out how to do that without physically typing it in.
It may be just a typo but you should have connected InspectionsPK (in the Inspections TO) to InspectionsFK(in the Issues TO).
The Issues table should have its own primary key (IssuesPK) and a link (foreign key) to the Instructions table, InspectionsFK.
Hope this resolves the problem.
Open Manage | Database | relationships
Double Click the line linking your two tables (table occurences).
Select the "Allow creation of records via this relationship" option for Issues.
Now, when you enter data into the issues portal, the matching value from InspectionsPK is automatically entered into the new record's IssuesFK (or InspectionsFK if you linked a field of that name as Jade suggested while I was typing this.) field to link the new issues record to the current Inspections record.
Yep, that's right. It was (another) mistake.
However, I did not have IssuesID_PK connected to IssuesID_FK in the inspection table. I did that. No change.
What I want to do is click on the "new issue" button on the inspection table and go right to a new record in the issues table that is populated with the record number of the inspection table that it comes from. I used a little script on the button that says to go to the issues layout and go to new record, BUT the record number does not fill in at the top.
The inspection record number is critical, I think, as it would tie all the issues to the appropriate inspection record.
By the way, when I created the portal on the inspection layout, it set up fine -- showing the issues table as related and I selected the appropriate fields.
I dunno what time it is where you are, but it's late.
Attach your button to this script:
Set Variable[$InspID; Inspections::InspectionsPK]
Go To Layout [Issues]
Set Field [ Issues::InspectionsFK; $InspID]
This assumes you are linking your tables by Inspections::InspectionsPK = Issues::InspectionsFK
It's getting late here too so best to tackle this with a clear head in the morning.
Just a point to sleep on:
Since you have a one-to-many relationship between Inspection and Issues, you do not need the IssuesID_FK in the Inspections table.
Phil and Jade:
Still stumped. This morning I created a new, minimal database to see if I could just get the portal function working, but no joy.
Two tables: Inspections and Issues
The InspectionsID_PK in Inspections is related to InspectionsID_FK in Issues (http://www.imgplace.com/viewimg690/8998/72relationship.jpg)
The Inspections layout (http://www.imgplace.com/viewimg690/7585/33inspectionslayout.jpg) records the basic information information about the inspection. Each inspection can have many issues discovered, each of which needs to be fully described and then updated and tracked until resolved. However, each issue pertains to only one inspection, but one inspection can have many issues.
I was trying to put a portal in the Inspections layout to retrieve limited information for each pertinent issue. There would be a New Issue button to allow a new issue to recorded in the Issues layout (http://www.imgplace.com/viewimg690/6777/23issueslayout.jpg), which should then immediately show up in the portal on the Inspection layout. Then there would be a button in (or next?) to the portal entry on the Inspections layout to allow going directly to the Issue layout to view the complete details of the specific issue or to modify the issue.
I used the script that Phil recommended earlier (http://www.imgplace.com/viewimg690/9817/89newissuescript.jpg). No luck.
Also changed the relationship to allow creation of new records via the InspectionsID_PK - InspectionsID_FK relationship (http://www.imgplace.com/viewimg690/8308/13relationshipedit.jpg).
When I click the New Issue button, a new issue is created, but the InspectionsID_FK is not populated with the serial number of the inspection. This is necessary to make the portal work.
I must be doing something basically wrong, but after almost 10 hours of working at it I'm still at the same place.
I can send the file itself, but not sure how to do that. It's quite small.
"I used the script that Phil recommended earlier No luck."
Your first and last steps of your script are incomplete:
Set Variable [$InspectionID_PK]
Set Variable [$InspectID; Value: Inspections::InspectionID_PK]
Set Field [Issues::InspectionsID_FK]
Set Field [Issues::InspectionsID_FK ; $InspectID ]
The first line captures the ID value of your current Inspection record in a variable.
The last line refers to this variable to copy that data into the newly created Issues record so that it will match the current Inspections record.