Hello,

I am currently building a catalog for a library and I have run into a
problem with one part of it. The scenario is this:

Some of the items in the library are serials (such as magazines, multi-
volume encyclopedias, etc.). Rather than catalog each instance of
these serials separately, I have created one master record for the
item (e.g. "Grolier's Encyclopedia") and then enter its constituent
parts (e.g. "Vol 1: A-B, Vol. 2 C-E") into a related "serials"
table.

The problem that I am running into is this:

I would like these child records to have an ID that inherits the
parent record 's ID and is then followed by an incremental suffix
(e.g. "_001, _002, _003" and so forth).

So if the Parent ID of Grolier's Encyclopdedia is 100507, then Vol. 1:
A-B would be 1005007_001 and Vol 2: C-E would be 1005007_002, etc.

I need this numbering system to restart at "_001" for each parent
record, so that If I move to another record (say "100508: Food
Industries"), when I enter its child record (Vol 1.), I would again
get an ID at the beginning of the sequence (in this case, Vol 1. of
Food Industries would be 100508_00).

The closest that I have come to solving this has been by using a field
in the "serials" table called serial_autoenter and then using that in
the Serial ID calculation field:

item_acquisitions::item_id & "_" & serial_autoenter

The problem with this is that it doesn't allow me to restart the
numbering system for each parent record.

Any insights or thoughts into this would be greatly appreciated.

Andrew

RLA <reanimationlibrary (AT) gmail (DOT) com> writes:

If you separate the child_id in an extra field (number) you can
calculate the max value in the parent record with

max_child_id = max(children::child_id).

For any new value of child_id you only need to add 1 to this calc
field.

Jens

--
A: Because it fouls the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

In article <1183240895.584045.63580 (AT) k79g2000hse (DOT) googlegroups.com>, RLA
<reanimationlibrary (AT) gmail (DOT) com> wrote:

You can't use a normal FileMaker serial number field since that is
affected by all records within the table (as you've discovered).


You could use an "Add Record" button and script that calculates the
Child ID as needed, but that has problems when users don't follow the
data entry rules (eg. using the keyboard shortcuts to create new
records).


You need to create a relationship within the Serials table which groups
together the records from the same parent record.
eg.
for the Serials table:
rel_SameParent Match records from Serials to Serials
when Parent ID = Serials::Parent ID

The Parent ID should already be in the Serials table since that is how
the Parent->Chiuld relationship links the related records.

Using this relationship you can "count" the number of records with the
same Parent (adding 1, otherwise you'll start at _000), and therefore
get an increasing child serial number using a field with an auto-enter
by calculation option.
eg.
Child ID Text, Auto-enter by Calculation
= Parent ID & "_" & (Max(rel_SameParent::Child ID) + 1)

Unfortuantely you can't simply use the Count function because that can
cause doubled-up Child ID numbers when a record is deleted. Instead
it's best to use the Max function and leave "holes" in the sequence
when you delete child records.



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

On Jun 30, 5:49 pm, Jens Teich <i... (AT) jensteich (DOT) de> wrote:
Unfortunately, Max doesn't work on a found set of records. However,
sorting the relationship by the ID number, in descending order, should
*show* the most recent ID number and you can add 1 to that. So if you
set up the relationship correctly, this should work:

next_child_id = children::child_id+1

In article <1183281402.965515.294570 (AT) m36g2000hse (DOT) googlegroups.com>,
Grip <grip (AT) cybermesa (DOT) com> wrote:

Max doesn't work on a "Found Set of records", but it does work via a
Relationship across related records as is being done here ... just like
any of the other aggregation functions Sum, Average, Min, etc.

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

On Jul 1, 2:59 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:
You're right. I'm not sure how I misread that. My apologies Jens.

G

In article <1183332224.378692.59640 (AT) g4g2000hsf (DOT) googlegroups.com>, Grip
<grip (AT) cybermesa (DOT) com> wrote:

That's something we all do occasionally, usually when tired or have
little time. )


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

I think scripting is you best option.

I'll post a sample here in a moment.


Sean


----------------------------
Sean Walsh, partner

SolvEnterprises, LLC
dissolving problems, designing solutions
www.dissolvingproblems.com


On 2007-06-30 17:01:35 -0500, RLA <reanimationlibrary (AT) gmail (DOT) com> said:

On Jun 30, 10:33 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:
Hello and thank you to everybody who has responded to my question. I
am still trying to solve this.

I have a question for Helpful Harry:

When you write:

You need to create a relationship within the Serials table which
groups
together the records from the same parent record.
eg.
for the Serials table:
rel_SameParent Match records from Serials to Serials
when Parent ID = Serials::Parent ID

Should "rel_SameParent" be a new field in the Serials table, or a new
table? If it is a field in Serials, should it be a calculation field
where "Parent ID = Serials::Parent ID"?

Now, when you write:

Child ID Text, Auto-enter by Calculation
This is a calculation for the Child Serial ID, right? I am confused
by "rel_SameParent::Child ID" in this calculation. Should
"rel_SameParent" be its own table? If not, what is the relationship
here between "rel_SameParent" and "Child ID" in this calculation?

Thanks!

Andrew

In article <1183479917.342265.261960 (AT) k79g2000hse (DOT) googlegroups.com>, RLA
<reanimationlibrary (AT) gmail (DOT) com> wrote:

"rel_SameParent" is not a field, it's the name I gave to the
Relationship which you have to create using the Define Relationships
option in the File menu.

You've already got one Relationship that links the main table to the
Serials table, using Parent ID as the link field (ie. Parent ID in the
main table -> Parent ID in the Serials table).

For the self-updating Child ID you need to create another Reationship
that links the Serials table to itself, again using Parent ID as the
link field (ie. Parent ID in the Serials table -> Parent ID in the
Serials table). This will provide a way of grouping together the
records in the Serials table that have the same Parent ID. Using the
Max function via this Relationship means the calculation will give the
highest (maximum) value of Child ID of the related records.


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