Choose from a lookup of several sub contractors and add them as needed?

Hello -
I am having a heck of a time and could use whatever help I can get.

I have a contact list of subcontractors.
I need to be able to fill in a form in which I choose which ones will be used on a job from a drop down or embedded list, etc. Each job could have 10 subcontractors. I have tried a couple of different things to accomplish this but nothing is working.

Let me know how I should go about this.

Thanks

Hello @ChristianWentz,

You can either use the Multiple Choice Lookup field or List or Library control.

Using the custom validators you can restrict the number of subcontracts. Please see the example of custom validation here.

HI! Thanks for the help.
I have been trying to get it to do this. When I go to the DATA SOURCE EDITOR, there is nothing available in the LOOKUP FIELD. The list is right, the views are right, but nothing in LOOKUP.

Am I missing something? Thanks again

Hello @ChristianWentz,

Have you decided to use List or Library control?

If so, you need to create a third list to store dependence between job and subcontractors, let's name it Related subcontractors.

In Related subcontractors, you need to create two lookup fields:

  1. Subcontractors - points to the list of subcontractors
  2. Parent ID - points to the list of jobs

In the DataSource settings of the List or Library control, select Parent ID from the Lookup Field dropdown.

1 Like

Great! Let me give it a shot. Thanks for the help :slight_smile:

1 Like

Good morning -
Perhaps I should clarify what I am trying to accomplish.

I do want to use a List or Library control to look up my Subcontractors Name, Email, and Phone Number from another list called Subctontractors and add them to my form connected to a list called Job Information. Also I want to be able to filter them based on their status as Available or Unavailable. It is important also that I am able to add one to my Job Information form, then add another, and another, and another as needed.

The problem is that the List or Library Control - connected to the Subcontractors List shows what is in the Subcontractors list, rather than giving me the option to choose from it. I have them set as lookups, but I am certainly doing something wrong.

Thanks

Hello @ChristianWentz,

Please share the screenshot of the lists and forms that you already designed. That'll give me the whole picture of what you have and I'll help to fix any mistakes.

Thanks! you guys are the best! I will get them together shortly.

1 Like

The main list is JOB INFORMATION. The attached screenshots are the Job Information form. The Job Information list is a TASK list and the dates and times of the jobs are then aggregated on the task calendar and timeline.

At present there is nothing configured because I started over.

The Sub Contractors list was just a contact list. Since I wanted to be able to pull from that list based on their certifications and availability, I created a relational list. The subcontractors contacts, and another list that defines availability and certifications.

For the Rentals, I did something similar. One list was the rental companies contacts. The other list was the equipment that we would rent, filtered by availability - much like the subcontractors.

I wanted to be able to accomplish three things.

  1. Add a new subcontractor contact (email, phone number, notes, etc) or new Rental Company and new equipment.
  2. Choose from the drop downs a subcontractor, or a rental company and the equipment
  3. Because each job may require many subcontractors and many different rental companies and equipment, choose as many of them as I need in order to fulfill the requirements of each job.

I hope that is enough. Let me know if you need more.

@ChristianWentz,

Thank you for the details. Let's focus on Subcontractors first.

Please share the screenshot of the subcontractor's source list. How do you want Job Information form and subcontractor's list to be connected?

You can create an example, for instance in Excel, so I could understand the dependence.

Uploading: 20201119_130947.jpg... Hi!

Well, it is really simple to do for a single entry. I have it like this:

Subcontractors Contact List: Name, Email, Phone, Notes, Status (choice drop down)

The availability of the subcontractor is filtered by status (active, inactive, or in review)

Each subcontractor also has different specialties and certifications. So using a lookup to filter for those kinds of certifications is also required.
So just like the status, there are 15 different types of certifications available. The problem is that each certification is in its own list in 15 different columns and each contractor may have multiple certifications.

If we want to look for a subcontractor that is certified in lead removal, choosing the "lead removal" certification from a dropdown, then populating the "Subcontractor name" drop down with the subcontractors that are available is what I want to accomplish. I truly have no idea how to make this work.

At present, I have one subcontractor choice set up on the Job information form. I have no idea how to add another once I have chosen one.

I drew a relationship map. I hope this helps:

@ChristianWentz,

As I previously mentioned, you need to use the List or Library control to store the list of subcontractors related to the job.

The tricky point is to design the form for adding a subcontractor.

How does certificates from multiple lists are connected with the list of subcontractors? Do you bind it by the name of the subcontractor or using lookup?

1 Like

Good morning -

Honestly the part I have the most problem with is being able to add one subcontractor to the Jobs List, then add another, and another, and another - as many as I need. I can add one at a time. How do I go about adding more than one? The lookups I think I have figured out.

For example, Job A may have 9 subcontractors. My form allows me to add just one. How do I go about adding as many as I need?

This is the most urgent part of my problem. The lookups for certifications and availability are an afterthought.

THank you!

@ChristianWentz,

One option is to allow multiple values to be selected in the Lookup field.

image

The second option is to create a list SubcontractorsAssignedToJob, add List or Library control to add/display subcontractors on the job form. For instance, the same way it done for the list of items on the purchase order form.
image

Please find more information and instructions here.

1 Like

Got it. I had created my subcontractors lookup with ALLLOW MULTIPLE VALUES checked. It did not provide me with any further options in the form.

You had shared with me the List/Library option before and perhaps I simply did not understand it. I shall give it another shot. Thanks for your time.

1 Like

Hi mnikitina - I am able to follow these instructions for creating two lists "parent and child" and am able to get the lookup to work, etc.
How would I then be able to aggregate them in yet a THIRD list (Job Information) so that my end users are working from a single space. Is that possible?

@ChristianWentz,

You need to add List or Library control to the Job information form and set its data source to point to the list that stores the subcontractors that are related to the job. The filtration is made by the lookup field.

1 Like

Mornin!
Yeah, I had tried using the List or Library control when we started this thread and that is where my frustration came in.

I have the parent (subcontractors)
The child (Resources) lookup that pulls from the parent (subcontractors)
I designed forms for both.
I put the List or Library Control in the Job Information form.
In the Data Source Editor I choose the child list and the lookup field says (NONE)
I have also just created two test lists. Parent, Child, and set the Child to lookup from the parent. I get the same thing. The Data Source Editor gives me (NONE) in the lookup field.

I am terribly sorry to be so difficult. As a network security engineer I know what it is like to have to deal with someone that seems unable to follow instructions or is clearly not listening. :slight_smile:

Hello @ChristianWentz,

No problem, I'm here to help you out.

Please share the screenshot of both test lists you've created, the list view like this:

and the settings of the lookup field:

And the List or Library control DataSource Settings:
image