Need to change Primary Keys

[LaRetta]

Well, I've gone and done it. I have a database in trouble! Its data was imported from an old AccPac program. Its primary key (ProcedureCode) needs to be changed! It should be easy to correct; however, it is joined to another db Service:ProcedureCode which currently contains 1500+ records and there's another catch!

db name: ProcedureCodes

Record 1: Code: BA023, Description: Family Therapy Rate $85.00

Record 2: Code: 90855, Description: Family Therapy Rate $85.00

---the first is a Medicaid or State billing code and the second is it's equivalent insurance billing code -- but they BOTH are the same procedural description and price, so both should exist within the same record in different fields. The reasoning (I believe) is so that, during the invoice process both codes are available for selection -- so when billing Family Therapy, use BA023 if Medicaid, or use 90855 if private insurance.

To complicate matters (or hopefully to simplify them), I want to create a new 'unique primary' key to join the two dbs, yes, like duh, I thought the ProcedureCode was unique enough, and yes I've learned my lesson on this

Question is: How should I do this? I assume that I should create the unique primary key in ProcedureCodes db first. Okay, I'm comfortable with that, using Replace - it's a stand-alone system. Wait, but first I have to 'merge' by description before I create the new primary keys or I'll waste ID numbers! Then I'll have to create the same ID in Service:ProcedureID -- if I leave the existing join in place, it would be easy in service to replace Service:ProcedureID Procedure:ProcedureID; oh, I'm lost! Help please! How would ya'all do this

[CobaltSky]

It goes without saying that you should back up both files before you start 'just in case'. The, to create a new key field between the two files, whilst preserving the existing relationships, you will need to:

1. Create a new text field in your related database, and populate it with the unique values that you wish to use as the new key between the files (eg using the Replace Contents command).

2. Create a new text field in your main file, and run the Replace Contents command on it, using the 'Replace with calculated result' option, and specifying the formula as:

RelatedFile::NewKeyField (where the relationship refernced is the existing one between the two files, and the field referenced is the one referred to in 1 above)

3. Return to the main file, go into relationship definitions and open the 'Edit...' dialog for the relationship to your related (ProcedureCode) file, and select the new fields in both files as the key fields for the relationship.

That's it. Now your files should be relating based on the new fields. Before doing anything else, check over all the records and make sure that the relationship is working correctly for all cases.

Once you are satisfied that all is operating as it should, to avoid confusion, it will be wise to delete the former key field from the related file.

As regards structure, I suggest that you store only the new key field (ProcedureID) in the main file, and reference the procedure codes wherever they are required via a calculation, which selects the appropriate code from the ProcedureCodes file according to the relevant criteria which you place in a Case( ) function.

[LaRetta]

Thanks Ray! Do you have any suggestions on 'combining' the two types of ProcedureCodes with same description/rate in the ProcedureCodes db? And should that be done before or after the above? One last question, in Service, one entry will show BA023 and another will show 90855 -- and, in ProcedureCodes those will now be different fields within the same record?

[LaRetta]

Okay, Ray, sorry, skip my 'last question,' I understand using a calc there. I find it confusing when referring to Main File and Related File. Is the Main file always the one on the left in define relationships? I find it confusing because it depends upon where I'm 'standing' in the moment, doesn't it? Sorry for the dumb question ... in the above situation, which IS my Main file? If I went to Service and joined to ProcedureCode, service would be my main! A join only needs to be 'done' from one side, right? Does it really matter which side? Or do I always have to join 'both' directions? In other words, my ProcedureCode db is small, almost just a lookup db. If I join it to Service, Procedure becomes my Main? But when I go to Service, Service is my main?

[CobaltSky]

Yes. The way I see it, the 'equivalent' procedure codes should sit in separate fields of the same record in the ProcedureCodes db, along with a single description field and rate field - and of course, with your new ProcedureID field.

You are right that relationships are described in terms which are context dependent. So Terms such as, 'parent' and 'main file' refer to the db on the left side of a relationship, while the counterpart terms 'child', 'related file' and sometimes 'target' refer to the db on the right side of a particular relationship. A db which is the parent in one relationship may the child in another (just like real life )

To confuse the issue even further, the terms 'primary key' and 'foreign key' are occasionally used with overlapping meaning, but to be strictly correct, the primary key is the defining key for the records in a db, and the foreign keys are the keys stored which activate links to other databases. Thus your ProcedureID field will be the primary key when it is in the ProcedureCodes db, but will be a foreign key where it is stored in the Service db.

A join only needs to be 'done' from one side, right? Does it really matter which side?

Data flows in only one direction in any one relationship - alwyas from the child to the parent - ie from the db on the right side of the relationship to the db on the left. So it matters which 'side' you place the relationship on, because the data will flow to that side (and not in the other direction).

Or do I always have to join 'both' directions? In other words, my ProcedureCode db is small, almost just a lookup db. If I join it to Service, Procedure becomes my Main? But when I go to Service, Service is my main?

You only need to join in both directions if you require data to flow in both directions. So, if you want data from the service db to be 'visible' from the perspective of the ProcedureCodes db, then you'll need a relationship which has the ProcedureCodes db on the left and the Service db on the right, as well as one with Service on the left and ProcedureCodes on the right.

Situations where you might want to do this would include where you wanted to use the ProcedureCodes db to generate summary data on services by procedure - but this would be only one of several possible ways to achieve that.

[LaRetta]

Thank you for the clarification. I've always been uncertain and so I've always joined both directions 'just in case.' Hmm, maybe I should go back and fix those, huh? I don't like things to be untidy or illogical and ... might it draw resources, slow down, or confound my structures? What I CERTAINLY don't need is any more confused relationships