Two tables, Table_A (parent) and Table_B (child). Table_B records display in a portal of a Table_A layout. Match field is A_ID. Table_B should have field B_ID such that A_ID & B_ID together are a unique key for Table_B. I want to automatically generate B_ID, and I want it to start over at 1 for each parent record, and increment for each added child record, so that, for record 1 in Table_A, the related Table_B records have B_ID of 1,2,3..., and for record 2 in Table_A, the related Table_B records have B_ID of 1,2,3... etc.

The problem: A portal row doesn't commit until the parent record commits, so you'd have to type in a new portal row, then click outside the portal to commit everything, then go back and add a new portal row, then commit again. If I do all this, logic works such as "Independently Incrementing Serial Numbers" at http://www.nightwing.com.au/FileMaker/demos1.html. This kind of logic depends on evaluations which don't work right until each child row is committed. If you add a bunch of child records without clicking outside of the portal, they aren't committed and the logic doesn't generate a new serial number for each new record - it generates the same serial number over & over.

The question: Is there any way to do this other than scripting a "New Child Record" button and a "Commit" button?

Or is this whole approach stupid & there's a better and easier way to do it?

From FM8 Help: When you change related data (such as related records displayed in a portal), these changes are not committed until you commit the record that is displaying the related data.

If what you wish to achieve is where @@ doesn't give any indication if a portaalrow have been deleted?

Could this be of some inspiration:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000128

I've tried to show it in the attached template!

--sd

snabelA.fp7.zip

a better and easier way to do it?

The best way is not to do it. Your primary key in any table should be completely meaningless and independent of any attribute a record might have (and certainly independent of attributes that depend on other records!).

And, as you have discovered, it's difficult to come up with a reliable scheme for such numbering - see also:

http://fmforums.com/forum/showpost.php?post/172165/

Why do you need this anyway? Records can be numbered dynamically in any context.

Thanks for your prompt, thoughtful, helpful and complete reply and reference.

I think you're suggesting that I simply use an auto-enter serial number for the child records, even though the numbering would go from one to infinity with no reference to the parent record that each child record relates to.

The only point to this serial number field would be to have a unique primary key for the relation. I wouldn't ever use it (that I can see). Is DB integrity a sufficient reason to have this field?

My example has families (relation A) and family members (relation :. This would apply also to something like an invoice with line items. Are you saying that the thing to do is to have the invoices use one series of serial numbers, and the line items use another series of serial numbers, and have the two series be independent? Or should I just forget a serial number field for the child records?

Edited July 8, 200817 yr by Guest

Are you saying that the thing to do is to have the invoices use one series of serial numbers, and the line items use another series of serial numbers, and have the two series be independent?

Yes.

should I just forget a serial number field for the child records?

It's useful to have a serial number in every table - even if it's not being used as a primary key in a relationship. You never know when a future feature, such as a filtering self-join, might require a unique ID.

Thanks so much. You da man.