Update Status of Car in Car Table Based on changes in Status in Contract Table

Hi @Mohsin_Raza!
You can absolutely do this with an automation, but a simple lookup field will work as well. I asume that the Car Table and Contract Table are linked for this.

In the Car Table, create a formula field and use this, or a variation on this:

IF(REGEX_MATCH({Status}, 'Returned|Swapped Cars & Bond Transferred'),'✅ Available')

Then you look this new field up in the Contract Table.

IF(REGEX_MATCH({Status}, 'Returned|Swapped Cars & Bond Transferred'),'✅ Available')

Status are different in both the tables.

status in contract table like this

status in cars table like this

So does it will work.

And How to apply formula on a status column as it is a single select field.

Oh, okay, I didn’t get that you already had a status field in the other table you wanted to use. Then you absolutely need an automation.

This should work

Yeah right, but this will work for any status option. Meaning for any status option changed to returned or swapped this is gonna work which is i dont want. I want only for the limited options

Assumtions:

1. One car can have a history of many contracts; can have many records linked in the linked field.
2. A car changes status only once from any of Active Rental, Rent to Own Active, AA Auckland to Returned or Swapped Cars & Bond Transferred.

I’ve tested an automation that works for assumtion #2, but unfortunately I don’t have more time today or tomorrow to add assumtion #1 into the mix.

@Adam_TheTimeSavingCo Are you available and have time to help? Or someone else?
Otherwise I will return on Wednesday to provide a solution.

Under Contract Table, Currently I am adding only one car to a single contract. Meaning a single contract is linked to single car while a single car is linked can be linked to multiple contract but not at the same time.

2. A car changes status only once from any of Active Rental, Rent to Own Active, AA Auckland to Returned or Swapped Cars & Bond Transferred.

This should not work if I change Contract status from returned to swapped or swapped to returned.

Hi @Mohsin_Raza,
I’m sorry for the extra day it has taken me to get back to you. Life happened.

I’m not sure what you meant by this, however, I think my solution has this covered.

I’m sharing my test base with you so you can see what I’ve done better than from images alone. You can copy it and then explore.

Any field starting with :gear: fills a formula or automation function, but can be hidden away from view.

Any field with :white_check_mark: in their name helps determine what contract is the latest in instances where the car has more than one contract linked. I’d built the solution before my break and I find it a good idea to build this in from start, rather than to have to add it at a later date and to have to correct automations doing their job, but not what you now need them to, manually. So, I left that part in the test base.

Hopefully this solves things for you.