6 Replies Latest reply on Mar 26, 2010 7:51 AM by LeoB

    Relationship/structure error?



      Relationship/structure error?


      Fairly simple database (compared to other examples I've seen in this forum).  No numbers or calculations, all text and dates.


      Two main tables: Activity and Issue and a third table called Admin used to host the Main Menu (for navigation and notification) and all the help menus that are involved.  The Activity table is the heart of the database, and each Activity can have multiple Issues.  there are other tables linked directly to Activity and Issue, but not relavent to this question, I think,.


                  Admin                           Activity                               Issue

                AdminID-PK - - - ->  AdminID_FK

                                                    ActivityID_PK - - - - - -> ActivityID-FK


      Based on the Admin table is a Main Menu that has links to all the tables, data input layouts, and reports.  All work fine.  There are no records stored in the Admin table.


      However, on that Main Menu I want it to run OnLayoutLoad a series of searches in the Activity and Issue tables with the results inserted in global fields.  The global fields represent various numbers of records in different categories and present them on the Main Menu.  This isn't working so hot.  The search should run, and then the found count inserted in a specified global field.  I've tried putting the global field in the Admin table and each pertinent table, but with mixed results.


      I tried basing the Main Menu on the Activity table rather than Admin, but still having problems.  I want the users to continually go the Main Menu for navigation among the layouts, and each time the Main Menu should refresh all the the numbers.


      Should I have some other relationship than the one above?  I tried making a join table (between Activity and Issue) and hosting the Main Menu there, but the same problems.  Some global fields are updated, and others are not.


      In the script to run OnLayoutLoad I have a series of Perform Find [Restore]/Set Field functions.


      I know I'm doing something basically wrong, and suspect it's in the relationship, but reading Phil's paper on TOs is to me a lot like trying to translate an ancient Greek text using an Albanian dictionary.


      Any guidance?








        • 1. Re: Relationship/structure error?

          I'm fairly new to FM too but have built some nice, complex relationships. Until someone more competent comes along I might be able to help.


          What I can't see in your issue is a description of the actual problem. I gather that you want people to go to a common layout with a menu of sorts and that someplace, perhaps next to titles, subjects, categories or similar you want the number of issues in your database shown here. Is this correct?  What are you seeing?

          • 2. Re: Relationship/structure error?



            The problem is that the scripted searches do not work on the Issue table.  Even though it "sees" the table, I'm not getting back the expected results.


            Actually, there are a total of 8 scripted searches that I want to run OnLayoutLoad that will populate fields on the Main Menu to show how many Activity records there are by different categories (years), how many Issue records (by year), Issue records with certain attributes (High Risk - liable to fail in near future and overdue suspense dates), etc.



            • 3. Re: Relationship/structure error?

              More details on your searches would help.


              It is possible that some portals on your main menu could be set up to display the data you want without performing any searches at all. You might also be able to use the Copy All Records/Request step to move the data displayed on a layout after perfoming a find into a single field such as one of your globals.


              Those are just some possible solutions, but can't recommend either of them nor post a more detailed example without knowing more about what you are tring to do.

              • 4. Re: Relationship/structure error?



                >>It is possible that some portals on your main menu could be set up to display the data you want without performing any searches at all.<<


                Well, I don't want to show actual records on the Main Menu, only the number of records in certain categories.  Also, portals won't work on the Main Menu as the Admin table does not host records, only the Activity and Issue tables hold actual records.


                As I said earlier, the Main Menu is primarily for navigation to all the layouts, but I had hoped to also use it for notification to let the user know how many records are in various categories.


                Here's the basic file structure again:


                          Admin                         Activity                            Issue

                          AdminID-PK - - - ->  AdminID_FK

                                                              ActivityID_PK - - - - - -> ActivityID-FK


                Let me give you one example.


                A part (one of seven finds) of a script that runs OnLayoutLoad for the Main Menu (Admin table) is:


                Perform Find [Restore]     Issue::Status: =[Overdue]

                Set Field [Admin::gOverdueIssueRecords; Get ( Found Count )]   Global Field


                There is a button on the Main Menu that takes the user to a list layout based on the Issue table that contains all overdue records.  Next to the button is a field for gOverdueIssueRecords that I want to just show the number of overdue issue records in the queue, if any.


                When entering the Main Menu layout from 26 of the 27 layouts in the file, gOverdueIssueRecords is not updated.  However, if I click on the button to go to the list layout of overdue records and then return to the Main Menu, it is now updated.


                I have also tried basing the Main Menu on the Activity table (which works fine overall) and which hosts the master key for all records, it does the same thing.


                This method is just what I could figure out from working with FM thus far.  There are, I'm sure, better ways to do it -- obviously, as my way doesn't work.


                Any recommendations greatly appreciated.





                • 5. Re: Relationship/structure error?

                  I think your current script should read like this:


                  Freeze Window

                  Go to Layout ["Issue" (Issue)]

                  Perform Find [Restore]     Issue::Status: =[Overdue]

                  Set Field [Admin::gOverdueIssueRecords; Get ( Found Count )] 

                  Go To Layout [Original Layout]


                  A nonscripted approach:

                  Define a calculation field in Admin, cOverduekey to return the text: Overdue.


                  Create a second table occurrence of Issues and link it to Admin as

                  Admin::AdminID = OverdueIssues::AdminID AND

                  Admin::cOverdueKey = OverdueIssues::Status

                  (OverdueIssues is a second Table Occurrence of Issues.)


                  Now define a calculation field in Admin as Count ( OverdueIssues::AdminID )

                  and it will display a count of the number of overdue issues without any scripting needed.

                  • 6. Re: Relationship/structure error?

                    WooHoo!  (That word is strangely missing from the FMP lexicon.)


                    The script got it -- I wasn't using the Freeze Window and Go to Layout functions properly.


                    I need a lot more experience to try the non-scripted approach, but will try that in the future.