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!