Auto-Link Entry to Another Table By Matching Content (OR Auto-Fill Single Select Field Based on Content of Another Field)


I’m not sure if this is possible with AirTable, but I’m hoping someone more savvy can help me brainstorm a solution.

I’m a lawyer, and I’d like to create a database of cases that are cited in briefing, so that I can go back and reference my notes / summaries of each case when they are cited again in a new case. The thing I’m struggling with right now is whether there is a way to associate each case with its Jurisdiction (the court that decided the case) and its Reporter (the publication where the case appears).

There is a limited universe of Jurisdictions and Reporters, most of which I have pulled into separate tables already.

Cases come from one of the following:

  • The U.S. Supreme Court
  • One of the state supreme courts
  • One of the U.S. Courts of Appeal
  • One of the state appellate courts
  • One of the U.S. District Courts
  • One of the state trial courts

Each case from an appellate or trial court has a similar citation structure that goes like this:
[Volume] [Reporter] [Page] ([Jurisdiction] [YYYY])

Each case from a supreme court has the same structure, but omits the Jurisdiction, which is inferred from the Reporter (i.e., only supreme court cases appear in those reporters, so there’s no need to put the jurisdiction):
[Volume] [Reporter] [Page] ([YYYY])

To make things somewhat more complicated, there are also “unpublished” cases that appear in Westlaw (an online database), and that have slightly different citations:
[Case No.] [Year] WL [Page] ([Jurisdiction] [MMM. D, YYYY])

So, for example, full citations might look like this:

  • A case decided by the Supreme Court: 49 U.S. 457 (2003)
  • A case decided by the Alabama Supreme Court: 23 Ala. 289 (2005)
  • A case decided by a U.S. Court of Appeals: 568 F.3d 456 (9th Cir. 2010)
  • A case decided by a state court of appeals: 398 A.2d (Md. Ct. Spec. App. 2015)
  • A case decided by a U.S. district court: 530 F.Supp.3d 234 (S.D.N.Y. 2010)
  • An unpublished case by a U.S. District Court: No. 05-CR-6050 CJS, 2005 WL 2709572 (W.D.N.Y. Oct. 21, 2005)

On the Cases Table, I’d like to be able to enter the full citation of a case (or paste it from another source), and have AirTable automatically “pull” the Jurisdiction, Reporter, and Date (either full date or year) from the citation and (1) for the Jurisdiction and Reporter, either select the appropriate value in a single select field in the Cases Table, or associate the Case with the appropriate entry in the Jurisdiction Table and Reporter Table; and (2) for the Date, enter the appropriate value in a Date field.

Anyone bored and up to the challenge of helping me figure this out? :grimacing: