numbering children

[mleiser]

I have a parent and child database - 2 separate databases because it's a conversion from fm6. In the parent database I have a screen with parent info and a portal where children can be entered, like name, grade, age, etc. One of the things I need to do is charge tuition. For teh second child of the same family there's a specific discount, for the third a double discount, etc. Right now I have to enter child number in each child, 1 for the first one, 2 for the second, etc. Then my calculation can take over to determine tuition for each child with the discount, if applicable. Now here's my question. Is there some trick I can use to have the first child come up with a 1, the second with a 2, etc. automatically. It gets even trickier if I delete and/or add a child. Any ideas?

[isaaccs]

just a thought:

create an instance of your contact table such that it only shows the children of a given parent record. this would self-joined table based on the relationship of, say, the parent record id. any set in this table should ONLY show children of the same parent.

when you create a new child record within this instance, define a calculation for the "child #" that returns a value based on the current record count (function) — if a parent currently has three children/records, if you create a forth, it should put "4" into the child's number field. am i making sense?

[sbg2]

Can you not simply have a Discount field and leave the tuition for each child at the normal price?

Parent::Discount = Sum (ChildDB::tuition) * DiscountPercent

Parent::DiscountPercent = Case(Count(Childrecords) = 1; 0; Count(Childrecords) = 2; .05; Count(Childrecords) = 3; .10; 0)

Parent::TuitionFinal = Sum (ChildDB::tuition) - Discount.

[sbg2]

when you create a new child record within this instance, define a calculation for the "child #" that returns a value based on the current record count (function) — if a parent currently has three children/records, if you create a forth, it should put "4" into the child's number field. am i making sense?

If this is an autoenter calculation what happens when a family with 3 children has one of the children graduate? Child 2 should now be 1 and child 3 should go to 2.

If an unstored calc wouldn't this return the same number for each child?

[comment]

The relationship can be:

Children::ParentID = PreviousChildren::ParentID

AND

Children::ChildID > PreviousChildren::ChildID

Then Count ( PreviousChildren::ChildID ) + 1 returns the serial number of the child in family.

But I like your idea of calculating the discount directly in Parent much better.

[mleiser]

First, how do you put the number into the record just upon creation? Also, how would you modify it if one child is deleted?

[mleiser]

This works if you want just a total discount. I need, for each child record, to display tuition and discount. Of course, there's also a total discount and tuition, but I need the individual ones.

[comment]

Then use the first part of my post.

[mleiser]

Could you elaborate? A little more detail would be useful, if you don't mind. This version 7 is REALLY different than 6.

Mike

[comment]

There's really not much I could add.

NthChildDiscount.fp7.zip

[mleiser]

Thanks for sending the file. Can you explain what the child to previous child of childid>childid does? I understand the parent id shows all children for a particular parent. It seems to me that each new child gets a unique id. So where does the second criteria come in? Maybe I'm just having a bad day. (Actually I am). Thanks for all the help.

Mike

[comment]

Each new child gets a unique ChildID. And each new child's ChildID is higher than any other, existing, ChildIDs. Therefore the relationship:

Child::ParentID = PreviousSiblings::ParentID

AND

Child::ChildID > PreviousSiblings::ChildID

is a relationship from a child to all its older siblings. Count your older siblings and you will know your place in the order of the siblings.