Set Field by value chosen from drop down list

[phip]

Hey everyone,

I am working on a database for a metal recycling company.

I have 2 Tables. A customer table and a service order table. They both act as different modules (customer module and invoice module). I have relationships set between the two so that when I start a new service order from a customer it automatically sets name, phone number, and address to its appropriate value. On the customer module, I set up a metal pricing list so that the owner can set the price per pound paid for each metal type (ie Customer ABC TOWING has thier prices set to: brass is 10 cents per pound, copper is 13 cents per pound, steel is 18 cents per pound, etc).

On the invoice module, I set up a portal to calculate the price paid to the customer for all of their metal.

I have 4 fields in the portal: metal type (drop down list), Price per pound, Total Weight, and Total paid.

This is what I need to happen:

When I select Brass from the drop down menu, I need the field "price per pound" to be automatically set to the price that customer gets paid per pound for brass from their pricing list in the customer module. (ie ABC towing is set to 10 cents per pound for brass, so when i select brass from the drop down menu, .10 is automatically entered into the field "price per pound")

I am assuming it may be something along the lines of:

If (Metal Type) = Brass Set Field (Price Per Pound) to (Brass Price)

If (Metal Type) = Copper Set Field (Price Per Pound) to (Copper Price)

I may be way off, but this is what I am thinking along the lines of.

Please Help!!

Thank You

[Vaughan]

You're going to need a lot more tables.

The metal prices need to be in their own table, and related to the Company. This table can also be related to the invoices, but usually invoices have a LineItems table as well.

[phip]

I do have a line items table set up. The portal is for LineItems. Metal Type, Price Per Pound, Total Weight, and Subtotal are the 4 fields I have in the portal that are being pulled from my lineitems table.

I can figure out the relationship aspect, I just need to know how to set up the If-Then command. Would I do a calculation? or would it be something done with a script?

[Vaughan]

It won't be an If-Then.

Create a relationship between the Invoices table and the Prices table, and use CompanyID and Metal fields for the relationship. As long as there is only one Metal record for each company, the price will relate correctly when the CompanyID is selected in the Invoice.

[skingjack2]

A little off topic but mentioned in this thread: For the more advanced users, this is old hat but for newbies I'm encountering a lot of frustration

with the line described below:

"I have relationships set between the two so that when I start a new service order from a customer it automatically sets name, phone number, and address to its appropriate value"

My issue is with having this information displayed automatically. My solution (for lack of a better name) has 2 databases (booking, vendor) related by vendor IDs. The problem is that I have to manually enter/copy the vendorID from vendor table into the vendor ID field in the booking table for the information to update there. Any thoughts on how I can get the vendor id from the vendor table to copy to the vendor ID in the booking detail without resorting to copy/paste. I've struggled for some time with this;I'm sure there is an easy/obvious answer but open to some enlightenment. Assistance welcomed

Jack

[Vaughan]

Create a value list that references the VendorID field in the Vendor database. It can be set up to also display the vendor name to make selection easier.

Use this value list to assist data entry on the VendorID field in the Bookings table.

Or create the bookings related records through a portal from the Vendors table directly. Set the relationship to Bookings to allow the creation of related records.

[skingjack2]

Sorry Vaughan... I'm not following. Here is the steps I've followed that has got me to the point where I'm at so far...

1) drag new field (which will be the vendor name) onto the booking table ; FM asks you to specify field

2) from specify field, I chose the vendor table as source & vendor name as the desired field

3) from the field/control -> setup menu I chose pop up menu as display & display values

from a previously created value list called "vendors"

Is this not correct or is there a step I'm overlooking?

Jack

[mpau]

I have a similar question and did not find the answer in the posted solutions. I have a company where we do four different types of sales (wholesale, retail, drop-ship and to the trade.) Depending on the type of sale the unit price is different. I was hoping that I would be able to set up a relational or lookup calculation for unit price based on which type of sale was selected in the value list drop down. Is this possible?

for example, if 'wholesale' is selected in the value list drop down, that the unit price field will populate with the 'wholesaleprice' for the item. If 'retail' is selected than it will populate with the 'retailprice' for the item, etc.

Does anyone know if this is possible? All other solutions I've come up with seem far too clunky to me.