Incrementing a Sub ID field

[bdam]

So my Primary Key is made up of a project_id and a project_sub_id. In my case I intend to use this to track books using the project_id and various editions, translations, ect. using a project_sub_id. To rephrase, there will be multiple records for the same project_id but each project_id can have muliple sub_ids which are simply incrementing numbers.

Now, getting the project_id to increment is no problem. However, how am I going to get the sub_id to increment properly?

Mentally I'm thinking of relating the table to itself using the project_id, finding the current max sub_id and simply incrementing it in the new record by one. My only problem is that I don't know where to start. How do I run a script that finds the max number for all related records? Maybee instead of using the related records should I just do a find for the current project_id and iterate through each to find the max. How do you do a find in a script for the current project_id?

Thanks for any help,

Bryan

[T-Square]

It sounds like you are trying to combine two different entities (projects and sub-projects) into one object (table). Use the relational capabilities of FM to track each entity clearly, efficiently, and unambiguously. Set up a table for Projects, and one for Sub-Projects (Editions?). Put a ProjectID field in the Editions table, and join the two tables via a relationship that is sorted by TaskID. To show a Project-Task identifier on screen as you describe, create a calculated field that makes use of the sorted relationship to generate the appropriate code. In other words, you should be able to have two calculated fields something like this:

Editions::Position:

Get(RecordNumber)

Projects::CodedDisplay:

Projects::ProjectID & "-" & Right("0000" & Editions::Position; 4)

HTH,

David

[bdam]

Thanks T-Square.

You are right it would initially make sense to have two tables here. However there's no data per-se that we would record at the Project level. In other words, there's nothing that is guaranteed to be common amongst the sub-projects. The only reason we have it is so we can essentially group Sub-Projects for various reasons such as sales data. So the Project ID is entirely arbitrary, it's not based on any particular data (author, ISBN, title).

However you touch on a way I can script this. Rather than use a sorted relationship I can have a script that does a find on the current project id and then sorts descending on sub-project id and simply increments that.

Thanks,

Bryan