1 2 Previous Next 15 Replies Latest reply on Jan 29, 2010 12:26 PM by LeoB

    Designing layout question



      Designing layout question


      Been using FMP10 for a couple months.  Did one fairly simple database and got it working fine.  I'm starting on another one -- more ambitious -- but I'm having a problem on how to do the layouts.


      This is a database to record technical inspections.  So far I think it will be just one huge flat-file database as all the information pertains to only one inspection.  Drop-down lists work fine in the preliminary fields.  My problem is how to record and lay out the discrepencies.  Most inspections find two or three problems at most, and the database is to document the problems and then run reports to determine how many are fixed, their status, etc.  But, to be on the safe side I've set up the table to handle up to 10 problems per inspection.


      In the data entry layout I've tried using tabs, but the area within the tab doesn't leave enough room to document everything (at least on one layout screen).  And, 10 tabs on the screen looks really bad.  It could work on my 24" desktop monitor, but this will have to run on a laptop.


      I've also tried a separate layout for each problem (I call them "issues") linked to the main layout.  That works, but I can't figure out how to update the main layout to show how many issues there were originally and the status of each.


      I may be out of my depth here, but was wondering if anybody had any advice on the best way to do this.



        • 1. Re: Designing layout question

          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. 

          • 2. Re: Designing layout question



            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.



            • 3. Re: Designing layout question
                 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.
              • 4. Re: Designing layout question

                You're gonna make me think, aren't ya.



                • 5. Re: Designing layout question
                     Hurts doesn't it? 
                  • 6. Re: Designing layout question

                    Maybe this link ought to be pinned to the forum:





                    • 7. Re: Designing layout question

                      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.


                      Any ideas?

                      • 8. Re: Designing layout question

                        Hi Leo,


                        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. 


                        • 9. Re: Designing layout question

                          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.

                          • 10. Re: Designing layout question

                            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.



                            • 11. Re: Designing layout question

                              Attach your button to this script:


                              Set Variable[$InspID; Inspections::InspectionsPK]

                              Go To Layout [Issues]

                              New Record/Request

                              Set Field [ Issues::InspectionsFK; $InspID]


                              This assumes you are linking your tables by Inspections::InspectionsPK = Issues::InspectionsFK

                              • 12. Re: Designing layout question

                                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. 


                                • 13. Re: Designing layout question

                                  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.


                                  Any ideas?



                                  • 14. Re: Designing layout question

                                    "I used the script that Phil recommended earlier No luck."


                                    Your first and last steps of your script are incomplete:


                                    Set Variable [$InspectionID_PK]


                                    Should be:


                                    Set Variable [$InspectID; Value: Inspections::InspectionID_PK]


                                    Set Field [Issues::InspectionsID_FK]


                                    Should be


                                    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.

                                    1 2 Previous Next