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

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • In Progress
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?
Photo of moleman108

moleman108

  • 954 Points 500 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
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.
Photo of moleman108

moleman108

  • 954 Points 500 badge 2x thumb
it would be 1 trip with 2 guys. I want to be able to track # of trips and what installers are on what jobs.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
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.

Photo of moleman108

moleman108

  • 954 Points 500 badge 2x thumb
are you saying there's another table called "Installation Assignments"?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
Yes, exactly.
Photo of moleman108

moleman108

  • 954 Points 500 badge 2x thumb
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.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
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.



Photo of moleman108

moleman108

  • 954 Points 500 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
If you get really stuck contact me off line and I will give you some scheduled time.
QuickBaseCoach.com
(Edited)
Photo of moleman108

moleman108

  • 954 Points 500 badge 2x thumb
thanks!
Photo of moleman108

moleman108

  • 954 Points 500 badge 2x thumb
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.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
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
Photo of moleman108

moleman108

  • 954 Points 500 badge 2x thumb
But which table is actually being used to record the info? the installation or the assigned installers?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
Well, both.  The Installation record is about the trip and maybe what is to be done that day for the job, and the child table is which installers are riding in the truck.
Photo of moleman108

moleman108

  • 954 Points 500 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,670 Points 50k badge 2x thumb
I’m pretty tied up in the next.p 2 days with client work and will respond over the weekend.
Photo of moleman108

moleman108

  • 954 Points 500 badge 2x thumb
I appreciate any help you can give.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,670 Points 50k badge 2x thumb
Can you contact me off line?  QuickBaseCoach.com