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?