3 Replies Latest reply on Feb 13, 2015 6:38 AM by philmodjunk

    Counting customers in the same County



      Counting customers in the same County


      I'd imagine this is pretty simple but today I cannot get it to work!

      I have an address table and for normalization purposes a County table hooked into it.  The address table has the foreign key of the county next to each customer's address.  Hants = 10, Surrey = 11 and so on.

      What I need to do is count how many customers, including the one I'm looking at, are in the same county.  This info is to be shown 1) on a general report (Hants = 33 Customers) and 2) on a customer record (Total other customers in your county excluding yourself = 32).

      Taking the Hants example, I need to run down the address table and (in my twisted logic) Count If (CountyID = 'The Customer's Own County ID'.  Or something similar.

      Please excuse the incomprehensible ramblings, it's been a long two days.....


        • 1. Re: Counting customers in the same County

          If you base your report on the table of counties instead of the table of addresses, it's pretty easy to produce a report that lists each county once with a count of the number of related address records for that county. Both  a count function in the counties table and a "count of" summary field in the addresses table can be used to produce that count.

          You can also set up a summary report based on addresses where a summary field inside a sub summary layout "when sorted by county" provides the same count. Such a report can also list the individual addresses or just the counties.

          To get a count of "all addresses from the same county as me", set up a self join relationship between two occurrences of the same table with the county ID field specified as the match field on both sides of the relationship. Then a count function in a calculation field or a summary field can be used to count the related records.

          • 2. Re: Counting customers in the same County

            Thanks Phil

            That's given me a good start.  I've self joined by CountyID and this works.  

            A related question which cropped up...Each county falls in a region, details of which are stored on a further related table.  When I tried to do a third self join from addresses by RegionID (which I have as a calculation Region::RegionID), FM didn't recognise these as being many to many on the relationship (even as I made it in the relationship chart).  However, when I hard keyed the same region ID's into the table layout on Addresses and did a self join on this, it recognised it as many to many and worked.  Naturally, I'd rather just have a formula, but it seems in this case at least I cannot self join on a calculated field.  Is that normal?

            • 3. Re: Counting customers in the same County

              Your self join won't work if the same field is used on both sides of the relationship and it is an unstored calculation field.

              Unstored calculation fields, unindexed fields and global fields only work in a relationship from the context of the layout's table occurrence matching to other table occurrences. So you can use such a field on the 'starting point' side, but not on the other side as each of these fields lack the needed index for that to work.

              You'll need to come up with a way to get that data into a field that can be indexed before you can use it. One option is to define an auto-enter calculation. Note that the trick here is that you have to be careful to ensure that such a field correctly updates when you modify data used to calculate its value.