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.
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 OrganizationScorecardOrganization have 0 to 1 OrganizationScorecardDisplayLayout:Tab 1 Organization Suplimental InformationTab 2 Organization Scorecard - Portal for OrganizationScorecardDisplayRequirement:For a give Organization Display the latest 5 years of OrganizationScorecard information such thatthe 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 OrganizationScorecardProblems:1. When Organization Scorecard tab is opened the display is blank or shows prior display informationA. When Tab first opened no information is displayedB. If the refresh button is clicked information will display.C. When a new OrganizationScorecard record is added ("Add New Scorecard") and refresh button is clickedthe information in OrganizationScorecardDisplay does not change
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:
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 informationA. When Tab first opened no information is displayedAs 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?
Thanks yet again for the reply
To restate the relationships but with those match fields:
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.