3 Replies Latest reply on Jun 5, 2015 11:41 AM by siplus

    Using a self-join to group like records


      We track jobs in a "Jobs" table. Frequently, we have multiple jobs that are part of a larger campaign. What ends up happening is the account directors create new "jobs" with common names, so "Annual Report," "Annual Report Acquisition," "Annual Report Production," "Annual Report Web Layout," "Annual Report Chart Development," etc. This is a great workaround to my hacky FM database, because they can just search for "Annual Report" and get a list of all the jobs involved.

      I want to improve the options for grouping jobs into campaigns. At first, it seemed easy: a self-join in the jobs table. I made a new field, campaignNumber and created a new relationship that was a self join on the campaignNumber. To make this work, you have to copy the job# of the main job (the "campaign) and paste it into campaignNumber for each of the related jobs. This works... kind of. It still feels very hacky to me. Is there a better way?

        • 1. Re: Using a self-join to group like records

          Not sure if this'll help, but...


          I do something similar for activity records that are part of a "thread" (emails, phone calls, letters, meetings, etc. that are all part of a back-and-forth).


          In addition to the activity primary key, I have a "thread key", which auto-enters the primary key (I actually modify it to replace "ACT" with "THR" but keep the uid, but I don't think that matters here). If, however, you create an activity from an existing activity, the new activity adopts the original activity's thread key. Even if the original activity gets deleted, the rest of the activities in the thread still share the same thread key and can still create new records with that thread key.


          If you replace "activity" with "job" and "thread" with "campaign", this sounds very similar to what you're doing, except for the copy/paste part. For myself, I would make it so that you linked jobs to a campaign either by adding a job from a job ("Add New Job to This Campaign") , or by selecting a job that is part of the campaign you want this job to "join'. I wouldn't base the relationship on a value the user is allowed to edit, let alone one that they have to copy and paste, or type exactly elsewhere.


          Of course, if your campaign has attributes of it's own, it should have a table, but like my "threads", it sounds as if the grouping is all there is. This way, to link to other jobs in the campaign you just need to see where the campaign key matches but the job key does not.


          So, short answer (too late! ) is that this doesn't sound hacky, at least to me.




          Chris Cain


          • 2. Re: Using a self-join to group like records

            There are a couple of ways, both rather similar to what you  just described (at least in terms of total schema and relationships).


            1)  Parent/Child tables:  Create a 'Campaigns' table and then the 'Jobs' table.  Every Campaign would have at least one Job (for those that don't have multiples), but then if you did want to make multiples you just create a new related record to the parent Campaign record.   You would perhaps have to adjust some layouts and scripting to account for this new structure and to properly create related records.  You will still have a two table occurrences related together, this version just happens to be two separate tables instead of two versions of the same table.


            2) Similar to above, but using just a 'Jobs' table, you provide the end users a way to automatically created related Jobs records.   Just as you already described, you have to copy in a parent-Job ID into the related record; so provide them a button on the parent record that will create a new jobs record but relate it back to the parent.  I would also suggest (perhaps) that when you create a child, you also (in scripting) create a field that indicates that this is a child record and you NOT let them create children of the child.  That is, you can only have 1 parent record for a campaign and everything (if you want it related to that campaign), has to be a child of that one parent record.  Instead of permitting grand-children, great-grand-children, etc.


            --  J

            • 3. Re: Using a self-join to group like records

              Every new job you create should inherit a foreign key pointing back to the campaign it belongs to.


              You should not be able to create a job without having a campaign first. You can have a special campaign called "not yet assigned", though, to which all fatherless jobs belong to. But hey, they need adoption ASAP. Of course, such accidents only happen when you create jobs on a job layout instead of creating them as a related record on a Campaign one (which you shouldn't).


              Once you got that part done, it's merely going to related records, from campaign into jobs, to find the relevant ones.