1 Reply Latest reply on Nov 3, 2014 1:05 PM by philmodjunk

    Summarize Data Intersecting between two different layouts.

    MattW_2

      Title

      Summarize Data Intersecting between two different layouts.

      Post

      I am a novice user of FileMaker Pro recently switched from Access as utilizing a Mac. I have two separate charts (Chart 1 and Chart 2) listed as individual layouts. Each one contains Customer Name (Linked in relationship), Facility, Transaction Type, and Transaction Date. I am trying to pull information for how many Customers had a transaction listed in Chart 2 at a specific facility within 30 days from their first transaction listed on Chart 1 at the same facility. Basically, I need a count by facility for how many customers had a transaction within 30 days or less from their original transaction date at facility within given month. I have tried multiple ways of pulling such information using FileMaker Pro as well as Excel incase I was over thinking the complexity. I have tried to utilize scripts, reports, portals, etc. but not having much luck (I am not very familiar with SQL writing and hope there is a more user friendly option). Any help would be greatly appreciated, included an

        • 1. Re: Summarize Data Intersecting between two different layouts.
          philmodjunk

          It would help to see the data model involved. Layouts are not linked in relationships, Tutorial: What are Table Occurrences? are linked in relationships and each of your layouts are based on a table occurrence selected in "Show Records From" listed in Layout setup. These might be two occurrences of the same data source table or two separate tables.

          My guess is that you have separate data source tables and that could easily be a less than optimum design for your data model. Matching by Customer name is also not a good idea (not even in Access), as customer names are not unique, among several other potential issues that can keep name based relationships from working.

          But with this relationship:

          Table1::CustomerName = Table2::CustomerName

          A given record in Table1 will match to all records in table2 with the same value in CustomerName--no matter what the date of the transaction. To get the count that you need, you either need a different relationship or a filtered portal.

          Example::

          Table1::CustomerName = Table2::CustomerName AND
          Table1::TransactionDate > Table2::TransactionDate AND
          Table1::cTransDate30 < Table2::TransactionDate

          cTransDate30 would be a calculation field with a date result type defined as: TransactionDate + 30

          To link multiple fields in one relationship and to use inequality operators in place of =, double click the relationship line to open the edit relationship dialog.