Help

Linking Records

Topic Labels: Base design
404 3
cancel
Showing results for 
Search instead for 
Did you mean: 
readbetsyread
4 - Data Explorer
4 - Data Explorer

I am working on a base with one table that has a record of all my fabric purchases, their respective lengths and widths. I want to create a my second table as a list of patterns and the length and width requirements for the fabric necessary to complete the pattern and have it automatically pull what fabrics I have that are eligible for the project based on the length and width criteria of the pattern. Is this possible?

3 Replies 3
Andy_Lin1
9 - Sun
9 - Sun

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 pilcrowMatch § 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 nameMatch § character, which now indicates that this entry was not matched in the previous stepMatch 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.

Sho
11 - Venus
11 - Venus

@Andy_Lin1 approach is also interesting. Converting length to string length is a good idea.

 

@readbetsyread How about a slightly different approach than the automatic calculation, using Share link?

I made some changes to Andy_Lin1's and you can take a look at it.

Airtable - Cloth/Pattern Dimension Comparison

There is an Open URL field in the Patterns2 table.

The URL formula looks like this

"https://airtable.com/appKyvhgxaIfXMisy/shrof9oAs31P4uzSs/tblMSekfrw9g7lXvX"&
"?filterGreaterOrEqual_Width=" & {Width} &
"&filterGreaterOrEqual_Length=" & {Length}

Clicking on this will open a shareview of the Fabrics table, and URL filtering will extract only those fabrics that match the criteria.

Once you have determined the fabric, copy the Name and paste it into the Fabrics link table.

@Sho Wow, I didn't know that you could filter a shared view in such a way. Thanks for teaching me something new!