Table association (keys, lookups) question
That helps. Not needing more than the vendor/publisher name makes the setup slightly easier, and the varying length of the codes makes the setup only slightly more complex, so I guess it balances out in some odd way. :slightly_smiling_face: Even though the hyphen is in different places depending on how many digits are in the publisher code, the publisher code is always between the second and third hyphen, so we can use that to our advantage.
First, you’re going to need a “control” table, which will simply act as a place to collect information used by the system we’re building. I’ll often name these types of tables [CTRL]. After making the table, delete two of the three default records, and put a single period into the {Name} field of the one that remains.
In your [Books] table, add a link field pointing to the [CTRL] table, naming it {Link to CTRL}. Link the first record to that lone “period” record in [CTRL] manually, then drag-fill the rest. You should end up with something looking like this (minus the background color; this is copied from an existing table in one of my bases).
All records in [Books] will need this link in order for the system to operate. To ensure that all new records get linked, you could group this view by that link field. That way each new record will receive the link automatically.
Next, go to your [ISBN Digits-Vendor Reference List] table and create a similar link field setup pointing all vendor/publisher records to that lone record in [CTRL], and group by that field to ensure that all new publisher records will be auto-linked.
With that done, we need to give that [CTRL] table some data to collect, which we’ll create on the [ISBN Digits-Vendor Reference List] table. Make a new formula field named {Code Padded}, using the following formula (substituting your actual field name in place of {Code}😞
{Code} & REPT("X", 15-LEN({Code})
This will take all codes and pad them out to 15 characters with Xs. For example, the code for Random House that you listed above would look like this:
Code: 978-0-2280
Code padded: 978-0-2280XXXXX
The number 15 is somewhat arbitrary, but it just needs to be long enough to fully encapsulate any code it might be fed; i.e. the code itself before padding can’t be longer than 15 characters. If you want to mess with this number, feel free, but I’m going to use 15 for the purpose of this demo.
Make another formula field named {Publisher Padded} that does the same thing to the publisher name. However, before doing that, we need to figure out the length, so use a quick temp formula for this field for starters (again replacing {Publisher} with your actual field name:
LEN({Publisher})
Look through the results and find the highest number. For now, let’s say it’s 20. I prefer to have some extra characters on the end just in case, so I’ll go with 25. Replace that temp formula with this:
{Publisher} & REPT(" ", 25-LEN({Publisher})
That will pad all publisher names with spaces to take them up to 25 characters. We won’t see the spaces, but they’ll come in handy later.
Now move to the [CTRL] table and add a rollup field named {Padded Codes}, rolling up the {Code Padded} field we made in the publisher table, using the aggregation function:
ARRAYJOIN(values, "")
This will collect all of those padded codes and mash them together into one massive string. Now make another rollup named {Padded Publishers} that does the same with the {Publisher Padded} field, and using the same aggregation function.
Now go to the [Books] table, and add two lookup fields to pull the {Padded Codes} and {Padded Publishers} rollups from the [CTRL] table, using the same field names for convenience. This will bring these two massive strings we just made and make them available to every record.
Add a formula field named {Publisher Index} to the [Books] table using the following formula (as always, replacing field names as needed):
SEARCH(
LEFT(ISBN, FIND("-", ISBN, 7) - 1)
& REPT("X", 15-LEN(LEFT(ISBN, FIND("-", ISBN, 7) - 1))),
{Padded Codes} & ""
)
This uses what we know about the ISBN code design—that the publisher code is between the second and third hyphens, and that the second hyphen is always the sixth character—to determine how much of the left end of the full ISBN we want, then pads it with Xs to 15 characters and searches for that padded version in that massive mashed string of padded codes.
What should come back is a number representing the “index” of that code in the string, with the “index” being one larger than a multiple of 15. So say that the Random House padded code was found starting at position 31. Subtract 1 and we have 30. Divide that by 15 and we get 2. Using zero-based indexing (i.e. 0, 1, 2 vs 1, 2, 3), we know that it was the third code in the list.
Because of the way that rollups work with linked records from the same table, we can guarantee that the codes and publishers will be in the same order in their respective rollups. So now that we know that the third code is a match, we can extract the matching publisher name from the {Padded Publishers} mega-string by extracting its third item, with these items being 25 characters long.
Add a final formula field named {Publisher} using the following formula:
IF(
{Publisher Index},
TRIM(
MID(
{Padded Publishers} & "",
((({Publisher Index} - 1) / 15) * 25) + 1,
25
)
)
"❌ NOT FOUND ❌"
)
I wrapped the extracted text in a TRIM() function to get rid of the trailing spaces, leaving only the name. I also wrapped the whole thing in an IF to check for those rare cases where there is no match, alerting you via a colorful response that a match wasn’t found so that you can track down the missing code and add it to the code table.
NOTE: I put a good chunk of this together from memory, but have plenty of reference of similar setups in my own bases, so I’m pretty sure this will all work. If you run into any hiccups, or don’t understand something about the setup, just ask.
