HEADACHE!! Multistep formula for weight change and transposing columns

Topic Labels: Formulas
Solved
2573 11
cancel
Showing results for
Did you mean:
6 - Interface Innovator

Hiya, I am fairly new to Airtable and have been tasked with setting up our CRM for the charity (HUGE JOB!)

So... we have to get our dogs weighed roughly every month, but I need to be able to compare current weight with previous weight and how much the weight has either gone up or down. What I would like is when I come to enter a new current weight (in the {Current Weight kg} & {CW Date}(which is automatic date), the data that is already in those fields move to the {Previous weight} column and {PW Date} column (currently I am copying and pasting the data from current columns to previous columns before I enter the new current weight which is not ideal!.

I then would like to merge my two formula columns {Difference} & {Difference in weight} into one formula so that all it basically shows is the weight difference and either a colorful up arrow or a down arrow emoji.... (oh and have the blank fields remain blank (not saying up!!)

Can anyone help me do this?

1 Solution

Accepted Solutions
6 - Interface Innovator

@Nikiska  I made a base + script for you.  I believe this will do exactly what you're asking for.

Watch the loom video below for a 60 sec demo

Copy this base to get the script

https://airtable.com/appUyycRiK3Eum1z3/shrALRM0ndztSAHCm

11 Replies 11
6 - Interface Innovator

Hey @Nikiska.  I'm pretty sure this can only be accomplished with some scripting.  I can help you with the script, it shouldn't be too bad.  Would you mind sharing some test data with me so I can write and test the script for you?

10 - Mercury

Your up/down column needs an IF() statement that checks to see if there’s anything in Previous weight before doing the calculation that results in up/down.

IF(
AND(
{Previous weight}, {Previous weight}-{Current weight}<0
),
“up”,
IF(
AND(
Previous weight}, {Previous weight}-{Current weight}>0
),
“down”,
“”
)

As far as eliminating copy/paste, I would add a checkbox field called Enter New Weight that triggers an Update Record automation that copies the data from the current weight and date to the previous weight and date, clears the current weight and date and resets the checkmark to unchecked. But but but but if you need to keep a record of previous weights this will not work.

11 - Venus

Hey @Nikiska!

Just letting you know that I’ve seen this! I’m going to take some time to play around with this and put together some solutions for you.

10 - Mercury

Oops I haven’t had enough coffee. The formula to just have the weight difference and emoji is

IF({Previous weight}, {Previous weight}-{Current weight})& IF({Previous weight}-{Current weight}>0, ⬇️”, IF({Previous weight}-{Current weight}<0, “ ⬆️”, “ 🟰”)

6 - Interface Innovator

Thank you 🙂

6 - Interface Innovator

Sure - I have screenshot the actual airtable table in my question, but attached is a couple of rows in an excel sheet (no formulas, just figures)

6 - Interface Innovator

Thank you, those emoji's work great, I now need to add the actual weight difference into your formula somehow!! 😁 So the column will read -0.75kg ⬇️ ;   or 0.9kg ⬆️  ; etc etc!

This is what I am currently using to get the difference in weight to 2 decimal places:

ROUNDUP(FLOOR(SUM({Current Weight (kg)}-{Previous weight}),0.001),2)& "kg"
6 - Interface Innovator

@Nikiska  I made a base + script for you.  I believe this will do exactly what you're asking for.

Watch the loom video below for a 60 sec demo