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.com> writes:

> 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).

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.googlegroups.com>, RLA
<reanimationlibrary [at] gmail.com> wrote:

> 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.

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.de> wrote:
> RLA <reanimationlibr... [at] gmail.com> writes:
> > 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).
>
> 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?

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.googlegroups.com>,
Grip <grip [at] cybermesa.com> wrote:

> On Jun 30, 5:49 pm, Jens Teich <i... [at] jensteich.de> wrote:
> > RLA <reanimationlibr... [at] gmail.com> writes:
> > > 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).
> >
> > 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.
>
> 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

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.de.plume.com>
wrote:
> In article <1183281402.965515.294... [at] m36g2000hse.googlegroups.com>,
>
>
>
> Grip <g... [at] cybermesa.com> wrote:
> > On Jun 30, 5:49 pm, Jens Teich <i... [at] jensteich.de> wrote:
> > > RLA <reanimationlibr... [at] gmail.com> writes:
> > > > 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).
>
> > > 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.
>
> > 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
>
> 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)

You're right. I'm not sure how I misread that. My apologies Jens.

G

In article <1183332224.378692.59640 [at] g4g2000hsf.googlegroups.com>, Grip
<grip [at] cybermesa.com> wrote:

> On Jul 1, 2:59 pm, Helpful Harry <helpful_ha... [at] nom.de.plume.com>
> wrote:
> > In article <1183281402.965515.294... [at] m36g2000hse.googlegroups.com>,
> >
> >
> >
> > Grip <g... [at] cybermesa.com> wrote:
> > > On Jun 30, 5:49 pm, Jens Teich <i... [at] jensteich.de> wrote:
> > > > RLA <reanimationlibr... [at] gmail.com> writes:
> > > > > 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).
> >
> > > > 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.
> >
> > > 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
> >
> > 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.
>
> You're right. I'm not sure how I misread that. My apologies Jens.

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


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.com> said:

> 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

On Jun 30, 10:33 pm, Helpful Harry <helpful_ha... [at] nom.de.plume.com>
wrote:
> In article <1183240895.584045.63... [at] k79g2000hse.googlegroups.com>, RLA
>
>
>
> <reanimationlibr... [at] gmail.com> wrote:
> > 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.
>
> 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)

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
> = Parent ID & "_" & (Max(rel_SameParent::Child ID) + 1)

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.googlegroups.com>, RLA
<reanimationlibrary [at] gmail.com> wrote:

> On Jun 30, 10:33 pm, Helpful Harry <helpful_ha... [at] nom.de.plume.com>
> wrote:
> > In article <1183240895.584045.63... [at] k79g2000hse.googlegroups.com>, RLA
> >
> >
> >
> > <reanimationlibr... [at] gmail.com> wrote:
> > > 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.
> >
> > 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)
>
> 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
> = Parent ID & "_" & (Max(rel_SameParent::Child ID) + 1)
>
> 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?

"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)

On Jul 3, 4:56 pm, Helpful Harry <helpful_ha... [at] nom.de.plume.com>
wrote:
> In article <1183479917.342265.261... [at] k79g2000hse.googlegroups.com>, RLA
>
>
>
> <reanimationlibr... [at] gmail.com> wrote:
> > On Jun 30, 10:33 pm, Helpful Harry <helpful_ha... [at] nom.de.plume.com>
> > wrote:
> > > In article <1183240895.584045.63... [at] k79g2000hse.googlegroups.com>, RLA
>
> > > <reanimationlibr... [at] gmail.com> wrote:
> > > > 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 masterrecordfor 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 thesechildrecords to have an ID that inherits the
> > > > parentrecord's ID and is then followed by an incrementalsuffix
> > > > (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 anotherrecord(say "100508: Food
> > > > Industries"), when I enter itschildrecord(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 parentrecord.
>
> > > > Any insights or thoughts into this would be greatly appreciated.
>
> > > 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 "AddRecord" button and script that calculates the
> > >ChildID 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 parentrecord.
> > > 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 increasingchildserial number using a field with an auto-enter
> > > by calculation option.
> > > eg.
> > > ChildID Text, Auto-enter by Calculation
> > > = Parent ID & "_" & (Max(rel_SameParent::ChildID) + 1)
>
> > > Unfortuantely you can't simply use the Count function because that can
> > > cause doubled-upChildID numbers when arecordis deleted. Instead
> > > it's best to use the Max function and leave "holes" in the sequence
> > > when you deletechildrecords.
>
> > > Helpful Harry
> > > Hopefully helping harassed humans happily handle handiwork hardships ;o)
>
> > 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 parentrecord.
> > 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:
>
> >ChildID Text, Auto-enter by Calculation
> > = Parent ID & "_" & (Max(rel_SameParent::ChildID) + 1)
>
> > This is a calculation for theChildSerial ID, right? I am confused
> > by "rel_SameParent::ChildID" in this calculation. Should
> > "rel_SameParent" be its own table? If not, what is the relationship
> > here between "rel_SameParent" and "ChildID" in this calculation?
>
> "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-updatingChildID 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 ofChildID of the related records.
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)

Hi Harry,

Thank you so much for taking the time to help me with this.

So I have created the new "Same Parent" relationship within the
Serials table and I plugged in the calculation that you wrote into an
auto enter by calculation text field:

Parent ID & "_" & (Max(rel_SameParent::ChildID) + 1)

The result of this is looks like this:

For the first child record of a parent ID called "100506", I get
"100506_1", but for the second, I get "100506_1005062" and for the
third, I get "100506_1005061005063" and so forth. Because the
calculation is being based on the previous child id, it seems to be
pulling in the whole thing before putting the new increment at the
end. I have been banging my head against the wall, trying to tweak
the calculation to get it working, to no avail. Any ideas on how I
could change the calculation to get a suffix of "_001", "_002",
"_003", etc?

Thanks again for all of your insights.

Andrew

In article <1183665309.510124.108730 [at] n2g2000hse.googlegroups.com>, RLA
<reanimationlibrary [at] gmail.com> wrote:

> Hi Harry,
>
> Thank you so much for taking the time to help me with this.
>
> So I have created the new "Same Parent" relationship within the
> Serials table and I plugged in the calculation that you wrote into an
> auto enter by calculation text field:
>
> Parent ID & "_" & (Max(rel_SameParent::ChildID) + 1)
>
> The result of this is looks like this:
>
> For the first child record of a parent ID called "100506", I get
> "100506_1", but for the second, I get "100506_1005062" and for the
> third, I get "100506_1005061005063" and so forth. Because the
> calculation is being based on the previous child id, it seems to be
> pulling in the whole thing before putting the new increment at the
> end. I have been banging my head against the wall, trying to tweak
> the calculation to get it working, to no avail. Any ideas on how I
> could change the calculation to get a suffix of "_001", "_002",
> "_003", etc?
>
> Thanks again for all of your insights.

Sorry. That was my mistake.

When you try to calculate TextField + 1, FileMaker converts TextField
to a number, removing any excess non-numeric characters, and then adds
1. That means "100506_1 +1" calculates as "1005061 + 1", which is not
what you want. It's is also appending this entire number to the
original Parent ID again giving an increasing double-up of the Parent
ID.

Apologies for any confusion.

The easiest way to get what you need would be to create a separate new
field with an Auto-enter Calculation that is just the end of the Child
ID number (ie. the "001", "002", etc. part)
eg.

ChildID_suffix Text, Auto-enter by Calculation
= Right("00" & Max(rel_SameParent::ChildID_suffix) + 1, 3)

Note that the Max function now uses the new ChildID_suffix field via
the relationship so that it's only getting the highest suffix.

I've also added the extra "00" and Right function part to the
calculation so that you get the three digits - this will be a problem
if one parent record ever has more than 999 child records.

You can then change the ChildID Auto-enter Calculation to simply append
this to the Parent ID, first making sure that ChildID_suffix actually
contains data before performing the Auto-enter, otherwise you might end
up with just "100506_".
eg.
ChildID Text, Auto-enter by Calculation
= If (IsEmpty(ChildID_suffix),
"",
Parent ID & "_" & ChildID_suffix
)


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

On Jul 5, 8:35 pm, Helpful Harry <helpful_ha... [at] nom.de.plume.com>
wrote:
> In article <1183665309.510124.108... [at] n2g2000hse.googlegroups.com>, RLA
>
>
>
> <reanimationlibr... [at] gmail.com> wrote:
> > Hi Harry,
>
> > Thank you so much for taking the time to help me with this.
>
> > So I have created the new "Same Parent" relationship within the
> > Serials table and I plugged in the calculation that you wrote into an
> > auto enter by calculation text field:
>
> > Parent ID & "_" & (Max(rel_SameParent::ChildID) + 1)
>
> > The result of this is looks like this:
>
> > For the first child record of a parent ID called "100506", I get
> > "100506_1", but for the second, I get "100506_1005062" and for the
> > third, I get "100506_1005061005063" and so forth. Because the
> > calculation is being based on the previous child id, it seems to be
> > pulling in the whole thing before putting the new increment at the
> > end. I have been banging my head against the wall, trying to tweak
> > the calculation to get it working, to no avail. Any ideas on how I
> > could change the calculation to get a suffix of "_001", "_002",
> > "_003", etc?
>
> > Thanks again for all of your insights.
>
> Sorry. That was my mistake.
>
> When you try to calculate TextField + 1, FileMaker converts TextField
> to a number, removing any excess non-numeric characters, and then adds
> 1. That means "100506_1 +1" calculates as "1005061 + 1", which is not
> what you want. It's is also appending this entire number to the
> original Parent ID again giving an increasing double-up of the Parent
> ID.
>
> Apologies for any confusion.
>
> The easiest way to get what you need would be to create a separate new
> field with an Auto-enter Calculation that is just the end of the Child
> ID number (ie. the "001", "002", etc. part)
> eg.
>
> ChildID_suffix Text, Auto-enter by Calculation
> = Right("00" & Max(rel_SameParent::ChildID_suffix) + 1, 3)
>
> Note that the Max function now uses the new ChildID_suffix field via
> the relationship so that it's only getting the highest suffix.
>
> I've also added the extra "00" and Right function part to the
> calculation so that you get the three digits - this will be a problem
> if one parent record ever has more than 999 child records.
>
> You can then change the ChildID Auto-enter Calculation to simply append
> this to the Parent ID, first making sure that ChildID_suffix actually
> contains data before performing the Auto-enter, otherwise you might end
> up with just "100506_".
> eg.
> ChildID Text, Auto-enter by Calculation
> = If (IsEmpty(ChildID_suffix),
> "",
> Parent ID & "_" & ChildID_suffix
> )
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)

Hi Harry,

Thanks again for taking the time to help me out with this. I am
tantalizingly close to having this working (thanks to you), but I have
run into one minor problem:

The child id numbering system now works perfectly when entering data
directly into the Serials table, but I would like to be able to enter
serials data from my main data entry page. This is where the problem
is. Currently, from my main data entry page, I have a portal into the
Serials table with the following fields: child id, serial number,
serial volume, and serial title. I would like to be able to enter data
into either, serial number, serial volume, or serial title and
generate a new record that shows the incremental change in the child
id. Right now, if I enter data into any of these field, I do get a
new child id (let's say 100506_001), but then, unless I click out of
the portal, the next record in the portal is assigned the same child
id. If I click out of the portal, or the record, and come back to it,
then the incremental change occurs. I have been playing around with
many different portal properties, but I can't get anything to work.
I'm stumped.

Any suggestions?

Thanks so much!

Andrew

In article <1184009956.120423.291570 [at] q75g2000hsh.googlegroups.com>, RLA
<reanimationlibrary [at] gmail.com> wrote:
>
> Hi Harry,
>
> Thanks again for taking the time to help me out with this. I am
> tantalizingly close to having this working (thanks to you), but I have
> run into one minor problem:
>
> The child id numbering system now works perfectly when entering data
> directly into the Serials table, but I would like to be able to enter
> serials data from my main data entry page. This is where the problem
> is. Currently, from my main data entry page, I have a portal into the
> Serials table with the following fields: child id, serial number,
> serial volume, and serial title. I would like to be able to enter data
> into either, serial number, serial volume, or serial title and
> generate a new record that shows the incremental change in the child
> id. Right now, if I enter data into any of these field, I do get a
> new child id (let's say 100506_001), but then, unless I click out of
> the portal, the next record in the portal is assigned the same child
> id. If I click out of the portal, or the record, and come back to it,
> then the incremental change occurs. I have been playing around with
> many different portal properties, but I can't get anything to work.
> I'm stumped.
>
> Any suggestions?
>
> Thanks so much!

I can't see anywhere that you mentioned which version of FileMaker
you're using, but it sounds like it's one of the newer ones and you're
hitting a problem caused by FileMaker not committing records.
Unfortunately I'm not sure how you get around that (I'm still using
FileMaker 5.5) and every time I see it rear it ugly head I shudder at
the thought of having to deal with the silliness when I eventually
upgrade - it seems ridiculous to me that when you enter data it's not
actually saved. :o(

You could try making the fields in the Portal into buttons which run
scripts along the lines of:

If [IsEmpty(PortalRelationship::ChildID)]
Commit Records
End If
Go To Field [PortalRelationship::***]

where *** is the name of the field the script is for.


By the way, you don't NEED to have Child ID displayed in the Portal,
but you can of course have it there if you want to.


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

On Jul 9, 1:39 pm, RLA <reanimationlibr... [at] gmail.com> wrote:
> On Jul 5, 8:35 pm, Helpful Harry <helpful_ha... [at] nom.de.plume.com>
> wrote:
>
>
>
> > In article <1183665309.510124.108... [at] n2g2000hse.googlegroups.com>, RLA
>
> > <reanimationlibr... [at] gmail.com> wrote:
> > > Hi Harry,
>
> > > Thank you so much for taking the time to help me with this.
>
> > > So I have created the new "Same Parent" relationship within the
> > > Serials table and I plugged in the calculation that you wrote into an
> > > auto enter by calculation text field:
>
> > > Parent ID & "_" & (Max(rel_SameParent::ChildID) + 1)
>
> > > The result of this is looks like this:
>
> > > For the first child record of a parent ID called "100506", I get
> > > "100506_1", but for the second, I get "100506_1005062" and for the
> > > third, I get "100506_1005061005063" and so forth. Because the
> > > calculation is being based on the previous child id, it seems to be
> > > pulling in the whole thing before putting the new increment at the
> > > end. I have been banging my head against the wall, trying to tweak
> > > the calculation to get it working, to no avail. Any ideas on how I
> > > could change the calculation to get a suffix of "_001", "_002",
> > > "_003", etc?
>
> > > Thanks again for all of your insights.
>
> > Sorry. That was my mistake.
>
> > When you try to calculate TextField + 1, FileMaker converts TextField
> > to a number, removing any excess non-numeric characters, and then adds
> > 1. That means "100506_1 +1" calculates as "1005061 + 1", which is not
> > what you want. It's is also appending this entire number to the
> > original Parent ID again giving an increasing double-up of the Parent
> > ID.
>
> > Apologies for any confusion.
>
> > The easiest way to get what you need would be to create a separate new
> > field with an Auto-enter Calculation that is just the end of the Child
> > ID number (ie. the "001", "002", etc. part)
> > eg.
>
> > ChildID_suffix Text, Auto-enter by Calculation
> > = Right("00" & Max(rel_SameParent::ChildID_suffix) + 1, 3)
>
> > Note that the Max function now uses the new ChildID_suffix field via
> > the relationship so that it's only getting the highest suffix.
>
> > I've also added the extra "00" and Right function part to the
> > calculation so that you get the three digits - this will be a problem
> > if one parent record ever has more than 999 child records.
>
> > You can then change the ChildID Auto-enter Calculation to simply append
> > this to the Parent ID, first making sure that ChildID_suffix actually
> > contains data before performing the Auto-enter, otherwise you might end
> > up with just "100506_".
> > eg.
> > ChildID Text, Auto-enter by Calculation
> > = If (IsEmpty(ChildID_suffix),
> > "",
> > Parent ID & "_" & ChildID_suffix
> > )
>
> > Helpful Harry
> > Hopefully helping harassed humans happily handle handiwork hardships ;o)
>
> Hi Harry,
>
> Thanks again for taking the time to help me out with this. I am
> tantalizingly close to having this working (thanks to you), but I have
> run into one minor problem:
>
> The child id numbering system now works perfectly when entering data
> directly into the Serials table, but I would like to be able to enter
> serials data from my main data entry page. This is where the problem
> is. Currently, from my main data entry page, I have a portal into the
> Serials table with the following fields: child id, serial number,
> serial volume, and serial title. I would like to be able to enter data
> into either, serial number, serial volume, or serial title and
> generate a new record that shows the incremental change in the child
> id. Right now, if I enter data into any of these field, I do get a
> new child id (let's say 100506_001), but then, unless I click out of
> the portal, the next record in the portal is assigned the same child
> id. If I click out of the portal, or the record, and come back to it,
> then the incremental change occurs. I have been playing around with
> many different portal properties, but I can't get anything to work.
> I'm stumped.
>
> Any suggestions?
>
> Thanks so much!
>
> Andrew

I've never run into this problem, but there is a checkbox on the Auto-
Enter Serial option in the Define Database dialog. That check box
offers the options of creating the serial on record creation or record
commit.

On Jul 10, 2:53 am, Grip <g... [at] cybermesa.com> wrote:
> On Jul 9, 1:39 pm, RLA <reanimationlibr... [at] gmail.com> wrote:
>
>
>
> > On Jul 5, 8:35 pm, Helpful Harry <helpful_ha... [at] nom.de.plume.com>
> > wrote:
>
> > > In article <1183665309.510124.108... [at] n2g2000hse.googlegroups.com>, RLA
>
> > > <reanimationlibr... [at] gmail.com> wrote:
> > > > Hi Harry,
>
> > > > Thank you so much for taking the time to help me with this.
>
> > > > So I have created the new "Same Parent" relationship within the
> > > > Serials table and I plugged in the calculation that you wrote into an
> > > > auto enter by calculation text field:
>
> > > > Parent ID & "_" & (Max(rel_SameParent::ChildID) + 1)
>
> > > > The result of this is looks like this:
>
> > > > For the first child record of a parent ID called "100506", I get
> > > > "100506_1", but for the second, I get "100506_1005062" and for the
> > > > third, I get "100506_1005061005063" and so forth. Because the
> > > > calculation is being based on the previous child id, it seems to be
> > > > pulling in the whole thing before putting the new increment at the
> > > > end. I have been banging my head against the wall, trying to tweak
> > > > the calculation to get it working, to no avail. Any ideas on how I
> > > > could change the calculation to get a suffix of "_001", "_002",
> > > > "_003", etc?
>
> > > > Thanks again for all of your insights.
>
> > > Sorry. That was my mistake.
>
> > > When you try to calculate TextField + 1, FileMaker converts TextField
> > > to a number, removing any excess non-numeric characters, and then adds
> > > 1. That means "100506_1 +1" calculates as "1005061 + 1", which is not
> > > what you want. It's is also appending this entire number to the
> > > original Parent ID again giving an increasing double-up of the Parent
> > > ID.
>
> > > Apologies for any confusion.
>
> > > The easiest way to get what you need would be to create a separate new
> > > field with an Auto-enter Calculation that is just the end of the Child
> > > ID number (ie. the "001", "002", etc. part)
> > > eg.
>
> > > ChildID_suffix Text, Auto-enter by Calculation
> > > = Right("00" & Max(rel_SameParent::ChildID_suffix) + 1, 3)
>
> > > Note that the Max function now uses the new ChildID_suffix field via
> > > the relationship so that it's only getting the highest suffix.
>
> > > I've also added the extra "00" and Right function part to the
> > > calculation so that you get the three digits - this will be a problem
> > > if one parent record ever has more than 999 child records.
>
> > > You can then change the ChildID Auto-enter Calculation to simply append
> > > this to the Parent ID, first making sure that ChildID_suffix actually
> > > contains data before performing the Auto-enter, otherwise you might end
> > > up with just "100506_".
> > > eg.
> > > ChildID Text, Auto-enter by Calculation
> > > = If (IsEmpty(ChildID_suffix),
> > > "",
> > > Parent ID & "_" & ChildID_suffix
> > > )
>
> > > Helpful Harry
> > > Hopefully helping harassed humans happily handle handiwork hardships ;o)
>
> > Hi Harry,
>
> > Thanks again for taking the time to help me out with this. I am
> > tantalizingly close to having this working (thanks to you), but I have
> > run into one minor problem:
>
> > The child id numbering system now works perfectly when entering data
> > directly into the Serials table, but I would like to be able to enter
> > serials data from my main data entry page. This is where the problem
> > is. Currently, from my main data entry page, I have a portal into the
> > Serials table with the following fields: child id, serial number,
> > serial volume, and serial title. I would like to be able to enter data
> > into either, serial number, serial volume, or serial title and
> > generate a new record that shows the incremental change in the child
> > id. Right now, if I enter data into any of these field, I do get a
> > new child id (let's say 100506_001), but then, unless I click out of
> > the portal, the next record in the portal is assigned the same child
> > id. If I click out of the portal, or the record, and come back to it,
> > then the incremental change occurs. I have been playing around with
> > many different portal properties, but I can't get anything to work.
> > I'm stumped.
>
> > Any suggestions?
>
> > Thanks so much!
>
> > Andrew
>
> I've never run into this problem, but there is a checkbox on the Auto-
> Enter Serial option in the Define Database dialog. That check box
> offers the options of creating the serial on record creation or record
> commit.

OK,

So I have come up with a solution that will have to work. Sorry Harry
- I should have told you that I was using 8.5. I have simply added a
button to the portal that commits a record. I have to click on the
button to make the numbering system work, but it does work.

Thanks again for all your help.