Table association (keys, lookups) question

[Chris_Hong]

I’m a new user, so apologies if this query comes off as ignorant.

I’m trying to set up an interaction between two tables:

1. [Books], which has a list of book titles (with full ISBNs as the primary key), and

2. [ISBN Digits-Vendor Reference List], which is a reference table that has the first 4-8 digits (e.g. a truncated pattern — ‘978-0-2280’ — out of a full ‘978-0-2280-xxxx-x’) of a ISBN as a primary key (the “Vendor/Publisher Code”), and a second column that’s just the name of the vendor/publisher.

Like:

‘978-0-2280’ | ‘Random House’

The truncated pattern can vary in length, for example:

‘978-3-2’ | ‘Scholastic’

In [Books], I want to “look up” the vendor/publisher from [ISBN Digits-Vendor Reference List] by matching the “substring” ISBN pattern in [ISBN Digits-Vendor Reference List] against [Books]'s “full” ISBN primary key.

So any full ISBN like ‘978-0-2280-1111-1’ in [Books] can look up and pull in ‘Random House’ from [ISBN Digits-Vendor Reference List] based on the pattern of the first 8 digits.

So far, I haven’t had any luck. Is this even possible?

Thanks,
Chris

[Justin_Barrett]

The full process is outlined in my earlier message, including all formulas and extra fields you’ll need. If it would help, I can build a sample base with this setup in place so you can see how everything ties together, but I’m pretty busy these days, so it might take me a day or two to put it together.

EDIT: Sorry, it just clicked that without the dashes in the ISBN codes, it’s going to be trickier setting it up. Give me a few days to mull this over.

[Justin_Barrett]

Quick question: is it possible to still store the ISBN in Airtable, including dashes, and use a formula field to remove them so you’ve got a “clean” version for connecting to your external database? That would allow the system to remain as I outlined above. It’s kinda critical for those dashes to be there so that we know how to break up the full ISBN and search against the padded partials. With nothing but numbers, it pretty much breaks the whole thing.