illumejames

How to Correct a MANY to MANY Relationship?

Discussion created by illumejames on May 4, 2013
Latest reply on May 11, 2013 by illumejames

Hello all,

 

Here is my problem:

 

I inadvertently connected two tables together via their FK's.

 

[Pet Table] Pet PK

[Pet Table] Pet Owner FK ------> [Service Given Table] Pet FK

 

The Pet Table host a portal whose records are stored in [Service Given Table].

The portal is working fine expect in the cases where the Pet Owner has multipule

pets; in that case any "service" entered for one pet is added to every pet because

of the Many to Many relationship.

 

I understand relationship must be, and should have been One to Many.

 

[Pet Table] Pet PK ------> [Service Given Table] Pet FK

[Pet Table] Pet Owner FK

 

When I change the relationship the path is broken and the portal records

no longer show in the [Pet Table]. This is because the data in the [Service Given Table] still shows

the Pet Owner FK data. I have manually changed the Pet FK [=Owners Name] data to Pet PK and

the portal relationship is restored.

 

What I need to know is how to go about doing this via a script which changes all

1,500 records. Note: In the [Service Given Table] exsist both the Pet Owner Name

and the Pet's Name … using a combination of the two, I have successfully been able to use PERFORM FIND

in the [Pet Table] to find unique records and therefore copy the PET PK and place it in [Service Given Table] Pet FK.

What I have not been able to do is write a script which does this properly. Everything I've tried fails when it reaches

an owner with multipule pets.

 

Example:

 

BEFORE SCRIPT

 

[Service Given Table]

Ms. Moss | Dog | Poly

Ms. Pruitt | Dog | Ipa

Ms. Evan | Dog | King

Ms. Evan | Dog | Tracy

Ms. Evan | Dog | Bruno

Ms. Evan | Dog | Sage

 

AFTER SCRIPT

 

[Service Given Table]

45 | Dog | Poly

46 | Dog | Ipa

47 | Dog | King

47 | Dog | Tracy

47 | Dog | Bruno

47 | Dog | Sage

 

Note: All pets in [Pet Table] have unique IDs.

 

Any help would be appricated! [On a tight deadline]

Outcomes