Custom AutoFill

[NewGuy79]

Hey All,

I am working on a small database for our inventory team and need some help with Autofill.

In the DB we are tracking serial number, model number, and the sales person that picks the item up from our inventory room.

All our serial numbers are 11 or 12 digits long (letters and numbers) and the last 3 digits correspond to their model.

When we scan the serial I would like to autofill the model field based on the last 3 digits of the serial.

I have a table that tracks the last 3 of the serial and the models that match.

What is the best way to calculate and autofill the model based on the last 3 of the serial?

Thanks in advance,

Zach

[comment]

Why not use a calculation field =

Right ( SerialNumber ; 3 )

[NewGuy79]

What would the full calculation look like for this?

If I have 2 fields, 1 named SerialSufix and 1 named ModelNumber, would I need a 3rd field to hold the result?

I've never used calculations before.

[comment]

Here's what confusing:

All our serial numbers are 11 or 12 digits long (letters and numbers) and the last 3 digits correspond to their model.

If I have 2 fields, 1 named SerialSufix and 1 named ModelNumber,

If you have a field named SerialNumber that contains the 11/12 characters of the serial number, then ModelNumber should be a Calculation field using the above formula, with result type set to Text.

[NewGuy79]

Sorry, I wasn't very clear on the model numbers.

We deal with electronics and lets say the model number for a TV is "ABCD" The serial number for that TV ends with "1B3".

When we scan the serial number I would like the ModelNumber field to Autofill with the "ABCD"

I understand how the calculation provided works but not sure how to use it for what I am trying

Thanks

[comment]

OK, now you have lost me completely. How is "1B3" supposed to produce "ABCD"? Do you have a table that associates "1B3" with "ABCD"? And if so, is it a one-to-one match (i.e. ALL serial numbers that end with "1B3" belong to model "ABCD", and ALL serial numbers that belong to model "ABCD" end with "1B3")?

[NewGuy79]

Yes I have a table that has two fields Model_Number and Serial_Suffix. It is always a one-to-one match for model and serial numbers.

When we receive a new model in, I have a very simple portal where our inventory team puts in the new model number and the serial suffix that is associated with that new model.

I have a 2nd portal, the main layout, where we can track what salesperson picked up what item. Right now we have to scan the serial number AND scan the model number.

I would like to eliminate the scanning of the model number and have it autofill since this information is already contained in the serial number.

[comment]

OK, that makes it very simple. Two tables: Models and Pickups(?). The Pickups table has these two fields:

• SerialNumber, Text (scanned)

• cSNSuffix, Calculation = Right ( SerialNumber ; 3 )

Now define a relationship between the two tables as:

Pickups::cSNSuffix = Models::Serial_Suffix

and place the field Models::Model_Number on the layout of Pickups (make it non-enterable, so that they cannot modify it accidentally).

[NewGuy79]

That works perfect. Thank you for all the help.