7 Replies Latest reply on Apr 19, 2012 2:33 PM by sastickf

    Data Separation Drama

    PeterWindle

      Hello all, I'm building a solution that is done using a data separation model... I have relationships in both the GUI file and the DATA file...

       

      I have a bunch of relationships in a child record to show related data from another table, matching between the child table uses calculation fields to match values in another reference table, easy enough... however...

       

      The calculation fields pull data from parent files. I found that when I changed values in the parent table fields, thus changing the values in the child fields, the matching values from the other table would NOT refresh, it would still show values from a previous relationship. Ba-bow!

       

      Now, I have just spent the last 5-6 hours trying to refresh windows (flushing caches), preview/browse etc, only to discover that the problem needed to be resolved by doing a refresh window from the DATA file, NOT the GUI file, thus forcing the calculations to refresh.

       

      Really, calculations should never need to be refreshed, however, FileMaker needs to refresh values in fields, but the moral of this story is REFRESH FROM THE FILE that the fields reside in!!!

      Doing the refresh from the GUI file does NOTHING in this case, even though the DATA file is hidden, it still needs a refresh script called by the GUI file for the values to update.

       

      Yippie. FileMaker is so much fun, ummm, not really, I'd rather be asleep now. It's not yet midnight, but not far from it!

        • 1. Re: Data Separation Drama
          datamate

          I have used mostly separation models and am a little flummoxed with your issue. I too have calculations in relationships and have never experienced this.. Even though we will lack context could you post the calculation for us to see

          • 2. Re: Data Separation Drama
            Stephen Huston

            I'm with datamate on this one. I've never had a refresh issue with relationships in a data-seperation file system except in the GUI file with portal lists. In those cases, a window refresh with flush cache has fixed things, though sometimes a Go To Layout (different layout) followed by Go to Layout (original layout) had to be done to force it. So we do need more details.

            • 3. Re: Data Separation Drama
              sastickf

              I do not know what Peter's scenario is, but I have had a situation which sounds somewhat similar and have been watching this discussion in hopes of finding an answer to my problem. 

               

              In my DATA files, I have the tables 1) Checkbook Line Items and 2) Sub Accounts.  In the Sub Accts table, I created (10) global date fields which the user can manually specify (5) date ranges to look at the spending activity for all the sub accounts for the specified date ranges.  (The user changes these date ranges from the GUI file.)  In the Sub Accounts table, I have created (5) calculation fields to sum up the checkbook line amounts for spending in that particular sub acount for the (5) specified ranges.  In addition, I created (5) Summary fields to summarize the calculation fields.

               

              The problem I am experiencing is when the user manually changes the date ranges in the globals (from the GUI file), the calculation fields update, but the summary fields do not.  In order to get the summary fields to update, I have to open up the underlying DATA file (Manage Database) & close it again.  This is the only action that seems to reset the relationships, and thus the summary field amounts.  I have tried changing layouts (but only to a layout based on the same table occurence) & refreshing the window to get the Summary field amounts to update, but this does not resolve the problem.

               

              Let me know if you need a more specific example to understand the scenario I sought to describe....

               

              Thanks!

              • 4. Re: Data Separation Drama
                Stephen Huston

                Are you using the global date-ranges to change to actual relationships (in which case you need separate relationships for each range), or are you filtering the portal(s)?

                 

                Changing key fields (even globals) should work if you are using the dates at the relationship key field levels and matching them to indexed values in the portal table.

                 

                On the other hand, filtering of portals does not change the underlying relationship, so summary fields based on the relationship do not change to reflect portal filters.

                • 5. Re: Data Separation Drama
                  sastickf

                  The layout I am working from is a listing of the sub accounts, therefore I am not using portals.  I have the (5) summary fields within the Header part of the layout.

                   

                  My date fields are global & I have (5) separate relationships created for each of the calculation fields (using the global date fields at the key field level).  I do not know if the date field in the Checkbook Line Items table is indexed, I need to check that!

                  • 6. Re: Data Separation Drama
                    Stephen Huston

                    More things come to mind:

                    1. If you are doing this in Form view, try modifying your layout so that all objects are on the body rather than in the header., and deleted the uneeded header part. (There have been some instances reported where header fields do not play nicely with found sets, though the problems generally appeared when in list view layouts.)
                    2. You indicate the layout uses the subaccounts as the base table. Are the globals in that table or elsewhere? Yuu may be better off using a portal or just using the table in which the globals reside, as I assume that is where the calculations sums reside.
                    • 7. Re: Data Separation Drama
                      sastickf

                      Thanks for your help Stephen.  I was just able to look at the solution.  The date field was indexed and I was working in table view.  The date global fields do reside in the subaccounts table.

                       

                      I changed my script so that when the date ranges are reset (YTD or 5-year), it switches to another layout based on a different table occurence & then returns back to the original layout.  I thought I had tried this previously, but I must not have because it resolved my issue!  This seems to go back to your 3/30/12 response/suggestion... I just never considered the importance of switching to a layout based on a different table occurence.