advice on creating relationship

[madman411]

Hi Guys.

 

So I have two tables - "Inventory" and "Consignors".

 

The Inventory table has one field that is called a "Secondary Asset ID" which would be the Consignors ID and the Asset ID tied together. The Consignor typically beings with an S, though that isn't always true (or the asset may not be consigned at all). A typical Consignor ID is usually three digits (i.e. S310). An example of a Secondary Asset ID would be S310-HDCR10010, or BSI-HDCR02654.

 

In the Consignors table I have three fields - Their ID (in this example, S310), the Consignors name, and their company. Currently I have the Consignor ID field tied with the Secondary Asset ID field, with a ::consignors_name field being displayed within the Inventory table. 

 

For example, for a record returned with Sec. Asset ID "S310-HDCR10010" in the inventory table, the consignors name (lets call them John Doe) will be returned from the Consignors table. 

 

My problem is that the relationship options don't allow me to match the fields properly, therefore it doesn't return the correct information from the consignors table. I wanted to avoid creating an additional field in the Inventory table to achieve this, as this information is provided by an external data source, whereas everything else is from within the database. 

 

Please help! Thank you.

[madman411]

Update: After some additional research I've found it necessary to create a separate field that will strip down the Secondary Asset ID to just the consignor number using the Case function. Then I will create the relationship based on the new field. I will leave the new field hidden as having the Consignors number appear twice on the same form is redundant. 

 

I believe a variation of the following calculation in my new field may work, but I'm still running in to issues:

 

Case ( Left( _secondary_asset_id; 4) = "-" ; Right( _secondary_asset_id; Length( _secondary_asset_id) - 4

 

I just need to retrieve the characters up until the hyphen, which are typically the first three or four characters.