Aug 31, 2023 05:20 AM
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?
Solved! Go to Solution.
Sep 04, 2023 11:01 AM
@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
https://www.loom.com/share/9f17f57c72e14ad5be67d04a19e83382?sid=402c3700-310b-4840-a019-025149e885e1
Copy this base to get the script
Aug 31, 2023 06:15 AM
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?
Aug 31, 2023 07:43 AM
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.
Aug 31, 2023 07:45 AM
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.
Aug 31, 2023 07:51 AM - edited Aug 31, 2023 07:54 AM
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, “ ⬆️”, “ 🟰”)
Aug 31, 2023 12:55 PM
Thank you 🙂
Aug 31, 2023 01:45 PM
Aug 31, 2023 01:51 PM
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:
Sep 04, 2023 11:01 AM
@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
https://www.loom.com/share/9f17f57c72e14ad5be67d04a19e83382?sid=402c3700-310b-4840-a019-025149e885e1
Copy this base to get the script
Sep 05, 2023 08:58 AM
I’m confused. You have weights entered to 1 decimal point, but you want the weight differences to be to 2 decimal points? I think I’m missing something…