5 Replies Latest reply on Jun 13, 2014 2:28 PM by philmodjunk

    Problems with Display in a 2nd Tab Layout

    gregdc

      Title

      Problems with Display in a 2nd Tab Layout

      Post

           Layout has two tabs   Tab A and Tab B.   Tab A is the primary tab.  Tab B is portal that contains calculated fields

           Tab B has OnObjectEntry script to do the calculations.  Tab B has a "refresh data" button.  Both run the same script 

           Problems:

           1.  Data does not up update when entering the tab.  It retains the data from the last time.   The button will refresh the data correctly.   Why would this happen?

           2.  When the script is run from the button, at script ends it always returns to Tab A.   How do I get it to return to Tab B?

            

           Thanks again for the help.

           GregDC

        • 1. Re: Problems with Display in a 2nd Tab Layout
          philmodjunk

               1) Why wouldn't it happen? Simply entering this tab would not automatically run your script to refresh your data.

               But there is much that you do not describe about the design of your database. A portal indicates that you have a relationship, yet you do not describe that relationship. You do not explain exactly what you mean by "data from the last time". It's quite possible that with design changes made to your database, that no such "refresh" script would be needed to recalculate values in your portal.

               2) Does your script change layouts and then return? that will cause the default panel of your tab control to automatically become the front tab panel. You can use the Name box in the Inspector to give each tab panel a unique object name. Then Go to Object in a script can specify a tab panel by object name and cause it to become the front tab panel, thus you can return to the original layout and then use Go to Object to bring up Tab B.

          • 2. Re: Problems with Display in a 2nd Tab Layout
            gregdc
                 The script is complicated, but here goes:
                  
                 Tables:
                  Organizations (PK OrginizationID)
                  OrganizationScorecard (PK OrginizationID) ScoreCardYr, Factor1, Factor2...Factor8, FactorAvg(Calc field)
                  OrganizationScorecardDisplay (PK OrginizationID) RowID, Column1, Column2 ... Column7, FactorChg(Calc field)
                  
                 Organization have 0 to Many OrganizationScorecard
                 Organization have 0 to 1 OrganizationScorecardDisplay
                  
                 Layout:
                  Tab 1 Organization Suplimental Information
                  Tab 2 Organization Scorecard - Portal for OrganizationScorecardDisplay
                  
                 Requirement:
                  For a give Organization Display the latest 5 years of OrganizationScorecard information such that
                   the Name of the Factors are listed vertically, and the scorecardYr list horizontally.
                  
                 Script:
                  OrganizationScorecardSetup: tied to OnObjectEntry and "refresh table" button on layout
                  - Clears OrganizationScorecardDisplay table of all records with OrganizationID
                  - Clear all varibles
                  - Move OrganizationScorecard information to varibles
                  - Move varables to OrganizationScorecardDisplay
                  - Goto Object OrganizationScorecard
                  
                 Problems:
                  1. When Organization Scorecard tab is opened the display is blank or shows prior display information
                     A. When Tab first opened no information is displayed 
                     B. If the refresh button is clicked information will display.
                     C. When a new OrganizationScorecard record is added ("Add New Scorecard") and refresh button is clicked
                          the information in OrganizationScorecardDisplay does not change
                  
            • 3. Re: Problems with Display in a 2nd Tab Layout
              philmodjunk

                   Note PK OrginizationID (Primary Key, Organization ID), makes sense as a field name in your Organizations table. But naming the Foreign keys in the other two tables with "PK" is confusing as these are not primary key fields, but apparently the foreign key fields used to link them the Organizations table.

                   Best guess is that your relationships are:

                   OrganizationScorecardDisplay>--------Organizations-----<OrganizationScorecard

                   You are apparently using a script to gather up data from OrganizationScorecard to put into different fields and records of OrganizationScorecardDisplay. That is very inefficient and slow.  Calculation fields using ExecuteSQL() could pull the needed data from OrganizationScorecard without needing a script to do so.

                   And it would appear that your display difficulties are two fold:

                   
                         1. When Organization Scorecard tab is opened the display is blank or shows prior display information
                   
                            A. When Tab first opened no information is displayed
                   As previously stated, clicking a different tab in a tab control does not automatically perform a script (such as that performed by clicking the refresh button) unless you have specifically set up a script trigger to perform that script, both OnObjectModify and OnPanelSwitch are triggers that in FileMaker 13, can perform a script when a different panel of a tab control or slider is selected.
                   

                        C. When a new OrganizationScorecard record is added ("Add New Scorecard") and refresh button is clicked the information in OrganizationScorecardDisplay does not change

                   Since your relationships still link to the same set of related records, your script gathers the same data and it is displayed in the same manner as the original score card record. What do you expect to see in the new scorecard that would be different from the previous scorecard?


                    

              • 4. Re: Problems with Display in a 2nd Tab Layout
                gregdc

                     Thanks yet again for the reply

                Note PK OrginizationID (Primary Key, Organization ID), makes sense as a field name in your Organizations table. But naming the Foreign keys in the other two tables with "PK" is confusing as these are not primary key fields, but apparently the foreign key fields used to link them the Organizations table.

                     You are correct about the keys.  In reality the two FK are OrgID not OrganizationID

                Best guess is that your relationships are:

                OrganizationScorecardDisplay>--------Organizations-----<OrganizationScorecard

                     Yes you have guessed correctly.  The unique key for OrganizationScorecardDisplay is OrgID-RowID.  OrganizationScorecard has a unique key of OrgID-ScorecardYr  

                      

                You are apparently using a script to gather up data from OrganizationScorecard to put into different fields and records of OrganizationScorecardDisplay. That is very inefficient and slow.  Calculation fields using ExecuteSQL() could pull the needed data from OrganizationScorecard without needing a script to do so.

                     Here I am afraid you lost me.   I have no experience with using ExecuteSQL() for anything this complex.   Like you said in another message; there is no easy way of doing a pivot of a dataset.  And that is what I am doing.   If I were to do a portal on just the OrganizationScorecard it would be something like this

                     Year      Factor 1   Factor 2   Factor 3 .....  Factor Avg

                     2014        2.5            3.0            1.9  ...........     2.3

                     2013        2.6            2.9             2.0  ..........     2.0 

                     2011 .....

                     2010 .....

                     Change   -.1            +.1             -.1                 +.3

                      

                     What they want is

                                       2010     2011   2013    2014   Change

                     Factor 1     ....          .....        2.6        2.5        -.1

                     Factor 2    .....          .....        2.9        3.0      +.1

                     ......

                      

                     So how would you use SQL to retrieve the information?    Row 1 is are the years, Row 2 the Factor 1 (for the last 5 years), etc

                     I hate to ask for any example because it is not your job to write my programs.  Maybe there is an example somewhere you could tell me about?

                      

                • 5. Re: Problems with Display in a 2nd Tab Layout
                  philmodjunk

                       To restate the relationships but with those match fields:

                       OrganizationScorecardDisplay>--------Organizations-----<OrganizationScorecard

                       Organizations::PK OrginizationID = OrganizationScorecard::OrgID-ScorecardYr
                       Organizations::PK OrginizationID = OrganizationScorecardDisplay::OrgID-RowID

                       Note for the future:  A screenshot of Manage | Database | Relationship--if a narrative explanation of it is included, can be a much better way to describe your data model.

                       Without the portal, I'd use filtered one row portals in a list view layout based on OrganizationScorecard to produce the requested cross tab display of this data. But you can't place a portal inside the row of another portal. I mention this option anyway as redesigning your layout to not use a portal in this fashion may be an alternative worth considering as it avoids the need for SQL queries

                       Calculation fields defined in OrganizationScorecardDisplay can be placed in a portal row so that is were ExecuteSQL() becomes a real problem solver here, but only if you are willing to dive into the complexities and peculiarities of this function and the SQL needed to make it work. But it is difficult to help you with that given the fact that I don't have a clear understanding of your data model, in particular the structure and function of your OrganizationScorecardDisplaytable and that is the table from which these SQL queries can extract data.

                       I can point you to a pair of resources that can help people master the use of this function:

                       There's a PDF on SQL that you can access via FileMaker Help | Product Documentation | More Documentation: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

                       And SeedCode has produced a tool that helps you create a working SQL query without getting tripped up over syntax errors that would otherwise produce the maddeningly uninformative ? result: http://www.seedcode.com/cp-app/ste_cat/sqlxfree

                       There are also numerous online resources that can help you master SQL in a general format, but be advised that every database system that uses SQL, employs a different "flavor" of SQL. The basics of a SELECT query--the only kind you can use with ExecuteSQL() will be the same, but specific functions, and syntax details to how you set up expressions within the query will vary from database system to database system so you'll want to refer the FileMaker's reference doc to stay on top of those key details.