Forum Discussion

ChrisNewsome's avatar
ChrisNewsome
Qrew Captain
7 years ago

Need to have 2 fields that pull from the same related table on 1 form

OK so I have an interesting question here. We build and install custom closets. We'd like to track our installations so we can see how many trips we make to a job, and which installer(s) were on the jobs, and the value of those jobs installed. Here's how it's setup:

I have a table called "Opportunities" for the job itself. This tracks value, etc.
I have a table called "Installs" to track the number of trips to the job. 
I have a table called "Installers" to track my employees.

Relationships:
Many installs - 1 opportunity
Many Installs - 1 Installer
(maybe I need many to many?? Don't quite know how to do that)

I'm currently pulling in field for the installer's name to the "Installs" form. There are times I might send more than 1 installer to a job, so I need to be able to select more than 1. I made a table for them so that I could globally add and remove installers. There is a field called "Status" with a choice for "Active" or "Inactive" for the installer.

So on my installs form, if I make 2 fields to select the installer, it will correct both fields to read the same name since I am pulling from the same table / field. I'd also like to limit the list to installers who are only listed as "Active." I've run through all my knowledge on this and came up very short. Anyone out there able to help?
Forms
Tables and fields
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Can you tell me if you send 2 installers to the same job, are you saying that is 1 install (trip) where say 2 guys are on the same truck driving out to the job?  or do you want to count that as 2 Installs.
  • ChrisNewsome's avatar
    ChrisNewsome
    Qrew Captain
    7 years ago
    it would be 1 trip with 2 guys. I want to be able to track # of trips and what installers are on what jobs.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Right, that is what I thought.
    So then you will need a many to many relationship setup.

    One Install has many Installation Assignments.
    1 Installer has Many  Installation Assignments.

    and then you will end up deleting your table for the 1 Installer has many Installs.

    If you have existing live data in your app, then there are ways to migrate that data across to the new relationship setup before deleting the relationship and losing the data.

  • ChrisNewsome's avatar
    ChrisNewsome
    Qrew Captain
    7 years ago
    are you saying there's another table called "Installation Assignments"?
  • ChrisNewsome's avatar
    ChrisNewsome
    Qrew Captain
    7 years ago
    OK, just so I'm clear, (this is my first time with many-to-many), will that table need to have it's own fields? I need to make this as foolproof as possible for my team.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    The middle table may not end up having any extra fields.  It may just end up being lookup fields from the Installations and the Installer(Employee) table.  But if, for example you need to record the amount of time on the job, per installer, then you might need to add some fields.

    A way to get the Many to many concept in your head is to think of a traditional order or invoice which has line items on it.

    One Invoice has many invoice lines.
    One Item Master has many invoice lines.  (ie, obviously when you have an Item in your Item master, you hope to sell it more than once!)

    The middle table sometimes called  join table, would have lookup fields from the item master for the item code and the item description, but would typically have its own fields for the qty, and the it might have a lookup for the price which would be used in a snapshot field to freeze the price at order entry time and then a field for the extended price times qty.  It might also have a Price override field. 

    So the middle table will have lookup fields, and then might also have it's own fields if they are needed.



  • ChrisNewsome's avatar
    ChrisNewsome
    Qrew Captain
    7 years ago
    I appreciate your time. My first attempt is apparently a failure. I am hoping to make the process as seamless as possible for my scheduler, and my first pass was quite confusing to use. I can't seem to get the info from my "installers" to show up on my "trips" by connecting to that "assignments" table. It wants me to add a new assignment. So I'm going to trips, then to assigments, and back again. It needs to be simpler than that.

    I'm going to try to work this out. Your explanation makes a lot of sense. I watched the QB university video on it as well and it didn't seem that bad, but in real world use it didn't work the same as expected. I've probably missed something. Fortunately, these are new tables so there's not tons of data to be worried about.
    • ChrisNewsome's avatar
      ChrisNewsome
      Qrew Captain
      7 years ago
      Quick question.... which of the 3 tables should be used for actually creating the trip? I think this is where I'm getting tripped up.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      Well, you would first set up the installation record, and then add Assigned Installers.

      If you edit the Avpdvanced table properties for installations to check the box for save automatically when a child is added, that will be a smoother process
  • ChrisNewsome's avatar
    ChrisNewsome
    Qrew Captain
    7 years ago
    OK, I have created the relationships like this:

    Installations < Trips
    Installers < Trips


    Installations < Trips has the following connections:
    "related Install"
    "related Install - Related Opportunity"

    Installers < Trips has the following connections:
    "related Installer"
    "installer - name"

    I go to my opportunity (job) and create an installation. Then, within that installation, I create a trip I have the one field for Installer - Name. How do I get the second installer on that trip? I can't copy the field, then I'm back where I started as both fields show up as the same name no matter what I enter.