HEADACHE!! Multistep formula for weight change and transposing columns

[Nikiska]

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?

@Ben_Young1 

[Nikiska]

Hiya - oops - didn't see that - I'm actually not that bothered to be honest. Most vet scales will give it to one decimal place so lets stick with that! Soz

[pressGO_design]

Then this should work:

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

As far as the automation part goes, I would need to know more about how your base is actually structured to help you do that without a script. @Jake_Wilson already provided a script for you if that’s the direction you want to go.

Good luck!