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?

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?

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.

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?

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.

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

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.

Then use the first part of my post.

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

Mike

There's really not much I could add.

NthChildDiscount.fp7.zip

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

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.