I have used alpha 4 over the years to run a small database for my optometry practice. I had a patient information screen that linked to a child database for my ledger transactions. I recently switched to alpha5 v4 and started to lose my links between my parent and child database. My patients would show up for a receipt and I had no record of them on the ledger. After some time I assumed that I had a poor link design. I tried and tested some different link designs but kept losing ledger records. I knew the records were there because I checked the child database.
My current link design utilizes the "patient ID" field which is the result of a calculated formula that takes the first 3 letters of the patients last name with the first 2 of the first and then their date of birth. I did this so if I lose my link I will still be able to hunt down the transaction. I have also set referential integrity to cascade changes.
My problem now is that when I go to my master database of the set and enter any new information in a previous patient's records, the ID field is changed from the old calculated formula of RECNO() to my new formula, but this does not cascade to the ledger record. If I pull up that patients record again, the previous ledger record entries for that patient are no longer linked to that patient.
My assumption was that when the new patient ID in the parent database was calculated, that this would cascade to the child database, and my link would be maintained. What is going on?, why is this not happening, and what can I do to fix it. My link is one to many, with cascading. My linking fields are both character, and of the same length
I tried to update the ID field on all of my records to maintain consistency, assuming that the new ID value would cascade to the child database. That only worked on some of the records.
If I cannot solve this problem with the links I will have to consider a lookup database for transactions. I believe that this however does not take full advantage of the power of this database.
I would also appreciate some, (other that those in the book) more aggressive and detailed strategies to maintain links in general. There has to be a better way to insure the integrity of my database links.
My current link design utilizes the "patient ID" field which is the result of a calculated formula that takes the first 3 letters of the patients last name with the first 2 of the first and then their date of birth. I did this so if I lose my link I will still be able to hunt down the transaction. I have also set referential integrity to cascade changes.
My problem now is that when I go to my master database of the set and enter any new information in a previous patient's records, the ID field is changed from the old calculated formula of RECNO() to my new formula, but this does not cascade to the ledger record. If I pull up that patients record again, the previous ledger record entries for that patient are no longer linked to that patient.
My assumption was that when the new patient ID in the parent database was calculated, that this would cascade to the child database, and my link would be maintained. What is going on?, why is this not happening, and what can I do to fix it. My link is one to many, with cascading. My linking fields are both character, and of the same length
I tried to update the ID field on all of my records to maintain consistency, assuming that the new ID value would cascade to the child database. That only worked on some of the records.
If I cannot solve this problem with the links I will have to consider a lookup database for transactions. I believe that this however does not take full advantage of the power of this database.
I would also appreciate some, (other that those in the book) more aggressive and detailed strategies to maintain links in general. There has to be a better way to insure the integrity of my database links.
Comment