Yes, but it's a little complicated... Here's an example base for you to reference:
https://airtable.com/appT736eIWPC4dfbt/shrjuX8ojLYH7Tqjd
The reason why this is complicated is because Airtable doesn't support doing math in rollup fields without reducing it to a single number (as far as I know, here's the help page on it: https://support.airtable.com/docs/rollup-field-reference ), nor does it support doing math in a formula field that uses values from a lookup field. Otherwise, you'd be able to generate two truth tables (one for width, one for length), and then apply to the list of fabrics, like how in Excel you could use SUMPRODUCT to filter a range based on multiple criteria.
Solution
What we're going to do is abuse the fact that regular expressions allow us to use numbers, and importantly, numbers from other fields. (Otherwise, if you just had a static set of numbers across all patterns, you could just set the conditions in the lookup field, as in: https://support.airtable.com/docs/conditional-counts-lookups-and-rollups.)
One caveat: you will have to link every fabric to every pattern for this to work (well, every fabric you'd like to consider using in a pattern). You can set up an automation to do this; or you can do it once, and then copy and paste it whenever you create a new record.
So, to start, we have two tables: Fabrics and Patterns
- Fabrics
- Width, Number field
- Length, Number field
- Patterns, Linked Record field
- Dimensions in Ticks, Formula field
- Patterns
- Width, Number field
- Length, Number field
- Fabrics, Linked Record field
- Eligible Fabrics, Rollup field based on Fabrics/Dimensions in Ticks
The width and length are integer fields. If you regularly work with decimals/fractions, we'll need to convert them to whole numbers (by multiplying by 10, 100, 100, etc.). And obviously, the complicated bit is in the formula and rollup fields.
Here's the formula for the field {Dimensions in Ticks}:
Name & "§" & REPT("x", Width) & REPT("y", Length)And if you're working with decimals, it's much the same, just multiply each dimension by 10 (or however many decimal places you need).
Name & "§" & REPT("x", {Width (Decimal)} * 10) & REPT("y", {Length (Decimal)} * 10)What we're doing is counting out our dimension numbers with ticks ("x" for Width and "y" for Length), so that we can pass a string that is essentially {Name} & {Width} & {Length}, but in a way that Airtable can work with. For example, if we have Fabric A with a width of 5 and length of 2, the code produces "Fabric A§xxxxxyy", 5 "x" for the width, and 2 "y" for the length. I've used the section symbol (§) because it's not usually something that's in a fabric name – it can be replaced with any other symbol, even an emoji, but just remember to replace it in the next formula as well.
Here's the formula for the field {Eligible Fabrics}:
SUBSTITUTE(
REGEX_REPLACE(
REGEX_REPLACE(
ARRAYJOIN(values, "¶"),
"([^¶]+)§x{" & Width & ",}y{" & Length & ",}",
"$1"),
"[^¶]+§[xy]+¶?",
""),
"¶",
", "
)
and here's the version for decimal width and length:
SUBSTITUTE(
REGEX_REPLACE(
REGEX_REPLACE(
ARRAYJOIN(values, "¶"),
"([^¶]+)§x{" & ({Width (Decimal)} * 10) & ",}y{" & ({Length (Decimal)} * 10) & ",}",
"$1"),
"[^¶]+§[xy]+¶?",
""),
"¶",
", "
)
To adapt the code to your needs, just change the field names to those in your base, and if using decimal measurements, add zeroes to the "10" to suit the number of decimal places.
Explanation
It's best to explain it from the inside out, since each function builds on the previous one.
ARRAYJOIN(values, "¶")
First, we join all the {Dimensions in Ticks} entries with the pilcrow/paragraph mark (¶, again chosen because it's not commonly used), this will tell us where one entry ends and where another begins.
REGEX_REPLACE(
[results of the previous step],
"([^¶]+)§x{" & Width & ",}y{" & Length & ",}",
"$1"
)
We then use the REGEX_REPLACE function to search for all entries that have at least the number of ticks ("x" and "y") of the dimensions of the pattern, and replace them with just the name of the fabric. The regular expression query is structured as follows:
| ([^¶]+) | § | x{" & Width & ",} | y{" & Length & ",} |
| Capture (save) all characters that are not a pilcrow | Match § character (used to separate name from dimensions) | x{a,b} means match instances of "x", from a minimum of "a" repetitions to a maximum of "b" repetitions. In this case, we're matching x, if there are at least {Width} number of x. By leaving out "b", there's no maximum limit to the number of "x" it can match. | This is the same as the previous expression for width; with both expressions, we're only matching those entries that have both the width and length required. |
When the function finds a match, we tell it to replace it with the captured group ($1). So, for example, if "Fabric A§xxxxxyy" is eligible, it'll become just "Fabric A", if not, nothing happens to it.
REGEX_REPLACE(
[results of the previous step]
"[^¶]+§[xy]+¶?",
""
)
The next step is to remove all the ineligible fabrics. We can identify these by the fact that they'll still have the § character.
| [^¶]+ | § | [xy]+ | ¶? |
| Match all characters that are not a pilcrow, i.e. the fabric name | Match § character, which now indicates that this entry was not matched in the previous step | Match all "x" and "y" characters. | Optionally match a pilcrow. Entries are separated by pilcrows, except at the end of the rollup. |
SUBSTITUTE(
[results of the previous step]
"¶",
", "
)
Finally we just replace all the remaining pilcrows (from the end of valid entries) with commas.
EDIT: Fixed up the formatting, cleaned up the regex, and changed "height" to "length", because we're talking about fabrics, not rectangles.
