Jump to content

jeremychance

Members
  • Content count

    15
  • Joined

  • Last visited

Community Reputation

0 Neutral

About jeremychance

  • Rank
    member
  1. jeremychance

    Many many-to-many relationships

    I work for a multidisciplinary design/fabrication/research practice, so our contact lists covers a wide array of industries and uses, and our targets are often multidisciplinary as well. We want to be able to track Individual contacts, the multiple Entities they might belong to, and to organize both Individuals and Entities in Lists for specific outreach campaigns (phone, individual emails, as well as email blasts). Ideally, Individuals and Entities can interrelate. On a layout showing one Individual record, I want to be able to see all the Entities the Individual is a part of. On an Entity record, I want to see all the Individuals that comprise the Entity. And I want to be able to have multiple, time/user tagged Notes for every Individual, every Entity, and every List. Examples: 1) we often want to contact a museum curator in one context, but contact the same person through their academic position at a university for a completely different reason; however, we want to be able to keep an overview of all communications with this person. 2) we fabricate sculptures for artists, sometimes we try to reach them as individuals, but other times we campaign the multiple galleries that represent these artists. artist and/or gallery could become our client, so again, we need to see an overview of the interrelationships does this help clarify? Thanks!
  2. jeremychance

    Many many-to-many relationships

    Hello, I am in the planning stages of building a Contact / Marketing Outreach solution, and it has become clear that all my tables need to interrelate in a many-to-many way. It will have: Individuals Entities (i.e. companies, organizations) Lists Notes All these things interrelate in a many-to-many way, so they require a join table (though Notes might be an exception, as any given note would only relate to a single Individual, Entity or List). What I'm wondering is if it makes sense to use only one join table between all 4 of these things and use complex relationship criteria, scripts, calculations, and filters to build out all the requisite layouts - OR - do I use a different join table for each relationship. Thanks! Jeremy
  3. Hi Brian, That works! Thank you for helping me. But what's the difference between the layout and the portal? Why is that the layout still shows all the records while the portal shows just those with the Reminder checked?
  4. Hi Brian, Thanks for the suggestion! I'm still see all the records though. I made the verifyReminder field an indexed calculation. so now the relationship is: Pkey Tracking = Pkey Tracking AND verifyReminder = Reminder could it have to do with the context of the verifyReminder calculation? or the lack of Fkey Tracking? Thank you, Jeremy SITUDB2.zip
  5. Hi, I am making a CRM database for my company and I'm having trouble understanding the fundamentals of using table occurrences and multiple criteria relationships to create specific layouts. I have a Contacts table in a many to many relationship with a Tags table. They are joined by a Tracking table that has a "Reminder" check box, a "Primary Partner" field (i.e. "Staff") , and a Next Contact date. From here, I want to create layouts for each Primary Partner that only list all the records with the Reminder check box filled and sorted by Next Contact date. (Ultimately, I'm aiming to script automatic emails with these lists to be sent out at regular intervals). The way I've attempted this so far is to connect two occurrences of the Tracking table by matching the Pkey to itself AND to compare the Reminder check box field, which returns "yes", to a verifyReminder field that simply contains the word "yes". My assumption is that a layout created through this table occurrence would then show only the records with the reminder box checked, but I fear that I am making a fundamental mistake because the layout continues to show me all the records in the table. I haven't even attempted to filter it by the Primary Partner, figuring I need to fix and understand this first. I'm attaching my file. The layout I'm focusing on is Contact_Tracking_All. I'd appreciate any suggestions. Best, Jeremy SITUDB2.zip
  6. HI All, I am looking for a tutorial on something that is probably basic, and probably already well documented - I'm just having trouble finding it: I'd like to create a list layout with a drop down box on the top that sets the sort term and re-sorts the layout. This exists in the FM12 "Content Management" starter solution, and I've tried to replicate it here, but I think I am missing something(s). I am working on the layout "Company Loader - Sort List". I welcome any suggestions! Thanks, Jeremy db.zip
  7. jeremychance

    Portals and Joins

    ok! sorry for the confusion with the files. i see that you've used additional instances of the Campaign and Company tables. I wondered about that, but was not really sure how to begin. you've helped me greatly with my project, and i look forward to digging into how you did this. thanks, jeremy
  8. jeremychance

    Portals and Joins

    Thanks Bruce! As far as I can tell, my Campaign layout is working fine, even with the Add button in the portal row (it sounds like you think this is problematic though). I am referring to the Campaign Tracker layout, ive highlighted the portal in yellow. Thanks! Jeremy
  9. jeremychance

    Portals and Joins

    You are absolutely right. Here is the file without restrictions. Thanks! Jeremy SituDB.zip
  10. jeremychance

    Portals and Joins

    Hi Friends, I am working on a Contact Management / Marketing Campaign Database. At its core is a many-to-many relationship connected through a Join Table connecting Campaigns and Companies. I'm building a layout that strives to collapse a lot of functionality on one screen, called the Campaign Tracker. It shows the records of each company associated with a given campaign with notes specific to each and a portal to each company's contacts. Now, I am trying to add an additional portal that shows all the companies in the campaign. So far, each portal I add only shows the company record i am already looking at, not all the companies of any given campaign. Essentially, I want to have the same list that is on my Campaign layout, but i cant get it to work. I think its probably something stupid, but I can't see it. Any suggestions? My file is attached. Many Thanks, Jeremy SituDB.zip
  11. jeremychance

    Marketing Campaign Help (Many to Many)

    So simple! Thanks!
  12. jeremychance

    Marketing Campaign Help (Many to Many)

    Thanks Lazarus, Comment, All of this has been helpful and is appreciated. I think I have the many-to-many relationship structure under control. Now I have a new challenge: Ive been trying to add a Notes table. I've pulled the basics from a Contact Management starter solution, but I must have made mistakes because it isn't working. I've looked at all the parts a few times and can't figure out where I went wrong. The Notes table is linked to a join table compiling Campaigns and Companies, as each instance of a joined Campaign and Company needs to have its own set of notes. It uses an auto entered calculation to create timestamp/userstamp at the beginning of each note, and sorts by this as well; however, this does not work, and is throwing everything else off, such as the New Note button and its related script. Any suggestions would be appreciated! I am attaching a copy of my file as well as the starter solution I am pulling from. Thanks! Jeremy JeremyFiles.zip
  13. jeremychance

    Marketing Campaign Help (Many to Many)

    Hi Lazarus, Thanks for the suggestion - it was helpful. I was able to use the same scripts to achieve what I wanted to do. But being a novice, I don't totally understand why the Select Client script is necessary. What is it doing that the existing table relationships can't do? Thanks, Jeremy
  14. Hello, I am a novice trying to improve my employers contact database. Presently we have a basic contact DB with Company and People tables. I am now trying to add a Marketing Campaign table that will contain the name, description and companies included in each marketing campaign. Each Campaign will have many unique companies and each company can be a member of many campaigns. Ideally, I would be able to create a new campaign record and then add companies to the campaign from a drop down box. I could delete companies from the list without deleting the companies altogether. I've made a few failed attempts. I'm think i'm getting hung up on the many to many relationship. Any help would be very appreciated. Thanks! Jeremy
×

Important Information

By using this site, you agree to our Terms of Use.