1 2 Previous Next 23 Replies Latest reply on Sep 28, 2012 9:17 AM by philmodjunk

    Summary of fields in different table?

      Title

      Summary of fields in different table?

      Post

           This seems pretty basic, bu I am missing something.  I am having some trouble getting a summary of some values after I move them to a different table.  (We are trying to narrow down one table that has grown to 700+ fields.)  This new table is a 1-to-1 relationship:  A::PK = B::A_FK.

           When I put the summary fields in the new table, that only gets me the values for that one record, of course.  But even putting them back in the original table, I seem to only be getting the value from one specific record (doesn't appear to be the first record, necessarily, though). 

           So I have a layout where I have a found set from A (say 10 records), and the layout is showing some values from table B for each of those records.  Simple enough.  But I would like to get a summary of those B values on this layout (which is based on table A). 

           Such as:

           A1     B3     B4     B5    

           A2     B3     B4     B5

           A3     B3     B4     B5

           sum  (B3)  (B4)  (B5)

           Do I have to copy the B values back into A to get this to work?

           Thanks,

           - J

        • 1. Re: Summary of fields in different table?
          philmodjunk

               Summary values can be computed with an aggregate function such as Sum() or with a summary field. What method are you using? With either, the relationship will control the total returned. If you want an aggregate value such as a sum from a related table, you need a relationship that matches to multiple records.

          • 2. Re: Summary of fields in different table?

                 The pre-existing form was a summary field.  Works great when all the data is in the same table as the found set becomes the summed up values.  But now I have a found set from Table A, and I want to sum related values in Table B.  The relationship is 1-to-1 between the two tables; there is only one related record in B for each record in A.  I need something that will give me related results across the found set, and not just (apparently for the summary field anyway) one record on the B-side of the relation.

                 Here's a screen cap showing one layout where this issue crops up.  The totals at the bottom don't change based on the sort order; perhaps there is a background ordering that it is using where the values returned are just the first record's values.

                 Attached is a screen cap showing three things:  the layout (in browse mode), the RG, and the relationship defnition window.

                  

            • 3. Re: Summary of fields in different table?
              philmodjunk

                   If you are using FileMaker 11 or older, you may be better off not splitting your tables like this. If you are using FileMaker 12, you may be able to use ExecuteSQL to compute the needed total(s).

                   As I stated previously, you need a relationship that matches the set of related records that corresponds to your found set of records you have pulled up for your report.

                   Here are some options to consider:

                   Base your report layout on the other table. You can still perform your find on this layout with the same criteria and the one to one relationship will pull the data you need from the first table into your layout. Since you are now on the table where this data resides, your summary fields should work correctly. This won't be a workable option, however, if you have multiple related tables and need different summary values from each.

                   See if you can define a relationship that matches to all the records in the second table that meet the same criteria specified in the find that pulled up your report's found set. This can be anything from simple, to complex to nearly impossible depending on the type of find criteria you need to use and how much variation there is in the type of criteria specified from one report to the next.

                   Try using a filtered one row portal with a summary field from the related table placed inside the portal row. The borders and fill of the portal can be made transparent to make this field look like any other field on your layout. Use a combination of relationship (may need to use the cartesian join operator to match to all records), and portal filter expression to limit the data summarized to a group of records that corresponds with your found set. A portal filter expression may be more flexible than just a relationship--which is where this option may work for you.

              • 4. Re: Summary of fields in different table?

                     Hmmm...seems to work OK if I re-base the layout into the new table (B), and put the summary in the new table (B). 

                     But it seems like it should work from the original table, too. 

                • 5. Re: Summary of fields in different table?

                       Ah, but then it doesn't respect the found set when moving from one layout (based on Table A) to this layout (now based on B).  I can do a GTRR though, and select 'all records in found set'.  That seems to work pretty well.

                  • 6. Re: Summary of fields in different table?
                    philmodjunk

                         Why use the GTRR with the "match found set" option when you can simply perform the find on the other layout? GTRR with this option can often trigger a significant delay in bring up the found set on the second layout.

                    • 7. Re: Summary of fields in different table?

                           Hehehe.  We had some cross posting going on.  I hadn't refreshed my view to see your 10:05 reply before posting my 10:10 one.  You mentioned a solution that I came up with as well:  re-basing the layout into the new table.  :)

                           I had also pondered on using ExecuteSQL in some manner, but was a bit stymied by how to define the WHERE clause to match the current found set, at least without creating some much-longer-than-needed script to cycle through things.  (Oh, yes, I am using FM12.)

                      • 8. Re: Summary of fields in different table?

                             Why use GTRR?  Well, it was the first thing I found in messing around and it worked.  :)  No other good reason.

                             How would I repeat the find?  This is not a predefined or scripted found set.  I am dealing with one that an end user manually creates to list specific things from Table A (in a Table A layout, of course).  That layout then has buttons leading to the layout showing the associated cost values (which are in B) for those items from Table A.  GTRR was the first thing that I thought of that would reproduce the found set from A on the layout in B.

                             If there is a better work-around for repeating the found set, I am interested in hearing about it.  Especially since this effort is an attempt to improve overall performance.  I don't want to implement something to fix problem A only to create problem B.  :)

                        • 9. Re: Summary of fields in different table?
                          philmodjunk

                               Note, with a one to one relationship, you can add fields from both tables to the same layout and they will look and function (with very few exceptions) exactly as though all the fields are from the same table.

                               Just do one find on a layout based on Table B. Since there is a one to one relationship, this layout can look and function exactly like your Table A based layout. The user won't know the difference. They can pull up this layout, enter find mode, perform the find and the only difference is that your report's summary fields now work when they didn't the other way around.

                          • 10. Re: Summary of fields in different table?

                                 Ah, so continue the process of updating the layouts to use the new Table B as the basis instead of Table A, right?

                            • 12. Re: Summary of fields in different table?

                                   OK, I ran into another snafu with this that has reopened the summarize-data-in-another-table wounds.

                                   Since I am using the 'Create records via this relationship' setting on the 1-to-1 relation, if I base a layout on Table B then records from Table A that haven't explicitly had data entered into Table B fields located on a Table A layout, there won't be those records shown in the Table B layout.  They don't exist yet.  So when summarizing things on a layout based on B, then there could conceivably be missing records.  The summary will work as expected, though.  It seems that this could be confusing to the end user:  "I know I created record XYZ, why doesn't it show up in the list?"

                                   Now, while it makes some sense to me as the developer that it wouldn't show up, that isn't so obvious to the end user.  And if they would just go back to a detail-view-layout and enter some data for that A-record, then it would show up in the B-summary-layout.  So I am inclined to leave this summary layout based on A, just so that all records will show.  So I am now stuck with trying to summarize records in B from A again.

                                   I like your suggestions of a filtered portal, but I am trying to figure out how to make this work without usurping FM's built in Find function and scripting it all.  That is really where the issue lies.  If a user searchs for parts = "blue" via the built in Find function, how do I get that information to affect the relationship?  I need some way of pulling record IDs from the current found set (regardless of how it was generated), and then searching against any of those in table B to summarize the values from there.

                                   Not such a tall order, is it?  :)

                                   Frankly, FileMaker IS already doing that; it shows me all the numbers that I need on one layout, pulling information from Table B based on the records found in table A.  It just needs to expand it's thinking a bit more and then allow manipulations on what is already being displayed without having to revist the context and the relationship.  The record-centric system is already pulling down all of the data for the entire record referenced in table B anyway, why can't it operate on that?

                                   -- J

                              • 13. Re: Summary of fields in different table?
                                philmodjunk

                                     It's a well known limitation of FilMaker that you can't set up a "left join"--which is what you are describing here. The simplest solution is to automate the process so that a record in B is always created when a new record in A is created. Either that or don't split your table in two like this in the first place.

                                • 14. Re: Summary of fields in different table?

                                       Yeah, I am looking into options for doing that (having a script run on record creation in A).  That seems to defeat the purpose of the 'Create records via this relationship' setting, though.  The issues stems from nailing down all the points where an A record might be created.  Ah well.

                                       Not splitting the table would be nice, yes, but this table is 700+ fields wide and we are trying to pare that down to improve performance (again, because of that record-centric world view).  And many of those fields are rather ancillary data, that makes sense to have off somewhere else.

                                        

                                       -- J

                                  1 2 Previous Next