Creating new child serial ID.
Apologies if I am asking something that has been asked already in the past. I've been looking already for few hours in different forums as well and I don't find something that is "close enough" to what I am looking.
I want to create a child serial number ID that is connected to a parent ID from a parent table. My problem is that when I set the child ID as serial number it affects all child IDs and not only the one I want. Is there a way I can write a script to fix this?
Parent table is "Patients". In this table I have clients number ID field "PatientNo". Its child table is "PatientsVisitDetails" and the serial number id I want to use is "NumberOfVisits".
Right now I have set "ClientNo" as serial number on creation and I have no problem with this. The problem is with "NumberOfVisits". Let's say I register patient "A" and this patient visits 3 times. Its patient number will be 1 and then it will assign details for each visit which are 3 in total. If I register a new patient "B" it will asign 2 for PatientNo, but when I add a new record FM will set number "4" instead of 1 for NumberOfVisits to this patient. I want the child serial number to be independent from others.
Apologies if I am not providing enough information.
Any ideas of how I can fix this?
Thank you in advance