Hello,

I have a question about persistent relationships. I've developed a complex user interface for a medical database system. The interface is a tabbed form with many grids, and the database has many complex levels of relationships.

For simplicity sake, I will describe one particular section of the database application. On one particular form, I have a grid which displays the various medical procedures associated with the currrent patient record being displayed. Thus, when you move to the next patient record, the medical procedures performed on that patient are automatically displayed in the grid. I setup a persistent relationship between the patient table and procedure table. No big deal there.

I wanted to make sure that the procedures are displayed in ascending order of date. I created a persistent relationship between the patient table index and the procedure table index. The index on the patient table is based on the social_security number only. The social security number is of the character data type. The index expression for the patient table is: "Social_Security". The index on the procedure table is based on the Social_Security number and procedure date. This is the index expression for the procedure table: "Social_Security+DTOS(ProcedureDate)".

Although the two indexes are not identical, I made a persistent relationship between the two indexes. Everything seems to work fine. Whenever I move to the next patient record, the correct procedure records appear in the grid for that particular patient, and in the correct order (ascending by date).

My question is this: Could any problems arise from creating a persistent relationship between two indexes which are not identical?? I realize that VFP's automatic referential integrity will not work in this situation, but that really isn't a problem. I will programmatically handle the referential integrity issues. There may have been a better way to do this, but this was the easiest method for me, considering the complexity of the data and user needs. This is a very complex application, with many sources of data being displayed at once.

The thing that is bothering me is that I have created a relationship between indexes which are not identical. Is there any possibility that this could cause the records to not appear, or the wrong records to appear in any particular circumstance?? Apparently, VFP is matching the patient table index to only the social_security portion of the procedure table's index expression. The grid seems to be using the entire index to sort the records correctly. It appears to be working correctly, but I'm worried that I did something wrong that may come back to haunt me.

Any comments on this would be greatly appreciated.

Dave...:)