This is confusing.
You need to start by describing each table involved in this issue and how/if they are related to each other.
You may find it easiest to do a screen capture of manage | Database | Relationships and then use the controls below "Post A Answer" to upload the screen shot. If you do, please briefly describe the purpose of each table occurrence in the screen shot that is part of this issue.
sorry for the delay, but thank you for the response. Ill try to clarify. My client is a photographer and he sells each print he creates in editions of 50. So he creates his print (in the prints table) and defines its name, size, suggested price and the number of times that print can be sold (usually 50 - each one is a sequence). Then, when he gets an order for that print, he creates a record in the prints_sequence table logs the print name, size and sequence number of that particular print (1 of 50 and so on). I cant figure out how to track the sequence number (of the 50) of each print.
A side note: It looks like your invoice_details 1 and print_sequence 1 tables could be merged into a single table. It looks that way since I assume that you can only list a given print once on a single invoice. This is not a change you have to make however.
Define a calculation field, cMaxSeq, in Prints set to evaluate from the context of Prints 1.
Define it as: Max ( print_Sequence 1::sequence_nbr )
Put a button on your Prints 1 layout. Set it to run this script:
Set Variable [$PrintID ; prints 1::print_id]
Go to Layout [print_Sequence 1]
Set Field [print_Sequence 1::print_id ; $PrintID]
Set Field [print_Sequence 1::sequence_nbr ; prints 1::cMaxSeq + 1]
Go to Layout [original layout]
If you put a portal to print sequence 1 on your Prints 1 layout, clicking this button will cause a new record to automatically appear in the portal.
Please Note that this solution assumes that only one user will ever be creating new sequence records for a given print at a time. If two users tried this at the same time, you might get two sequence numbers for the same print that are the same. There are ways to add a validation rule that catches such instances should that rare but not impossible event occur.
thank you philmod.