Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Changing a relationship?


awarnock
 Share

Recommended Posts

OK, I screwed up. I have two tables related through a numeric ID field that I stuck in the primary table, not realizing that the function Get(RecordID) did the same thing. As a result, I have two fields in the primary table that are effectively reduntant (RecID and Name, both unique), and a relationship between the two tables based on RecID. I want to drop RecID.

 

This relationship is a little awkward for data entry because I want to use a Drop-down list for entering data. The value list I'm using has the (now unnecessary) RecID field as the primary field and displays the Name. But Drop-down lists display the primary field - not what I want since I no longer need the old RecID field.

 

However, if I just drop the RecID field from the primary table, it breaks the relationship between the two tables and I have to do the data entry in the secondary table over again - not at all a pleasant prospect to break to my client. What I want to do is fix the relationship between the two tables so that it depends on the Name field of the primary table, set up a value list with Name as the primary. Then I can use a Drop-down list for the data entry and display the value of the Name field for the user.

 

I've tried creating a second relationship based on the Name field of the primary table and then deleting the old relationship based on the RecID field of the primary table - the secondary table loses track of the Name field values. Any ideas of how to modify the relationship between the tables while avoiding the necessity of re-doing the data entry?

 

Oh, and the reason I don't want to just use a Pop-up menu for the data entry is that, although it only displays the secondary field value from the value list, it doesn't scroll the same way as the Drop-down list. It scrolls more slowly, and you always have to start at the top - this is quite awkward as the list gets long. My client prefers the way the Drop-down list behaves, and I agree.

Link to comment
Share on other sites

You really shouldn't use Get(RecordID) as the basis for relationships anyway: it's not like an auto-enter serial number, so if you ever had to import records from one copy of your db to another, the values for Get(RecordID) will not come over from the table being imported, and you're hosed.

 

(It works OK as an auto-enter option for a unique-value field, though).

 

 

Insofar as you did what you did and now want to change it:

 

 

Assuming for the sake of easy example that you have only ONE related child-table that's joined by your Get(RecordID) field — let's call that field RecID, shall we? —*decide on a new (unique, permanent) value to use for joining your tables and then run a script such as this:

 

Show All Records

Go to Record [First]

Loop

Set Variable [$NewKey, LocalTable::UniqueIDField]

Go to Related Record [OtherTable, using layout OthTbl, show only related, current rec only]

Loop

Set Field [OtherTable::RecIDMatchField, $NewKey]

Go to Record [Next, Exit After Last]

End Loop

Go to Layout [original layout]

Go to Record [Next, Exit After Last]

End Loop

 

Then when you're done, change your rel definition from RecID::RecIDMatchField to UniqueIDField::RecIDMatchField

 

 

That actually may not directly address your value list situation. I'm unclear about what you're doing with your value list and in what way you wish it to behave different / look different, etc.

Link to comment
Share on other sites

Great - I think that'll do what I want. I'll have to experiement with it a bit, but I appreciate the quick feedback.

 

> You really shouldn't use Get(RecordID) as the basis for relationships anyway:

> it's not like an auto-enter serial number, so if you ever had to import records

> from one copy of your db to another, the values for Get(RecordID) will not

> come over from the table being imported, and you're hosed.

 

Yeah - got that part. I don't really want to use Get(RecordID) as the basis, but it duplicates - logically, anyhow - the unique values in both RecID and Name, I figure I don't need both. If I need an ID field (which I'm using in defining a portal), then it suffices for that. The behavior of the data entry menus would be better if I just use the Name field. And, in turn, if I do that, then the old RecID field isn't used for anything once I can modify the relationship to use Name instead of the RecID field.

 

> That actually may not directly address your value list situation. I'm unclear

> about what you're doing with your value list and in what way you wish it to

> behave different / look different, etc.

 

Sorry - the value list stuff was more a way of explaining why I've decided to eliminate the RecID field. The only thing I'm using it for was to get the Name field to display in the data entry Pull-down menu. It's not really relevant to the relationship issue.

 

Thanks again for the help. I'll let you now if the script does the trick.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use