7 Replies Latest reply on Aug 2, 2016 5:51 AM by keywords

    Help with Relationship


      I am trying to create a report based on a relationship between two tables:


      • ORGs. Each record contains information about a single organization. Each record contains a unique ORGID
      • COHORTS. Each record is a cohort or group of Orgs meeting specific criteria. e.g. Cohort 1 = Orgs that purchased in Jan 2016. A single Text field in COHORTS (COHORTS::OrgList) contains multiple ORGIDs. COHORTS::Description is a text field containing a description of each cohort.


      I want to create a report for each Cohort showing information from ORGs. For example:


      COHORTS::ID = 6

      COHORTS:: Description = Orgs that purchased in Jan 2016


      ORGs::IDORGs::Org NameOrgs::DateOrdered


      What's the best way to create this report?

        • 1. Re: Help with Relationship

          Assuming you already have a relationship between COHORTS::OrgList and ORGs::ID, there are two options:



          1). On a layout based on COHORTS, create a portal showing related records from ORGs. Choose to display the fields mentioned (ORGs::ID, ORGs::Org Name, ORGs::DateOrdered).



          2). If you will want summary information, create a new List-view layout based on ORGs. Place the ORGs::ID, ORGs::Org Name and ORGs::DateOrdered fields in the Body Part. Create a new Part: Sub-summary when sorted by COHORTS::ID. Choose to display the part *above* the records. Then, in that new part, place your COHORTS::ID and COHORTS::Description fields. When you view the layout, be sure to sort the records by COHORTS::ID in order to view the COHORTS fields.



          More information on exactly what you want will help to narrow down your options.

          • 2. Re: Help with Relationship

            Hi Sccardais,


            Can you post an image of your relationship?

            • 3. Re: Help with Relationship

              To relate the two tables you should have a cohortID field in the ORG table, in which you post the ID of the cohort to which the organisation belongs.

              To make your report, however, base it on the ORG table with a sub summary part sorted by cohort.

              • 4. Re: Help with Relationship

                Thanks to all who responded.


                A COHORT could be assigned to many ORGS and an ORG could be assigned to many Cohorts so I probably need a Join table.


                • For example: Assume Cohort #1 tracks all Orgs that purchased in Jan 2016. Cohort 2 tracks all Orgs that purchased in Q1-2016. Cohort 3 tracks all Orgs over a specific size. A single Org could be part of all three of these Cohorts.


                Another option perhaps: The OrgID's for each Cohort are already stored in COHORTS::OrgsList. Pasting the values in this field into a find request in Orgs does not work but is there another way to automate a Find in Orgs using the values from COHORTS::OrgList?


                COHORTS::OrgList could contain as few as one OrgID but could contain hundreds. An example may help:


                1. Someone requests a new Cohort for tracking purposes. e.g. Orgs who Ordered in Jan 2016.
                2. Search ORGS for Orgs who purchased in Jan 2016.
                3. Store the OrgIDs of the found set in a Summary field (List of).
                4. Copy these values.
                5. Create a new record in COHORTS.
                6. Paste the OrgIDs into COHORTS::OrgList.


                Essentially, I want to reverse this process by using the values in COHORTS::OrgList to do a custom search in ORGs.


                This is sounding like a saved search but is there a way to store the criteria for a Saved Search in another table?

                • 5. Re: Help with Relationship

                  That definitely sounds like a case for a join table. You idea of an OrgList is certainly another way to achieve a similar result—effectively a multi-value key field—but a join table is simpler and more easily extensible. You would simply create the new Cohort record, the add away to your hearts content the relevant Orgs. It's very simple, especially if you set the relationships from both sides to the join table to allow record creation, and have value lists of all Orgs and all Cohorts and attach the to the relevant field in a join creation portal on each side (i.e. from Org side and from Cohort side).

                  • 6. Re: Help with Relationship

                    Thank you, Keywords.


                    Since I already have the ORGIDs in a field in COHORTS, I would prefer to use them instead of creating a Join table IF there’s a way to create a relationship between COHORTS and ORGs that works.


                    For each Cohort, I want to use the values in COHORTS:OrgsList to find records in ORGS:OrgID. Is this possible through a relationship? I thought the multi-value key field approach would work (se below) but so far, I haven’t beeb successful using this approach.


                    COHORTS::OrgsList = ORGS::OrgID does not work.


                    Can you suggest a multi-value key that would work? COHORTS::OrgList is a text field containing OrgID’s in a return-separated list.




                    If I have to create a Join table, once I complete the Find in Orgs, how can I quickly create separate records in JOIN to hold fk_ COHORT_ID and (possibly hundreds)  fk_ORGID?



                    Is there a way to save the search criteria used in Saved or Recent searches to fields in a separate table for later recall? It would be GREAT to store this info in each Cohort record.



                    Thanks again for your help and replies.

                    • 7. Re: Help with Relationship

                      Here is a demo file that shows both methods. Note that the multi-key method requires scripting, whereas the join table method is simpler.