Forum Discussion

XeniaWatterson's avatar
XeniaWatterson
Qrew Trainee
7 years ago

Summary field based upon two many to many relationships

I have four tables Projects, Kickoffs, Technicians and Site Visits.  The relationships between the tables are set up as Projects < Kickoffs > Technicians but also Projects < Site Visits > Technicians.

I need to be able to show if the Technician assigned to the Site Visit has had the Kickoff already for this specific Project.  I want to know did Sam the Technician get a Kickoff for the Saving Puppies Project (so good to go for all those visits) but she has not had the Kickoff for the Saving Kitties Project (so needs to have the kickoff for these visits). 

I am sure it is a super easy solution but I'm stumped.  I am not sure how to create a summary field that will show on the Site Visit that either the date of the Kickoff for the related Project and related Technician or at minimum that there is one Kickoff record that matches the related Project and related Technician.  I would even be happy to have a report link that shows just the one record that matches these two criteria but I can only have it match the related project or related technician.  How do I indicate that the technician has had a kickoff for that specific Project when assigned to a Site Visit?
Forms
reports and charts
Tables and fields
  • AlexCertificati's avatar
    AlexCertificati
    Qrew Cadet
    7 years ago
    So, Kickoffs and Site Visits are both join tables between Techs and Projects? I suppose there's a good reason they can't be unified in one table, say, Activities?

    How do you identify/refer to Kickoff records? It seems that what you basically need is a way to identify what Kickoff each Site Visit is related to - so a unique field identifying the Kickoff, which you can then lookup down to Project and from there back up to Site Visits.

    Though I have the sense there's something I'm missing about your structure. Conceptually I find it more comfortable to think of Projects as a parent table, with Site Visits and Kickoffs as its children, assigning Techs to the latter two from their own table. That makes it easy to use Project ID as the organizing principle and communication vector for the child tables. I know this isn't helpful, but I'm struggling with the same concepts you are.

    It's an interesting question. I'll be curious to see what the pros have to say.
  • XeniaWatterson's avatar
    XeniaWatterson
    Qrew Trainee
    7 years ago
    Thanks for responding.  Kickoffs aren't the same as Site Visits because a single tech can do multiple Site Visits for a project (so they could save Puppies in Chicago and Detroit and Minneapolis) but they only need one Kickoff per project.  I currently have a report link where the related projects match, but that's not being limited by the matching technician, so it will show all the techs that have the Kickoff for the project or all the projects the tech has had a kickoff for.
    • AlexCertificati's avatar
      AlexCertificati
      Qrew Cadet
      7 years ago
      But structurally, they kind of are the same. They live in the same space. You spin up a Project. Each Tech that is going to have any Activity on that Project has to have a Kickoff Activity. Once they've recorded the Kickoff Activity, they are eligible to be matched with Site Visit Activities.

      It sounds like a join table between Projects and Technicians, and each Activity has a classification (Kickoff or Site Visit or anything else), anyway when I look at it that way it makes the answer to your present question easy, because you're just looking up conforming records in the same table where you need to answer the question.

      The other approach does work, though, I think - or at least it sounds good enough that in my app I'd try to work through it while I was figuring it out. ;) Get your key Kickoff field onto both Technicians and Projects as 'related Kickoff' and go from there..

      EDIT: I'm just an interested observer, by no means an expert - just starting my journey in Quick Base so I enjoy thinking about these kinds of structural questions. I'm hoping somebody with a better grasp on possibilities comes along soon to actually help you!