Discussions

 View Only
Expand all | Collapse all

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

QuickBaseCoach Dev./Training

QuickBaseCoach Dev./Training11-06-2018 13:13

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

    Posted 11-05-2018 20:04
    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?


  • 2.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-05-2018 21:01
    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.


  • 3.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-05-2018 21:35
    it would be 1 trip with 2 guys. I want to be able to track # of trips and what installers are on what jobs.


  • 4.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-05-2018 21:41
    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.



  • 5.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-06-2018 13:11
    are you saying there's another table called "Installation Assignments"?


  • 6.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-06-2018 13:13
    Yes, exactly.


  • 7.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-06-2018 13:25
    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.


  • 8.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-06-2018 13:33
    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.





  • 9.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-06-2018 13:43
    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.


  • 10.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-06-2018 13:46
    If you get really stuck contact me off line and I will give you some scheduled time.
    QuickBaseCoach.com


  • 11.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-06-2018 13:50
    thanks!


  • 12.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-07-2018 13:09
    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.


  • 13.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-07-2018 13:28
    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


  • 14.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-07-2018 13:40
    But which table is actually being used to record the info? the installation or the assigned installers?


  • 15.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-07-2018 14:50
    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.


  • 16.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-07-2018 15:58
    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.


  • 17.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-08-2018 02:16
    I�m pretty tied up in the next.p 2 days with client work and will respond over the weekend.


  • 18.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-08-2018 13:12
    I appreciate any help you can give.


  • 19.  RE: Need to have 2 fields that pull from the same related table on 1 form

    Posted 11-08-2018 14:55
    Can you contact me off line?  QuickBaseCoach.com