Dr. Evil Posted September 21, 2022 Share Posted September 21, 2022 👋 Hello and thank you! 😊 looking for a calculation to properly concatenate address/suite/city/state/zip fields with punctuation on one line ie: 1235 Alley Way, Suite A, Dreamcity, 95991 CA also looking to avoid goofy results if there is any missing data ie: ,, Suite A, Dreamcity, 95991, with my limited knowledge I'm currently using: Case(not IsEmpty(address); address & ", ") & Case(not IsEmpty(suite); suiteTitle & " " & suite & ", ") & Case(not IsEmpty(city); city & ", ") & Case(not IsEmpty(state); state & " ") & Case(not IsEmpty(zip); zip) which may work if I could trim off the ", " at the end if ever it is hanging out at the end. Link to comment Share on other sites More sharing options...
comment Posted September 21, 2022 Share Posted September 21, 2022 Try something along the lines of: Substitute ( List ( StreetAddress ; Suite ; City ; Trim ( State & " " & ZIP ) ) ; ¶ ; ", " )  Link to comment Share on other sites More sharing options...
Dr. Evil Posted September 21, 2022 Author Share Posted September 21, 2022 Works beautifully! Thank you!.. tho I do not fully understand how its working, lol. I understand the Substitute and List function, but on the Trim, is the function only looking at fields State and Zip? ...also, I have fields suite and suiteTitle, so what if I did not want result Suite, H, but rather Suite H I like the idea of breaking down the address2 into selectable titles for the user. But if too messy, I can convert to using an Address2 field where user will have to type the entirety of the address2 in, ie: APT/SUITE/BLD/ETC 123 Link to comment Share on other sites More sharing options...
comment Posted September 21, 2022 Share Posted September 21, 2022 1 hour ago, Dr. Evil said: I do not fully understand how its working It's taking advantage of the fact that the List() function skips empty values. 1 hour ago, Dr. Evil said: on the Trim, is the function only looking at fields State and Zip Yes. If both fields are filled, you get both, separated by a space. If only one is filled, the space is trimmed away. If both are empty, the entire value is empty and therefore skipped. 1 hour ago, Dr. Evil said: I have fields suite and suiteTitle Do the same thing with Trim(). Â 1 Link to comment Share on other sites More sharing options...
Dr. Evil Posted September 22, 2022 Author Share Posted September 22, 2022 Thank you Comment, working like a dream! Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 582 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now