You don't need a script to look up data on a selected part. Looked up value settings can copy data from your parts table into your line items table and this leaves the data in fields where you can easily edit the data when the need, such as your new, no part number item, arises.
That way, you can format your units of measure field with a value list. It's possible to use a conditional value list for this such that you only see one unit of measure if you have a part # entered and all units of measure if you don't. This takes some behind the scenes slight of hand to do.
Before I go there, do you know how to set up conditional value lists?
I'm comfortable with conditional value lists, but don't know what to do if there is no part for the value list to look up (i.e. the value list would be empty).
That's where we'd have to get a bit sneaky so that you do have a value in a hidden field that controls the values in your value list.
Define this calculation field, cUnitsKey, in your line items table:
If ( IsEmpty ( PartNumber ) ; 0 ; PartNumber )
Clear the "do not evaluate if all referenced fields are blank" check box.
Define this calculation field, cUnitsKey, in your Parts table:
List ( 0 ; PartNumber )
Both of these calculation fields should have "text" specified as their return types.
Now you can create a new table occurrence of your Parts table and link it like this:
LineItems::cUnitsKey = PartsUnitsKey::cUnitsKey
Now your conditional value list can list the Units field from PartsUnitsKey, include only related values, starting from LineItems. (LineItems would be the name of the table occurrence listed in "show related records from" in portal setup on your quotes layout.)
With this value list, you'll see a value list of just one unit, the value stored in the Parts table for that one part when you specify a part number. If the field is blank, that 0 value will match to all records in your Parts table and you'll see all units currently present in the Parts table. (You don't have to use 0 for this. Any value will do as long as it does not match to any possible part number.)
I was guessing we would need to do a match field of some kind, but I knew that if I tried to re-invent the method it would take me 3 or 4 nights to finally nail it down. Thank you for your help.
After reviewing your post, I feel like it would be better just to script the drop down and always include all the Units of measure. If there is a part number linked and the user tries to change it, it will just revert back. If there is no part number, then they user can select any Unit.
I just like adding more scripts than calculation items and relationships in my system.
Just a thought.
Do you know how to do that?
It can be done with a script trigger on the drop down. The script can check the part number field and update the field with the value from the related parts table and leave it unchanged if it does not. It may take some extra scripting to keep this from being an infinite loop...