We are trying to add a formula that if one room is available even though others are not it should still says “Available”. If one room says Becoming Available but there’s no room that is available then it should say “Becoming Available”. If all rooms are rented then it should say “Rented”
If and Find Formula
+25Hi,
i would suggest change a capital letter in second option, at least write “Becoming available”, because your formula "find word Available, but not in “Becoming Available” will be much harder.
IF(FIND(‘Avail’,x)>0, ‘Available’, IF(FIND(‘Becom’,x)>0, ‘Becoming…’, ‘Rented’)) - is a simple way . x - your search field, in your case, it’s lookup, so you should put something like CONCATENATE(field) instead of just field name, to convert array to string.
the hard way, if you can’t change values, may be something like
LEN(SUBSTITUTE(field,‘Becoming’,’’))-LEN(SUBSTITUTE(field,‘Availab’,’’))
result will be +1 for each ‘Becoming Available’, but -7 for each ‘Available’
you can compare it to zero or do like
SWITCH(
ROUNDUP(
(LEN(SUBSTITUTE(field,‘Becoming’,’’))-LEN(SUBSTITUTE(field,‘Availab’,’’))/99),0)
,-1,‘Available’
,1,‘Becoming’
,0,‘Rented’)
again, don’t forget about CONCATENATE(field)
Hi,
i would suggest change a capital letter in second option, at least write “Becoming available”, because your formula "find word Available, but not in “Becoming Available” will be much harder.
IF(FIND(‘Avail’,x)>0, ‘Available’, IF(FIND(‘Becom’,x)>0, ‘Becoming…’, ‘Rented’)) - is a simple way . x - your search field, in your case, it’s lookup, so you should put something like CONCATENATE(field) instead of just field name, to convert array to string.
the hard way, if you can’t change values, may be something like
LEN(SUBSTITUTE(field,‘Becoming’,’’))-LEN(SUBSTITUTE(field,‘Availab’,’’))
result will be +1 for each ‘Becoming Available’, but -7 for each ‘Available’
you can compare it to zero or do like
SWITCH(
ROUNDUP(
(LEN(SUBSTITUTE(field,‘Becoming’,’’))-LEN(SUBSTITUTE(field,‘Availab’,’’))/99),0)
,-1,‘Available’
,1,‘Becoming’
,0,‘Rented’)
again, don’t forget about CONCATENATE(field)
I am not good with Airtable so how do I concatenate?
+25I am not good with Airtable so how do I concatenate?
Concatenate is a formula to join multiple values to a single string.
Problem with using lookup and some other fields in formula - it’s actually an array, not a single value. Even if it’s just a single value or nothing, it’s still an array of 1 or 0 elements.
When you link your record to some others, lookups will show content of some field in a linked records, like [“Available”, “Available”, “Rented”] and formula may count it in a wrong way. So, you should use CONCATENATE({Occupied/Vacant (from…)} ) in you case instead of just using {Occupied/Vacant (from…)}, it will turn value to “Available, Available, Rented” so formula will work with it in a right way.
In short, rename your {Occupied/Vacant…} field to “field”.
Put formula . Indeed, i spotted some mistakes in first, each “Becoming Available” will add -1, and each “Available” +7, so it will not work for 7Becoming+1Available, will show “Rented”, so you should update it when you have more that 7 rooms in a record.
That one is checked:
SWITCH(
ROUNDUP(
(
(
LEN(SUBSTITUTE(CONCATENATE(field),'Becoming',''))-LEN(SUBSTITUTE(CONCATENATE(field),'Availab',''))
)
/9)
,0)
,-1,'Becoming'
,1,'Available'
,0,'Rented')
and ensure it works.
rename the field back (formula will auto-update)
Concatenate is a formula to join multiple values to a single string.
Problem with using lookup and some other fields in formula - it’s actually an array, not a single value. Even if it’s just a single value or nothing, it’s still an array of 1 or 0 elements.
When you link your record to some others, lookups will show content of some field in a linked records, like [“Available”, “Available”, “Rented”] and formula may count it in a wrong way. So, you should use CONCATENATE({Occupied/Vacant (from…)} ) in you case instead of just using {Occupied/Vacant (from…)}, it will turn value to “Available, Available, Rented” so formula will work with it in a right way.
In short, rename your {Occupied/Vacant…} field to “field”.
Put formula . Indeed, i spotted some mistakes in first, each “Becoming Available” will add -1, and each “Available” +7, so it will not work for 7Becoming+1Available, will show “Rented”, so you should update it when you have more that 7 rooms in a record.
That one is checked:
SWITCH(
ROUNDUP(
(
(
LEN(SUBSTITUTE(CONCATENATE(field),'Becoming',''))-LEN(SUBSTITUTE(CONCATENATE(field),'Availab',''))
)
/9)
,0)
,-1,'Becoming'
,1,'Available'
,0,'Rented')
and ensure it works.
rename the field back (formula will auto-update)
Don’t worry about it. I have fixed it.
Login to the community
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.