Forming simple relationships with nonID fields?

Given the circumstances in which data was provided, I would like to form a relationship between two tables using a text-based field.

One is a structure based table (JChem entity), and one is a regular database table (Standard entity). The common field between the two is a text field with labels such as NMP0010.

When I try to form the many to one relationship from the standard entity (containing the assay data) to the structure based table using the common field, there is a yellow ! next to it. Upon clicking finish, I get the error seen in the attached image.

The CDIDs are mismatched between the two, so I need to link between this field. If there was some Excel-esque way to enter a formula and trim out the integer from the label (i.e. 0010), that would be great, but I don't know how (or if possible) to do so.

Any help on my options for getting this relationship formed would be greatly appreciated! Thanks!

Hi, it looks like you are trying to do the right thing, but the data is not compatible.

The error is because there are duplicated values in the column you are using at the "one" end of the relationship. At the "one" end the values must be unique. If you look through your data and correct this problem then you should be able to create the relationship.

There is no way to provide the "timming" you describe inside IJC, but of course there are plenty of appoaches that can be used to do it before you import the data, or you could potentially do this directly in the database using SQL once the data is imported.

Tim

Tim,

That worked marvelously! Thank you so much!