Counting related records

January 13, 200421 yr

I need to create an indexable field (lets call it childNumber) in a child file that returns a number indicating the child's position among its siblings (Is this the 1st related record, the 4th related record, etc).

Example:

Say I have carMake.fp5 and carModel.fp5 databases. I create a carMake record, Toyota. Then I create related carModel records, in the following order: Camry, 4Runner, Celica. In this case, Camry should have a childNumber of 1, 4Runner is 2, and Celica is 3.

I've tried creating a number field with an auto-enter calc, count( self_by_parentID::constant_field ) + 1. HOWEVER, that only returns the correct number starting with the second related record. The first related record gets an empty value.

I can't use a script to set the number -- as I create new records in a portal that allows creation of new records, the new related records must automatically enter their number. And, I can't just @@ in the layout because I actually need to search against this field (Hence, it must be indexed to avoid major performance problems).

Please, does anybody have any ideas?

January 13, 200421 yr

Mariano,

As FileMaker treats one relationship at a time, your SelfJoin is not automatically called at the moment of creation.

I'm not sure either a lookup wouldn't fail though which then would mean that SelfJoins are targetted after External Relationships.

This is my understanding of FileMaker's indexing process, but then I'm not a computer scientist...

In this situation, wether than relying on a SelfJoin on the Parent_ID, you'd better work with the other side of the relationship, that is Validated and active.

By creating a calculated field in the Parent file

c_Count = Count(ChildByParent_ID::Child_ID)

You'd be able to have your number field auto-entered with a formula along the lines of :

ParentByParent_ID::c_Count

One important step though.

While the Parent_ID will be auto-filled through your portal, this calculation (as any other lookup that would involve a relationship back to the Parent file) will be successfully entered if you had exited the Parent record right after its creation.

So you surely would need a script in some place to make it work...but you don't need it in the portal, and you moreover don't need to play around with a "+1" that could happen to be buggy in some instances.

Regarding this newly created index field, if you allow deletion of portal row as well as creation, then you'd better attach to the button a sub-script that loops through the related set and re-set all 'number fields' through a Set Field with formula along the lines of :

WordCount(Left(ValueListItems(Status(CurrentFileName), "ChildIDsList"), Position(ValueListItems(Status(CurrentFileName), "ChildList"), Child_ID, 0, 1)))

where the VLI obviously is this time related to your SelfJoinOnParent_ID, and gives a list of those siblings, in the order of creation.

HTH.

(I'm quite sure you'd need some sleep Mariano, as I feel like you currently are aware of all statements I've made above)

January 13, 200421 yr

Hi Mariano,

There's a demo at:

http://www.nightwing.com.au/FileMaker/demos1.html#d7

- which might help you with the above problem.

The only caution is that if the solution is multi-user, then there's a risk that near-simultaneous creation of records on different workstations will calculate the same sequence number.

I'll leave you to decide whether this is a problem in your case - and if so, whether there are ways to correct for it or work around it that would be acceptable in your current soloution...

January 13, 200421 yr

Hi Ray,

I used to implement the approach you outline with the Record_ID+1 and lookup, but in some other thread, you pointed that :

"The 'RecordID + 1' approach may appear to work over small numbers of records, but it will break if you use it more widely, because Status(CurrentRecordID) numbering is only contiguous within defined ranges.

and offered another of your genius calculations which perfectly worked for me afterwards.

Link to the thread

The article from FMI TechInfo you mentionned isn't online anymore. Did you by chance kept a copy of it saved in some place ?

This said, would the other suggestion of an auto-entered calculation of the related 'c_count' value act differently in a multi-user scenario ?