Script for sequential numbering

[eldoc]

Hope you can help me out on this one. The script below generates a new record in a child table (i.e, Consultas) and automatically inserts a matching ID field, allowing the user to cancel the request, if necessary. So far, this works fine.

However, I need that serial numbers of records in the child table are sequential for any given record in the mother table.

The script is working fine in sequencing the serials but for ALL the records in the child table. I need to find only the related records with the mother ID record. The script is not able to find the defined set of records to perform the FoundCount function.

I am really stuck, I would appreciate your help.

Set Error Capture [ Off ]

Beep

Show Custom Dialog [ Title: "Aviso"; Message: "Nueva consulta para este paciente?"; Buttons: “OK”, “Cancel” ]

If [ Get ( LastMessageChoice ) = 2 ]

Halt Script

End If

Go to Layout [ “Consulta Inicial” (Pacientes) ]

Set Variable [ $PacienteID; Value:Pacientes::PacientesID ]

Go to Layout [ “Consultas” (Consultas) ]

New Record/Request

Set Field [ Consultas::PacientesIDfk; $PacienteID ]

Commit Records/Requests

[ Skip data entry validation; No dialog ]

Go to Related Record [ From table: “Consultas”; Using layout: “Consultas” (Consultas) ]

[ Show only related records ]

Set Variable [ $serialconsulta; Value:If ( IsEmpty (Get ( FoundCount )); 1; Get ( FoundCount)) ]

Set Field [ Consultas::SerialConsulta; $serialconsulta ]

Enter Find Mode [ ]

Set Field [ Consultas::PacientesIDfk; $PacienteID ]

Perform Find [ ]

Sort Records [ ][ No dialog ]

Go to Record/Request/Page [ Last ]

[LaRetta]

This type of serial numbering scheme will break in multi-user mode and is not recommended. Your child IDs should remain sequentially assigned (and don't worry if you end up with gaps in the serial because of deleting a child record.)

To know the 'number' of each child record in relation to its parent, define your basic relationship (Parent::ParentID = Child::ParentID) and place a child portal on your parent layout. Then type @@ inside the portal. This will sequentially 'number' the child records. It is possible to use a serial table for numbering but it is not entry-level stuff and is rarely needed in any case.

:smile2:

[eldoc]

Thanks LaRetta for your feedback. I agree with you. However, I am not using the calculated serial number as the primary key in the table. It is additional to that one. The portal approach is a good one, but I need the value within the child table for further calculations. That´s the problem.

[LaRetta]

but I need the value within the child table for further calculations.

This still concerns me a great deal - you should not need this. Nonetheless, you've requested and here it is.

IT IS NOT SAFE IN MULTI-USER AND YOU CAN GET DUPLICATE CHILD RECORDS ... if two Users create the record at the same time, one might not yet commit and you'll get dup special child serials. You could also use a special serial table to track these special serials.

If that calculation you mention is important at ALL, please consider other methods (and this special serial does NOT replace the primary ID which binds relationships together). We'd be happy to discuss alternatives with you.

Usually I refuse to assist in potential suicide ... :king:

Patients.zip

[eldoc]

Thanks much LaRetta! You are right in pointing out the involved risks. I see the "messing potential" of the algorythm. The intended program is for a single-user, and the calculation involved is just a flag.

I keep separated and exclusive ID record fields for the mother and child tables.

I figured the solution out from the script you provided. Thanks again!