How to go about this:

Have 6 items, call them item A, B, C etc.
I want to put these items in a drop-down (box) field were users select the
item on input, say item "A" needs to be added to the database. But I also
need to have item 'A' linked to a serial number field.
Sofar so good, the thing I would like to do now is, that when the user
selects item 'B' or 'C' I need it to pick up a different serial number.

Item 'A' = serial number A100, A101 etc.
Item 'B' = serial number B40, B41 etc.

The other thing also is that on creation of a new record these serial
number values do not increase automatically but only for the value linked to
the Item selection.


Hope someone can give me some info or sample on how to go about this and
hope explanation is not all that confusing.

Thanks in advance,
Paul

Kiel Industries (Morwell) <kielindustries (AT) bigpond (DOT) com> wrote:

I'd put the A, B, C etc information and the serial numbers in different
fields, and then use a calc field or a merge field to disply A-nnn etc.

--
http://clk.ch

Kiel Industries (Morwell) wrote:

I assume you have two tables, one for selected/added to order... items,
and one for unique items

Your Item dropbox field: select the (text) item, use a second field
Item_SN to lookup the item serial number from the table containing the
unique items. This way the SN is 'hard' coded into the record. The
relationship for this lookup will be itemname::itemname


2 tables right?

regards

Chris Brown
University of Adelaide

In article <FHhOh.2586$M.1021 (AT) news-server (DOT) bigpond.net.au>, "Kiel
Industries \(Morwell\)" <kielindustries (AT) bigpond (DOT) com> wrote:

It's usually a better idea to stick to a normal serial number field and
simply append the A, B, etc. to the front.
eg.
SerialNum Number Auto-enter Serial Number
Increment by 1

RecordSerialNum Text Auto-enter calculation
= If (IsEmpty(DataField),
"",
DataField & SerialNum)

where DataField is the one being used to store the A, B, etc.

This way you will get records like:

Record 1 DataField = B RecordSerialNum = B1
Record 2 DataField = D RecordSerialNum = D2
Record 3 DataField = A RecordSerialNum = A3
Record 4 DataField = D RecordSerialNum = D4
etc.

This way there are no problems with serial numbers being re-used when
records are deleted. For example, if you delete Record 4 above and then
create a new one that is still D you will get:

Record 4 DataField = D RecordSerialNum = D5

RecordSerialNum could be a normal Calculation field, but if you're
going to be using it for the child side of a Relationship then it can
not be a Calculation field and must instead be an auto-enter. In newer
versions of FileMaker you can have the auto-enter re-calculate if the
value of DataField is changed, but in older ones you're stuck with
using a script. For example, changing the value of DataField in Record
3 above from A to C will still leave RecordSerialNum as A3 when using
an auto-enter field.

You can get around this last problem by not bothering to have the
separate RecordSerialNum field at all and simply put the two fields
next to each other on the layouts wherever the combined version is
needed.
eg.
[DataField] [SerialNum]

Internally your database will just use SerialNum to identify each
record uniquely.




Now, if instead you do still want a different "serial number" for each
record type, then you need to create a Relationship that groups
together all records of the same DataField value.
eg.
rel_SameType Link from DataTable to DataTable
when DataField = DataTable:ataField

With this in place you can now use functions like Count and Max to work
out what the new "serial number" for a new record will be beased on
which value of A, B, etc. is in DataField. You can work out what the
"serial number" should be for each new record by using an auto-enter
Calculation.
eg.
SerialNum Number Auto-enter calculation
= Case (IsEmpty(DataField),
"",
Count(rel_SameType:ataField) = 0,
1,
Max(rel_SameType::RecordSerialNum) + 1)

RecordSerialNum Text Auto-enter calculation
= If (IsEmpty(DataField),
"",
DataField & SerialNum)

Note 1: Because the fields' auto-enter calculations use each other, you
must first create both fields withOUT the auto-enter calculations, then
go back and put in the auto-enter Calculation for SerialNum and then
put in the auto-enter calculation for RecordSerialNum.

Note 2: There is a bug in FileMaker here (or at least some versions).
These auto-calculations work perfectly ... until you try to later
re-edit the SerialNum auto-enter calculation, at which time FileMaker
complains about a circular reference (which is technically correct, but
realistically incorect). This means you have to turn off the auto-enter
calculation for RecordSerialNum first, edit the SerialNum field and
then turn the RecordSerialNum back on. \

Note 3: Do NOT use the option in newer versions of FileMaker to update
the auto-enter calculation when fields change (hopefully it won't even
let you!). This WILL cause a circular reference and probably cause
FileMaker to crash the file in a never ending loop.


This will give you the "serial number" format you want with records
like:

Record 1 DataField = B RecordSerialNum = B1
Record 2 DataField = D RecordSerialNum = D1
Record 3 DataField = A RecordSerialNum = A1
Record 4 DataField = D RecordSerialNum = D2
etc.

But there is a problem. If you delete the last record of any DataField
value, then the same serial number will be re-used. For example, if you
delete Record 4 above and then create a new one you will get the same
serial number used again:

Record 4 DataField = D RecordSerialNum = D2

Again, because these are auto-enter calculations they will not alter if
you later change the value in DataField. For example, changing the
value of DataField in Record 3 above from A to C will still leave
RecordSerialNum as A1.

The only way around this problem of re-used serial numbers is to use a
script to create new records (disabling the creation of new records via
the keyboard / menu commands) and store the next serial number value in
Global fields.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)