serial increment a child record

[Riley Waugh]

Could it be Alzheimer's?

I want to do the following, and it should be easy but I'm lost. I have a relational database.

Parent record is a client with a unique Client_ID of, say, "ABC-123".

Case managers make case notes about the client. Case notes need to have serial numbers based upon the related parent file Client_ID.

For example, the case notes for client ABC-123 should be numbered 123-001, 123-002, 123-003, etc.

This should be like a FileMaker 101 kind of problem, so send me back to kindergarten. Help.

[LaRetta]

Serial numbers should never have meaning. Your child table should have auto-enter, FM-generated, meaningless, incrementing serial starting with 1. And so should all of your tables. The way that the two tables will be related is that you then place your ParentID in your Child table.

Any time you add meaning to a serial, you open up repeated problems. And never worry about there being holes in the serial numbers (if you delete out a record). These serials don't even need to be shown. Their purpose is to identify each record within a table (for using in relationships) and also for updating the record, migrating, recovering etc.

[Riley Waugh]

I agree with your comment about serial numbers if used as primary or secondary keys or a unique identifiers. I am not using it in that way. Each record in every table has a unique identifier.

All case notes must be entered and kept in unchangable chronological order. They may be entered on a date after they occured, and there may be multiple entries on a date, so using a date is not productive in this case. Also, there may be no gaps inthe serials numbers... that could indicate that a case note had been deleted which is a major no-no.

They must be tied to the client (in this example client ABC-123) with out using any other idnetifier like a name as they must remain confidential to outside reviewers.

Each case note must be identifiable so that a supervising case manager can talk about a particular note in case rewiews.

Hense, it should be: 123 (as a tie to client ABC-123) and a running serial number such as 001 so the individual note can be taked about. therefore I would like 123-001, 123-002, etc.

I have done this sort of thing many times inthe past. Should be easy with variables. I just have lost my mind an can't figure out the formula for the field.

[Vaughan]

Each main patient record needs to have a "note counter" field to store the number of notes. I'll assume it starts of as 0.

The note creation process needs to open the patient record, increment the note counter, then create the note record with the incremented number. If the patient record is in use then the increment will fail, so the process needs to trap for this otherwise duplicate numbers will occur. (This is the main challenge with these systems.)

The process for doing this is important because it needs to trap for errors and be designed to handle the problem of the patient record being locked, and possibly the situation where a note record cannot be created (in which case the number has already been incremented). Rolling the number back might not be possible because somebody else may have added a note in the meantime.

A defensive process might be to create the note record first, then increment the counter and update the note with it.

The formula for the field is the easy part. :)

[LaRetta]

Here is one approach (attached). All you have to do is prohibit deletion of records (and that can be handled through privileges.

You could also use a serial table and script it. But using script could always open possibly of it breaking.

SerialCreator.zip

[Riley Waugh]

Thank you Vaughn and LaRetta.

The lack of a stored result in the NoteCode was a problem for me until I saw the double self join in Notes table.

Thank you all for your help.

[comment]

Note:

In LaRetta's demo, if a note is deleted or reassigned to another client, the subsequent notes will be automatically renumbered - so the codes will be always consecutive, but not permanent.

In Vaughan's approach, deleting/reassigning a record will leave a gap in the sequence. Here, the codes will be permanent, but not necessarily consecutive.

You cannot have both.

[Deepak Kumar]

Here is one approach (attached). All you have to do is prohibit deletion of records (and that can be handled through privileges.

You could also use a serial table and script it. But using script could always open possibly of it breaking.

Thanks for ur post..i got my solution what i wanted.