Modifying ID (index) of listitems

Question

So I ran into a situation where I have two lists containing the same information but in different languages (don't ask me why they chose to do it this way).

Each item links through a workflow-created field to the corresponding item in the other list.

Eg:

/Lists/List1/DispForm.aspx?ID=1

Has a link to:

/Lists/List2/DispForm.aspx?ID=1

So it links to the ID (index) of the item.

This means, that the guys responsible for the data has to make sure each item is in the same order. However, they already messed up, and deleted the messed up entries. Sharepoint doesn't automatically continue from the former index though, but apparently just keeps going with an internal index I guess, so now there's a gap in indexes, making the lists 'unmatched'.

That is, the elements at ID 1825 and above doesn't match the elements in the other list.

Anyone have any idea of how to "normalize" the ID's or maybe bulk-modyfing the existing elements, and then making sure, the list continues at the same IDs?

I appreciate any and all pointers at this point.

/Dynde

Answer 1

Don't use the ID column. As you can't control it, you'll go into messing up more and more.

A suggestion : use you own "business key". You can maybe add a Guid column on both lists, and populate the same guid for the two records.

Another possibility, use a "master" list having the ID, and reference this master list in the two child list with a lookup column.

Answer 2

I ended up changing the custom workflow code from the simple method of forming a link using the same ID as the symbol, and instead searching for the same title in the opposite list. It takes a performance hit (because of the search), but works well. It also allows me to output "no corresponding element found in opposite list" if the other list hasn't been updated.

Thanks for all the help