11 Replies Latest reply on Jun 15, 2011 2:10 PM by sksmith2517

    Counting Unique Values from Related Table

    sksmith2517

      Title

      Counting Unique Values from Related Table

      Post

      I have a database that relates Contacts & Agencies. I want to be able to count the number of unique agencies that 70 contacts come from.  I have already successfully created summary fields for those filedsof data that relate to the contacts and are in the contacts table (# who serve specific populations, # from Home Office, etc).  But I have created a related table that gives all the Agency Info - Name, Address, Website, Department heads, etc) This table is related with Primary & Foreign Keys being an agency serial number.

      On one of my Reports of Contacts, I want to be able to summarize how many unique agencies are represented.  I have used the  KnowledgeBase Answer ID#3423 from Nov, 2009. I have not been able to get an accurate count.  My results are either the same as the number of records in the found set, or 1. (Depending on how I tweak the calculation & Summary fields)

      Based on that Answer #3423, I did the 3 following steps:

      1) made a self-join with the Agency using the Auto Ser# as the match field. I did that in the Agency file.

      2) created a calc field If (Min(_K_ID_Agency)=Agency SelfJoin::_K_ID_Agency; 1; 0)

       3) created a summary field that totals the Calc field.

      Then on the Contacts' Report List - I placed the summary field from the Agency file - it always results with a number of 1.

      So I created new fields within the Contacts file - accessing the same fields in agency table:

      If ( Min(_KF_ID_Agency)=Agencies::_K_ID_Agency; 1;0). When I place this field (that is from within Contacts table) onto the layout, it always results in the same number as the found set.

      ARRGGHH

      Obviously I am not referencing the correct fields and/or creating the wrong calcs/summaries, etc. Thanks for any help.

        • 1. Re: Counting Unique Values from Related Table
          philmodjunk

          How do you related contacts to agencies here?

          contacts::_K_ID_Agency = Agencies::_K_ID_Agency

          or

          contacts::_K_ID_Contact = Agencies::_KP_Contact

          This really looks like a situation where you may have a many to many relationship issue. (can one contact link to many agencies and can one agency link to many contacts?)

          Do you want the total unique agencies in the agencies table? Or the total unique agency records for a given contact?

          • 2. Re: Counting Unique Values from Related Table
            sksmith2517

            One contact links to only one agency.

            One agency can link to many contacts. I have a portal in the agency file that accurately lists all contacts affiliated with the agency.

            My intent was that the total unique agencies would show how many contacts are associated with the agency.  Ideally I want that to show on a Contacts layout where I have a header (or it could be a sub-summary) that shows Totals for 6 fields.  But if it is in the agency table - that'll still give the info I want. 

            Thanks for helping me clarify the issues.

            • 3. Re: Counting Unique Values from Related Table
              sksmith2517

              The contacts & Agencies are related as:

              contacts::_K_ID_Contact = Agencies::_KP_Contact

              Mine actually reads:

              Agencies::_K_ID_Agency = Contacts::_KF_ID_Agency - but I think that is the same as your example - yes?

              • 4. Re: Counting Unique Values from Related Table
                philmodjunk

                Yes, we have the same relationship in mind here.

                Since you have a one to many link from agencies to contacts. Aren't you just counting the number of related contacts for a given agency? That's how I read this statement: "My intent was that the total unique agencies would show how many contacts are associated with the agency."

                If so, then you can define this calculation in agencies: Count ( Contacts::_KF_ID_Agency). This counts the number of related records in your portal and can be included in the report that you describe.

                • 5. Re: Counting Unique Values from Related Table
                  sksmith2517

                  Although you quote exactly what I said, it is not what I meant. ARGH. Aren't you just counting the number of related contacts for a given  agency? That's how I read this statement: "My intent was that the total  unique agencies would show how many contacts are associated with the  agency." 

                  I actually have successfully done that so that in my Agency table it will count the number of contacts we have from that agency. Cool.

                  I really meant what I had said originally, in my first post: I want to be able to count the number of unique agencies that any found set of workers contains.e.g. if the found set shows 30 contacts and they come from 7 different agencies, I want the summary field to return 7.

                  I have learned that when I use the the summary field that totals the calc of the Min function inside the AGENCY table it always returns the number in the found set.  That would be accurate since each record is unique, (Via a serial number). And I also learned that if some records in the agency field are blank, they are not counted - that again is a good sign that I'm on the right track, it is counting unique Ser #s.  

                  I want to count how many agencies are represented in a found set of workers. I surely do not want to give up on this - I'm still thinking it is counting of the wrong field - or calculating from the wrong field.  Thank you so much for your guidance.  It really helps me think it thru.

                  • 6. Re: Counting Unique Values from Related Table
                    philmodjunk

                    OK, I take it that if Joe Smith and John Johns both list agency "XYZ" they are linked to the same record for "XYZ" in the Agency table.

                    I can definitely write a script for this:

                    //first find your contact records here

                    Go To Related Record [Show only related records; match found set; From table: Agencies; Using layout: "Agencies" (Agencies)]

                    Then Get ( FoundCount ) will give you your unique count of records which can be captured in a global variable or in a field in your script so that you can display the count on your layout.

                    A calculation could compute this, but only if we can set up a working relationship from Contacts to Agencies that matches for all the records in the current found set. You could set up a field that lists the IDs of all the contacts in a single text field separated by returns, and then Count, with this relationship, will give us the count you want, but the only ways I can think of to set up that list of ID's for the current found set requires a script anyway so the above method is simpler.

                    • 7. Re: Counting Unique Values from Related Table
                      sksmith2517

                      I can't get that Script to work. The result is always 1.   Here it is:

                      Enter Find Mode[]

                      Go  to Related Record [Show only related records; Match found set; From  table: "Agencies"; Using layout: "Agency List" (Agencies)]

                      Set Field [Workers::Count Unique Agencies; Agencies::GetFoundCount]

                      Go to Layout["Summary Report" (Workers)]

                      But

                      I can now accurately get a unique value via a Summary Total field of:

                      UniqueValue Calc [If (Min(_K_ID_Agency)=Agency SelfJoin::_K_ID_Agency; 1; 0)].

                      I have tried multiple tests and this number is accurate.  However it is  ONLY accurate in the Agency Table, not when I transfer it to the report  where all the other summary counts are located. There it always returns a  1.

                      I have yet to get the correct method to transfer that Summary Total number onto my report.  Sigh.  Seems so close but not quite there.  ARGH. What do you think would be the best means to get from Agencies into Workers report?

                      • 8. Re: Counting Unique Values from Related Table
                        RonCates

                        take out Enter Find Mode[]

                        • 9. Re: Counting Unique Values from Related Table
                          sksmith2517

                          Nothing changes - still returns 1.  I used the Find function to create different found sets to confirm that the Unique Value is accurate - it is.

                          I believe the issue is that the Summary Field shows only in a 'local layout' which in this case is the Agency Table.  The others summary totals are all from the Workers table which generates the report and that's where I think it should reside.  Thus I'm trying to capture that number via copy/paste; set field; global field, in any way that will work. 

                          • 10. Re: Counting Unique Values from Related Table
                            philmodjunk

                            You first have to find the records you want in contacts. Then the script uses Go To Related Records to match the found set of contacts records against the records in the related agencies table. You have to change:

                            //first find your contact records here

                            Into code that finds the records you want. I wrote it that way as I don't know what criteria you want to use to find your records in the contacts table.

                            You can set up either: Perform Find [Restore] //where you specify the criteria in the Find Step

                            or:

                            enter Find Mode []
                            Set field [Contacts::some field ; //Some criteria goes here]
                            Perform Find []

                            • 11. Re: Counting Unique Values from Related Table
                              sksmith2517

                               I use the script  just having Enter Find Mode so that I can enter any criteria (it changes so often that I didn't want to script that part). When I perform it that way my

                              unique value via a Summary Total field of: UniqueValue Calc [If (Min(_K_ID_Agency)=Agency SelfJoin::_K_ID_Agency; 1; 0)] is always correct.

                              Are you saying that I must enter the setfield Contacts:: etc in order for this to work? - e.g. it must be contained within that script? And that I cannot just enter any criteria beyond what is in the script?  OK - then let me try that, so my script now shows

                              Enter Find Mode [Restore]

                              Perform Find

                              GoTo Related Record [Show only related records; match found set; ... ... ...

                              Pause/Resume Script - so I could see what that matched found set looked like.  Indeed it shows accurately - the correct number of agencies included in the found set. but that correct number does NOT transfer into the report.  So it really doesn't seem to matter about how I perform the find - either prior to or inclusive in the script.

                              So I'm thinking the error is in my script step: Set Field[Workers::CountAgencies; Agencies::GetFoundCount] -When I check with the Pause/Resume step,  I see that the GetFoundCount result is the total number of agencies in the table, not the number of agencies in the found set.  The Unique Value summary is indeed the correct number. But even if the getfoundcount was accurate, it still shows as a 1 in the report, not the actual foundcount number. So I changed the script to have the setfield Source field to Agencies::UniqueValueSum and it still reports in the table as 1.  I tried putting a portal for just that single field and it reports out a 1. 

                              How one gets that unique sum value from Agency table to Worker table is beyond my brain... I just hate to give up on this.  The report summarizes so many relevant  pieces of info - and Agency seemed to be important to all.  Sigh.