7 Replies Latest reply on Apr 13, 2011 9:08 AM by philmodjunk

    how to link summary data to individual records

    PhiloCalhoun

      Title

      how to link summary data to individual records

      Post

      I have several year experience in Microsoft Access and am trying to transition to Filemaker Pro. Some things are easy, but I cannot figure out how to link summary data to individual records. What I am trying to do is to look up only records where there are exactly two records that have a same matching field. In Access, this is easy: one creates a group by query and counts one of the fields and selects this count to be equal to "2". Then one creates a second query and links the original table to this summary query. Since summaries in Filemaker are done in layouts, I cannot figure this out.

      Example:

      field1      field 2

      Bob         15 hours

      Bob         20 hours

      Bob           7 hours

      Sally        12 hours

      Sally          6 hours

      David        1 hour

      I want to just find out something about Sally, because that is the only client who has exactly two records. How do I do this  in Filemaker? ( I am assuming this will need to be scripted). I tried a self join with summary field, but I cannot perform a find on the summary. Thanks

        • 1. Re: how to link summary data to individual records
          rjlevesque

          Without seeing a snippet of code of what you are trying to do I would have to say use a loop. Use a loop to count through until it finds all that you need, or use a comparative lookup field. Can you share the solution for us to look at or part of it maybe? Because there are several approaches you can take here depending on exactly what you need/want.

          • 2. Re: how to link summary data to individual records
            PhiloCalhoun

            The actual problem is a bit complicated. Most of the data I get is from a sql server and the queries write to Excel. Due to security issues, I cannot connect Filemaker or a personal computer to the server. What I am trying to calculate is the average and median times for operations where two identical CPT codes (and only two codes) are done at the same time and compare them to procedures where only one code is done to see if we need to book the two procedure cases in two time slots or just one. Unfortunately the data comes with cpt codes separated by commas in one cell, so I use the middle function in filemaker (mid function in Access) to split them off into a table that has an event number and cpt code as its only fields. The original table has data that I can use to calculate OR time and room time, but I want to use the split off table to find just cases where exactly two identical CPT codes occur for one event and calculate averages and median times for these and compare them with average and median times for procedures with just once CPT code that matches. This is not that difficult in Access, as one can perform queries using other queries (including summary queries). I am new to Filemaker, and I am finding it easy to look up data but not that intuitive for sophisticated analysis, so far.

            Since I am performing a lot of additional analysis, it is important to use the Event/CPT code table (to link to relative value units etc.)

            If I were not doing the additional analysis, I would create a boolean calculated field where it checks if Left(CPTcodes, 5)=middle(CPTcodes, 6,5) and length(CPTcodes0=11 and another calculated field for left(CPTcodes) linked to a table where length(cptcodes)=5 and calculate the median and average times for each corresponding value. However, I would prefer to work with the cleaner table that has just event and cpt codes as fields and is linked back to the original table that has event and multiple other fields for provider, time into room, time out of room, anesthesia, etc.

            • 3. Re: how to link summary data to individual records
              philmodjunk

              However, I would prefer to work with the cleaner table that has just event and cpt codes as fields and is linked back to the original table that has event and multiple other fields for provider, time into room, time out of room, anesthesia, etc.

              An understandable preference but one that is very often not an option when working with FileMaker. It's design makes the inclusion of calculation field inside the table definitions something that cannot be avoided for many problems you need to resolve with your design.

              Since summaries in Filemaker are done in layouts, I cannot figure this out.

              Summary fields are not your only option. Look up Aggregate functions in FileMaker help. You can use these to compute Sums, Counts, Averages, Standard Deviation, etc. for a set of related records. If you have this relationship:

              Parent::PrimaryKey = Child::ForeignKey

              Then Sum ( Child::NumberField ) when defined in a calculation field in Parent or computed in a script from a layout based on Parent, will compute the same subtotal you might get with a summary field defined in the Child table. Unlike this summary field, you can perform finds on it.

              If ( Count ( Child::Neverblankfield ) = 2 ; Sum ( Child::NumberField ) )

              Is one option you might be able to use to get your total when you want a total only when there are 2 related records.

               

              • 4. Re: how to link summary data to individual records
                PhiloCalhoun

                Thank you. I will try the aggregate function method.

                It seems to me (as a newbe) that Filemaker shows remnants of its old heritage as a flat file database (compared to Access). I understand that there are plenty of problems with Access as well (not really good in a multiuser environment and perculiarities in its language relative to Microsoft SQL server for queries), but one thing that works really well in Access is the ability to build queries using other queries. The lack of Access for MAC OSX is a bummer.

                The ability to make, append, or update tables based on summary and calculated data takes only one step in Access. One can, for instance, update a field based on the average or count of another field with a complicated and/or filter ("where" in Access) of another field in a different table. It is a great timesaver, and the consistancy of tables having just data and calculations belonging to queries enables sophisticated data mining to be done with ease. Access allows me to work the way I did with complicated math: one step at a time and building the next step upon the last. (basing one query on another)

                Filemaker is powerful, but it has more of a learning curve than I was led to believe, coming from a background not only in Access, but also in SQL databases, Paradox, Foxpro, and dBase.

                • 5. Re: how to link summary data to individual records
                  philmodjunk

                  Filemaker is powerful, but it has more of a learning curve than I was led to believe,

                  Having worked in both worlds, I suggest that the learning curve is steep for you because you have to "unlearn" ways that work in SQL driven databases before you can figure out what methods FileMaker uses to do the same thing. You'll find that most things you can do in Access can also be done in FileMaker but often in a very different manner. Some of those "FileMaker methods" will be much easier and faster to implement. Others will not be as easy as what you are used to in Access.

                  FileMaker finds are very easy for new users to take advanatage of and can save us developers a lot of development time when we don't have to try to anticipate every possible query a user might decide they need for a given form and often do not have to develop controls or a pop up just to facilitate a way for the end user to manipulate the form's SQL based recordsource expression in order to pull up a new record set for them. However, as the needed query increases in complexity, a "tipping point" is reached where a SQL type query becomes easier to set up than a Filemaker confection of multiple find requests and scripts intended to accomplish the same goal.

                  You can, BTW, perform FileMaker finds in successive stages as a way to produce the desired found set in a series of steps. That's where Constrain and Extend Found set options can be useful.

                  (basing one query on another)

                  I always found that something of a mixed blessing in Access. It did make the development of some solutions easy to design as you built up a series of queries where each one further resolves one issue or another to produce the desired final found set, but I also found some real nightmare solutions where it took many many hours tracing the queries from "layer to layer" until I could understand the relationships and why they were failing to work correctly.

                   

                  • 6. Re: how to link summary data to individual records
                    PhiloCalhoun

                    I partially agree with your comment about mixed blessing in Access. There are two very different ways I've used Access. When I am creating a database for others I separate the front and back ends and store the back end on a server. There may be multiple different front ends for different people depending on their needs and these usually restrict menu choices and access to different tables.

                    The second way I use Access is for data mining. I usually care little about forms and reports and use rigid naming conventions for queries, tables, forms, etc so that if queries are stacked, I can figure out problems (inner, outer joins, etc.). If I stack queries four deep, I usually make a table and then query this table to keep the stacking no more than four deep.

                    The output from this second way usually goes to some other program (R or SAS for statistics, a dtp program for presentation, etc.), so I don't make the output look polished in Access.

                    I understand how Filemaker might be better than Access for the first system, but I'm finding it cumbersome for data mining on the fly.

                    • 7. Re: how to link summary data to individual records
                      philmodjunk

                      When I am creating a database for others I separate the front and back ends and store the back end on a server. There may be multiple different front ends for different people depending on their needs and these usually restrict menu choices and access to different tables.

                      This can also be done in FileMaker. It also has a much better system for managing accounts and passwords than Access unless the latest releases have improved on what I used to see.