Serial increment in child records

#11

On Jul 3, 4:56 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:

Quote:

In article <1183479917.342265.261... (AT) k79g2000hse (DOT) googlegroups.com>, RLA

reanimationlibr... (AT) gmail (DOT) com> wrote:
On Jun 30, 10:33 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com
wrote:
In article <1183240895.584045.63... (AT) k79g2000hse (DOT) googlegroups.com>, RLA

reanimationlibr... (AT) gmail (DOT) 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

#12

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

Quote:

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)

#13

On Jul 5, 8:35 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:

Quote:

In article <1183665309.510124.108... (AT) n2g2000hse (DOT) googlegroups.com>, RLA

reanimationlibr... (AT) gmail (DOT) 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

#14

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

Quote:

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.

(

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)

#15

On Jul 9, 1:39 pm, RLA <reanimationlibr... (AT) gmail (DOT) com> wrote:

Quote:

On Jul 5, 8:35 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com
wrote:

In article <1183665309.510124.108... (AT) n2g2000hse (DOT) googlegroups.com>, RLA

reanimationlibr... (AT) gmail (DOT) 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.

#16

On Jul 10, 2:53 am, Grip <g... (AT) cybermesa (DOT) com> wrote:

Quote:

On Jul 9, 1:39 pm, RLA <reanimationlibr... (AT) gmail (DOT) com> wrote:

On Jul 5, 8:35 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com
wrote:

In article <1183665309.510124.108... (AT) n2g2000hse (DOT) googlegroups.com>, RLA

reanimationlibr... (AT) gmail (DOT) 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Serial increment in child records

comp.databases.filemaker comp.databases.filemaker

Re: Serial increment in child records - 07-05-2007 , 02:55 PM

Re: Serial increment in child records - 07-05-2007 , 07:35 PM

Re: Serial increment in child records - 07-09-2007 , 02:39 PM

Re: Serial increment in child records - 07-10-2007 , 01:43 AM

Re: Serial increment in child records - 07-10-2007 , 01:53 AM

Re: Serial increment in child records - 07-10-2007 , 01:07 PM