Table Relationship problem

[cdr-dev]

I have a problem with passing data between 2 tables. The database is a single file with no external relationships between any other files.

What I have is a Screen to enter patient information and one of the fields is a Medical Record number (called MR Number PTINFO)and it is define in its own table. I have another screen called "insurance information" and it has its own table with a field called "MR Number INS" (To distinguish the two differently). When I add a new patient or retrieve a patient from the patient information screen, I need it to refect the data related in the insurance table as well.

I have created a relationship between the two tables in the respective MR number fields and set them = to each other and selected the check boxes for the insurance table to add records and delete records respectively when worked with in the Patient information table.

When I create a new record with all the criterial in the patient info screen, then go to the insurance screen, there is no data in the MR number field when you click in the field, it says "no records are present". Why is that?

Thanks!

Chris

[transpower]

The fields in the insurance information table can be calculated values dependent on the MR Number INS value--but you must first put that value in the record for all the other fields to update. You could post that data by script or enter it manually.

[-Queue-]

When I create a new record with all the criterial in the patient info screen, then go to the insurance screen, there is no data in the MR number field when you click in the field, it says "no records are present". Why is that?

What you do in one layout doesn't affect the found set in another. If you want to go to a related record in another layout, you'll have to specify that. Go to Related Records [show only related; relationship]. Otherwise, your found set is independent of other layouts and processes that affect them.

[MoonShadow]

Chris said...When I create a new record with all the criterial in the patient info screen, then go to the insurance screen, there is no data in the MR number field when you click in the field, it says "no records are present". Why is that?

Chris, you need to insert data into any other field than the MR number field itself. If you place the Insurer's Name directly on the Patient's layout and type into it, a new Insurer record for that Patient will be automatically created in response, and FM will insert the corresponding MR number for you. Just don't try to type into the ID that relates them.

Any time you cross-place related fields (and if Allow Creation is on) and type into any field but the key, a new related record will be created. It is very powerful and, in my opinion, underused.

[cdr-dev]

The MRNumber field is the key field but it is a field that will be entered into.

If a user creates a new record, he/she will first off enter the Med Rec number. That will be the first piece of data entered. So not entering data in the MR field first wouldn't be an option.

But here is a thought I had that might be an option. What do you guys think about creating a "autonumber" field that creates a new number each time a record is created and link the relationships together via the autonumber? It would be easier to tie all the fields together that way as opposed to classifing a important field like MR number, a primary key. Would that work?