Apr 17, 2019 12:33 PM
AirTable Gurus:
I’ve been struggling for a couple hours to apply @W_Vann_Hall’s solution from this post to a similar scenario. I wonder if anyone would be willing to take a quick look and see what I’m doing wrong in my rollup formula? Here’s a link to my example base .
As you may be able to see from what I’ve got, I’m trying to calculate the change between a patient’s Weight in one record with the Weight in the next record. Unfortunately, the records are not sequentially dated, so I couldn’t use the exact formula @W_Vann_Hall provided. Instead, I just auto-numbered the records, and tried to use the rollup field to provide the Weight from the “previous” record by just subtracting “1” from the Patient_Record field. Obviously, it’s not working, but I fear I’m out of my depth here and can’t seem to figure out why…
Apr 17, 2019 12:57 PM
Unfortunately we can’t look very deeply at your base because of how you shared it, but I was able to make a new share link that will work:
Playing with it now to see what I can come up with…
Apr 17, 2019 01:12 PM
Thanks, @Justin_Barrett! For future reference, what should I have done when sharing it?? (I’m still new to AirTable…). :grimacing:
Apr 17, 2019 01:32 PM
Okay, the first problem with your rollup formula in the [Records]
table is that it’s rolling up from the {Weights}
field in the [Calc]
table, but your FIND
function in the formula is searching for a number based on {Patient_Record}
field, which it will never find. You need to add that number to the weight in a formula field, then roll up that field in the [Calc]
table instead of just the direct weights. @W_Vann_Hall mentioned this in his post, but didn’t give a specific example of how to do it, which is why it might’ve been overlooked when you recreated his setup.
In your case, your formula would begin with the {Patient_Record}
value (with leading zeroes for a consistent length), a colon, and then the weight, like this:
001:95
There are some other pieces missing from the aggregation formula as well, which I’ll try to fix as I fiddle with it some more.
However, that’s not the only issue I see. Because your [Records]
table contains records for more than one patient, you’re going to end up with messy values as you transition from one patient to the next; e.g. from JD1’s last weight in record 27 to JD2’s first weight in record 28. That’s going to be a much more difficult challenge to overcome.
As for sharing bases, click the “Share” button in the upper-right corner of the Airtable UI when looking at your base (or the “Share” link on the base’s contextual menu when viewing your workspaces). In the dialog that opens, change the option under “Shared base link” to “Private read-only link”. The URL you want to share is in the pink box.
Apr 17, 2019 02:21 PM
Okay, I’ve got a mostly-working solution. The only thing this doesn’t address is the change between patients, and how that throws off the weight change calculation. I also deviated from the setup laid out by @W_Vann_Hall a little bit.
First, add a field to your [Records]
table named {Weight Padded}
, using the following formula:
REPT(" ", 5-LEN(Weight & "")) & Weight
This prepends a bunch of spaces in front of each weight value, such that the length of the resulting string is always exactly five characters. I chose 5 because I didn’t think you’d need more than that many characters for a given weight: ###.# . However, if you need more, just increase the number, but note it because you’ll need it later on.
In the [Calc]
table, modify the {Weights}
rollup to pull from {Weight Padded
}, using only "values"
as the aggregation function.
Back in [Records]
, use this for the {Previous Weight}
rollup aggregation function:
IF(
AND(
Date,
Weight
), VALUE(
MID(
ARRAYJOIN(values,""),
(({Patient_Record} - 2) * 5) + 1,
5
)
)
)
NOTE: if you changed the value from 5 to some other number in the {Weight Padded}
formula, replace each “5” in this one with the new number.
Rather than trying to find specific text, this simply uses the {Patient_Record}
value to calculate how far into the array-joined mega-string to grab a five-character chunk. That chunk is then turned into a numerical value.
Finally, change the {Change in Weight}
formula to this:
IF({Previous Weight}, Weight-{Previous Weight})
And the result looks like this (with most helper fields hidden):
If you want to only show a weight change when there’s one to show, you could use this, which would hide all of the distracting 0.0 entries.
IF(AND({Previous Weight}, Weight-{Previous Weight}), Weight-{Previous Weight})
Apr 17, 2019 02:36 PM
@Justin_Barrett, this is super helpful, thank you so much. I’m going to fiddle with it when I get back to the office tomorrow and see if I can get it to work with my original data, but it seems like a great solution. And thanks for identifying the issue with the messiness of patient changes. Seems like I might be able to fix that with another messy rollup that matches the Link to Patient with a new “Previous Patient” field… I’ll fiddle with that some more tomorrow too… Thank you again!!!
Apr 18, 2019 09:18 AM
@Justin_Barrett, Your solution worked really well, thank you! And as I suspected, I was able to create a {Previous Patient} field by doing the same linking back and forth to the [Calc] table, and then only running the {Change in Weight} formula when the {Patient} and {Previous Patient} fields matched.
I’m running into another tricky problem that I could use your help with. I’d like to be able to note the last {Date} on which the Patient’s {Weight} changed, but I can’t seem to figure out how to make it happen. This is one of those problems where I think that my spreadsheet-configured brain is having a really hard time wrapping my head around a possible solution.
In a spreadsheet, I would just say something to the effect of: IF({Change in Weight}!=0,{Date},[copy the {Date} from the previous row]), and it would just keep copying the prior date until there was a change.
I’ve tried a few different ways to mimic this behavior here, but I just can’t seem to get it right.
Any ideas?
Apr 19, 2019 07:16 AM
Add a {Change Date}
field to your [Records]
table, using the following formula:
IF(Patient_Record=1, Date, IF({Change in Weight}, Date))
This will track the date on the very first record, and on all subsequent records where a weight change is recorded.
In the [Calc]
table, add a rollup field named {Change Dates}
:
ARRAYUNIQUE
will only keep the actual dates, stripping out the blank values.
Back in [Records]
, add a lookup field named {All Change Dates}
:
Finally, add a formula field called {Previous Change}
with the following formula:
IF(
NOT(
FIND(
{Change Date},
''&{All Change Dates}
)=1
),DATETIME_FORMAT(
MID(
''&{All Change Dates},
FIND(
{Change Date},
''&{All Change Dates}
)-24,
24
),"MM/DD/YYYY"
)
)
Hide the two new helper fields, leaving only {Previous Change}
Apr 19, 2019 07:48 AM
@Justin_Barrett, That. Is. Brilliant. Thank you SO much. It appears to work perfectly!! One question, just for my own understanding: I’m not clear on what the NOT() function does in the last formula?
Apr 19, 2019 08:02 AM
NOT
inverts the result of the condition inside it. In this case, I’m checking to see if the FIND
function returns 1, which would point to the first date in the list. I want to ignore the first date because that’s going to be used when there’s an actual weight change. If FIND
returns 1, the condition is true, which is then inverted to false by NOT
, so the rest is skipped for that first line. The next time the change date is found in that merged array, the FIND
result is 25, so the comparison against 1 is false. NOT
makes it true, so it executes the next portion, which extracts the date that’s one before the found date.