I use Airtable for my business and have my customers fill out a form with their info. For the Address column I currently have it as a single line text and that is causing issues. Customers are filling it out is '10 whalen rd' instead of "10 Whalen Rd Rochester NY 14608". I have the field as required however because it is a single line text I can't force how the address is implemented. How can I fix this?
How to force a form to make an Address show as St #, St, City, State, Zip
+3
+19Hey @mc585 One way to solve this would be to break the address up into multiple components and then use a formula to 'build it back together'. Like when you purchase something online, there's usually 'address line 1' and 'city' and 'zip code' etc. Here's an example you can emulate.
Table Setup:
Formula To Combine Address Fields:
CONCATENATE(
IF(
{Address Line 1}, CONCATENATE({Address Line 1}, ", "), ""
) ,
IF(
{Address Line 2}, CONCATENATE({Address Line 2}, ", "), ""
),
IF(
{Address City}, CONCATENATE({Address City}, ", "), ""
) ,
IF(
{Address State}, CONCATENATE({Address State}, ", "), ""
),
IF(
{Address Zip}, CONCATENATE({Address Zip}, ", "), ""
),
IF({Address Country}, {Address Country}, "")
)
+3Hey @mc585 One way to solve this would be to break the address up into multiple components and then use a formula to 'build it back together'. Like when you purchase something online, there's usually 'address line 1' and 'city' and 'zip code' etc. Here's an example you can emulate.
Table Setup:
Formula To Combine Address Fields:
CONCATENATE(
IF(
{Address Line 1}, CONCATENATE({Address Line 1}, ", "), ""
) ,
IF(
{Address Line 2}, CONCATENATE({Address Line 2}, ", "), ""
),
IF(
{Address City}, CONCATENATE({Address City}, ", "), ""
) ,
IF(
{Address State}, CONCATENATE({Address State}, ", "), ""
),
IF(
{Address Zip}, CONCATENATE({Address Zip}, ", "), ""
),
IF({Address Country}, {Address Country}, "")
)
Thank you! I was thinking about doing this but was hoping there was a better way. These formulas will be helpful though!!
+3Hey @mc585 One way to solve this would be to break the address up into multiple components and then use a formula to 'build it back together'. Like when you purchase something online, there's usually 'address line 1' and 'city' and 'zip code' etc. Here's an example you can emulate.
Table Setup:
Formula To Combine Address Fields:
CONCATENATE(
IF(
{Address Line 1}, CONCATENATE({Address Line 1}, ", "), ""
) ,
IF(
{Address Line 2}, CONCATENATE({Address Line 2}, ", "), ""
),
IF(
{Address City}, CONCATENATE({Address City}, ", "), ""
) ,
IF(
{Address State}, CONCATENATE({Address State}, ", "), ""
),
IF(
{Address Zip}, CONCATENATE({Address Zip}, ", "), ""
),
IF({Address Country}, {Address Country}, "")
)
I did what you put above and it worked great thank you! I have an Interface addon with Page Designer that makes invoices for me and it pulls the customer's info from when they fill out the form. However, now if I change where that invoice is pulling the address from then every invoice from today on will be correct but all the old ones will show a blank address. How do a make a formula for a new column that will show both my old address column and the new formula one? (Obviously only one will be filled out so I won't end up with two addresses). And is there a way to prioritize the order of which is chooses incase there are two filled out?
+20I did what you put above and it worked great thank you! I have an Interface addon with Page Designer that makes invoices for me and it pulls the customer's info from when they fill out the form. However, now if I change where that invoice is pulling the address from then every invoice from today on will be correct but all the old ones will show a blank address. How do a make a formula for a new column that will show both my old address column and the new formula one? (Obviously only one will be filled out so I won't end up with two addresses). And is there a way to prioritize the order of which is chooses incase there are two filled out?
You can use an IF() formula to do this.
The formula below checks to see if a value is present in the old address field. If there is, it returns that value, otherwise, it computes the address using the formula referenced above. TLDR, this prioritizes the Old Address Field.
IF({Old Address Column}, {Old Address Column}, *INSERT FORMULA FROM ABOVE*)
The formula below prioritizes the new address fields. You'll need to insert one of the new address fields in the first argument.
IF({One of the New Address Input Fields}, *INSERT FORMULA ABOVE*, {Old Address Field})Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.