Forum Discussion

SaviNewman1's avatar
Qrew Trainee
3 years ago

Inventory Table to Job Table

I have a relationship between two tables.

The Set Up We Have:
Table 1= Service Calls, these calls will have an End Of Job report that include the following:
What they did on the job for the customer in one field.
What water samples were taken for the customer in a separate field.
What parts were used for the customer on site in a separate field (something they type in manually)

Table 2= Inventory, this inventory is imported from our QuickBooks account and is refreshed multiple times a day.

Problem we're having:
We're currently manually typing in parts we're using for each job - everyone calls parts in our industry something different, which causes issues for our billing department because they often don't know what to bill based on what was typed in by our service technicians. 

What We WANT To Do About It:
We currently have a relationship between our two tables, service calls & inventory - we want to be able to have our service technicians type the part they want to input (we've done this) but, we can't add multiple, we cannot use the "multi select text" feature because it then jumbles the order of the parts inputted and we need to be able to put the QTY used next to each line item in order, we can search and select from 8,000 parts we use and the multi select tool only allows for up to 20 choices, sometimes we use upward of 30-50 parts per visit. (We're in the water well industry.)
^Search and select

Now, I was thinking? Well, maybe I can have a report link that adds what you've selected into that field, which it did as shown below:

It now copies what's selected below, but as soon as I change the part chosen, it goes away with it. It won't stay!

Those are the two things I have tried.
I noticed I cannot log edits to any field that is a proxy, formula, etc, I can only do it to normal text values - which would turn me back to our original issue.

Solution we desire:
To be able search and select our parts, no matter how many, allow our service technicians to choose what parts they used for that job, and have it listed as parts used.

Bonus love if you can make all this possible with a spot for QTY used without me having to make A LOT of different fields for QTY Used next to each part used.

Sorry for the novel, thank you!

Newman, Savi

2 Replies

  • You "Inventory" file seems actually be a list of unique Items that you sell.

    The setup should be like this.

    Service Call < Service Call Items > Inventory Item Masters 

    What that means is one service call has many service call items and also that one Inventory Item Master is used on many service call items.

    Once you have that set up there you will put the Report link field on the Service Call form of all the items which were used.  Create a field there  for the quantity. 

    Once you have that structure in place you will likely find that the speed of entry is too slow for your liking because you're saying you could have 30 or 40 line items on a service order.  I can help you with a quick select method to allows a user to very quickly select items off the master item list and then having select the items you would go back to the service call record in grid edit mode and update the quantities. For that quick select method we would have to work together off-line but it would only take about an hour of consulting time to set up.

    Mark Shnier (YQC)
    • SaviNewman1's avatar
      Qrew Trainee
      Yes, I would appreciate a quick select method - I do not want to take more time than we have to to get this done.

      I would love to do a screen share together or something to get this set up and I can learn what you're doing as well.

      Newman, Savi