best address

[Charity]

I have the following fields

 

nameFirst

nameMiddle

nameLast

nameSuffix

streetNumber

streetName

streetOther

suite

city

state

zip

country

 

an example of the result I need is

 

Devon G. Brown, PhD.

124 Broadway NW, Suite B

Denver, CO 80204

USA

 

but sometimes not all information exists such as nameMiddle, streetNumber or suite and I want the best way to list it so that those periods, commas, carriage returns or spaces do not list by themselves or the name 'suite' does not list if there is no suite.  I began to test for not IsEmpty all the way but nothing I try comes out right.  I even tried using List because I remember a post which did that but that has failed also.

 

Please what is the best way to create this type of calculation?  I have already spent at least 10 hours trying myself and another 10 hours searching.

[LaRetta]

Is the comma after the city required?  Standard addressing in the US drops that comma.

[Lee Smith]

See if this helps.

 

http://fmforums.com/forum/topic/92313-dont-leave-space-if-the-field-is-empty/#entry424044

[Charity]

I saw that one Lee and it helps a bit but not really.  I just checked and you are right LaRetta I do not need the comma between city and state but I need the comma before suite and nameSuffix.

[LaRetta]

Hi Charity,

 

See how this works for you:

Let ( [
name = TrimAll ( nameFirst & " " & nameMiddle & " " & nameLast ; 0 ; 0 ) & If ( not IsEmpty ( nameSuffix ) ; ", " & nameSuffix ) ;
street = TrimAll ( streetnum & " " & streetname ; 0 ; 0 ) & If ( not IsEmpty ( suite ) ; ", Suite " & suite ) ;
location = TrimAll ( city & " " & state & " "  & zip ; 0 ; 0 )
] ;
List ( name ; street ; location ; country )
)

There is no 'one size fits all'.  For example, on nameMiddle, is it the full middle name or just the initial?  If just the initial, do you type the period or do you need to include that in this calculation reformat?

 

Well, others may have better approaches but this should get you closer to what you need.

[LaRetta]

BTW, I had recognized that, since the current 'address' line only has streetNum and streetName, you could use Trim() instead of TrimAll() on that line but I left it the same for consistency.  I am hoping this gives you enough to know how to adjust to your needs.  In the following calculation, I changed it to Trim() so you can choose what you wish.

 

For me, the idea is to create each 'line' in the Let() statement and then combine.  Also, if there is no address but there is a Suite (which would make no sense), it will produce ,suite on that line.  If you wish to protect from those possibilities, you can instead change the suite line to:

street = Trim ( streetnum & " " & streetname ) &
​If ( not IsEmpty ( streetnum&streetname ) and not IsEmpty ( suite ) ; ", " ) & If ( not IsEmpty ( suite ) ; "Suite " & suite ) ;

You could do the same with suffix although, again, it wouldn't make much sense to have a suffix without a name.  :-)

[Charity]

I see how this works now.  Treat each line separately in Let so it is not confusing.  Use TrimAll between fields which are separated by spaces, if more than two otherwise use Trim.  Test prior fields for being empty before putting comma etc then use List to bring the lines together.  I see that there is no one size fits all.  We are skipping the period after middle if only one character but I know I can now adjust it if needed since I understand how to do it.  Here is what I ended up with and no matter what is omitted, it works.

Let ( [
name = TrimAll ( nameFirst & " " & nameMiddle & " " & nameLast ; 0 ; 0 ) & If ( not IsEmpty ( nameFirst&nameMiddle&nameLast) and not IsEmpty ( nameSuffix ) ; ", " ) & nameSuffix ;

street = Trim ( streetNum & " " & streetName ) & ​If ( not IsEmpty ( streetNum&streetName ) and not IsEmpty ( suite ) ; ", " ) & If ( not IsEmpty ( suite ) ; "Suite " & suite ) ;

location = TrimAll ( city & " " & state & " "  & zip ; 0 ; 0 )
] ;
List ( name ; street ; streetOther ; location ; country )
)

Thank you very much for showing me how it can go together like this.  I am hoping I will have to do this soon again with something else.  This is fun to do once I understand. 

 

 

 

[LaRetta]

I'm pleased you have it worked out, Charity.  I wanted to mention for others who might read this (and already with your approval) ... that you had messaged me that you needed a calculation with stringent business requirements for export to engravers so I proceeded with a full calculation. Your ability to easily understand and tweak the calculation (which will be exported) seemed appropriate.  

 

Normally, if only for display purposes, it is best to use merge fields, merge variables and the new Hide capability as much as possible.  Merge variable can be written using this same calculation, in fact.

[OlgerDiekstra]

I have similar requirements, but do have a need for comma's and sometimes other characters. I use if then statements with IsEmpty to determine whether to display a value or not:

if ( IsEmpty ( Address::Unit ); ""; Address::Unit & ", " )

 

That way, the comma is only included if the field has a value.

[eos]

I see how this works now.  […] This is fun to do once I understand. 

 

Yes, once you have the hang of it, you can do amazing things.

 

Allow me to add that I try to use Let() consequently, so if a field or an expression appears several times, create a variable, i.e.

Let ( [
  f = nameFirst ;
  m = nameMiddle ;
  l = ; nameLast ;
  suff = nameSuffix ;
  sNum = streetNum ;
  sName = streetName ;
  s = suite ;

  name = TrimAll ( f & " " & m & " " & l ; 0 ; 0 ) & Case ( Count ( f ; m ; l ) and Count ( suff ) ; ", " & suff ) ;
  street = Trim ( sNum & " " & sName ) & ​Case ( Count ( sNum ; sName ) and Count ( s ) ; ", Suite " & s ) ;
  location = TrimAll ( city & " " & state & " " & zip ; 0 ; 0 )
  ] ; 
  List ( name ; street ; streetOther ; location ; country )
)

This uses more vertical space, but the individual lines are better readable IMO, and it makes experimenting easier (e.g. when using the Data Viewer, you can swap a field reference for a literal string in a single location).

 

Note that you can simplify the string calculation (suffix, suite), and use Count() to test on empty fields (I think we once had a discussion about Length(), but I cannot remember the outcome); too bad that a simple Case ( textExpression ; …) doesn't work.

[comment]

 if a field or an expression appears several times, create a variable

 

Expression yes, field no. That's just waste of good RAM. If you find "f" more readable than "FirstName" (I don't), then rename the field.

[mikedr]

Here's another approach.  This would be for the calculation of a variable or a field.  I listed your field names as if they were variables just for convenience.  This assumes that nameFirst, NameLast, streetName, city, state, zip, and country are all always present, but it would be easy enough to modify if any of those might not be present at all times.

 

The basic idea is that you go through the building process, and for things that may not be present, check to make sure they are not empty before adding in the requisite commas, etc.

 

Finally, I wasn't sure what streetOther was all about, but assumed it would be separated by a space from streetName (following streetName) in the same line.

$nameFirst & " " &
if($nameMiddle ≠ ""; $nameMiddle & " ";"") & 
$nameLast &
if($nameSuffix  ≠ ""; ", " & $nameSuffix;"") & "¶" &
if($streetNumber ≠ ""; $streetNumber & " ";"") &
$streetName &
if($streetOther ≠ ""; " " & $streetOther;"") &
if($suite ≠ ""; ", " & $suite;"") & "¶" &
$city & ", " & $state & " " $zip & "¶" & $country & "¶" 

[eos]

Expression yes, field no. That's just waste of good RAM. If you find "f" more readable than "FirstName" (I don't), then rename the field.

 

I know where you're coming from, but I'd like to rename a field that has a a good and expressive, but maybe a bit long name (especially if it's fully qualified) into something shorter within the calculation's context, without renaming the field itself.

 

And of course 'f' isn't a good field name.

[eos]

$nameMiddle ≠ ""

Don't do that; there's a purpose-built function to test on empty / not empty (and some others that can be (mis-)used, too, depending on the circumstances).

Here's another approach.

It's the same approach, except that you build the list manually, which makes the whole calculation more difficult and harder to read. I recommend you study the above examples to see how this can be done in a more streamlined and neater way.

[mikedr]

Don't do that; there's a purpose-built function to test on empty / not empty (and some others that can be (mis-)used, too, depending on the circumstances).

It's the same approach, except that you build the list manually, which makes the whole calculation more difficult and harder to read. I recommend you study the above examples to see how this can be done in a more streamlined and neater way.

 

Very good!  That's why I love this forum.  I'm still learning FMP, and sort of do the Rube Goldberg approach to things . . . .

[mikedr]

Also, with respect to the variable, field name comments above, this is the technique I try to abide by as a best practice.  Would be curious to hear comments.

 

In a script, if a field will be referenced by more than one command, then I set a variable equal to it.  If a field is referenced within one calculation of one command, then I use let in that calculation.

 

The idea is that RAM is fast and plentiful, and pinging the server to constantly acquire the field name is slower and bandwidth is less plentiful. 

 

Thoughts?

[comment]

pinging the server to constantly acquire the field name is slower

 

I don't think that's what's happening. At least not with stored calculations, referencing local fields. I am convinced Filemaker is smart enough to acquire all the values in the list of dependencies once. And it certainly doesn't download them from the server more than once.

 

Incidentally, the same applies to most, if not all Get() functions. Though I too have been guilty of stuffing them into variables just to shorten. say "Get ( CalculationRepetitionNumber )" to "i", the idea that Filemaker somehow "calculates" these each time it encounters them is preposterous.

[mikedr]

I don't think that what's happening. At least not with stored calculations referencing local fields. I am convinced Filemaker is smart enough to acquire all the values in the list of dependencies once. And it certainly doesn't download them from the server more than once.

 

Incidentally, the same applies to most, if not all Get() functions. Though I too have been guilty of stuffing them into variables just to shorten. say "Get ( CalculationRepetitionNumber )" to "i", the idea that Filemaker somehow "calculates" these each time it encounters them is preposterous.

 

Interesting.  I picked that up from "somewhere," but obviously it does make sense when thinking about it that there's some type of caching going on.  We recently moved to an FMP solution this year.  The database is hosted by a hosting company in Portland (that is, the server is in Portland).  I split my time each year between Phoenix and Seattle, the former I have a 150/20 connection and the latter I have a 100/20 connection -- and since migrating to FMP, we haven't been back to Seattle yet.  From my perspective, I haven't seen any speed difference between the database running locally when I was testing it and running remotely.  That said, when I remote into my paralegal's computer in Minnesota -- and she has an Internet connection that is flakier than it should be -- I do see a bit of a slowdown. 

 

We typically spend a few weeks each year around Toronto as well, and the place we rent there has a very low speed connection.  I'm curious when we go there how quick everything will be.

[Charity]

Thank you all for inputting on this thread everyone.  

 

if ( IsEmpty ( Address::Unit ); ""; Address::Unit & ", " )

 

 

That is precisely what we were doing except testing not IsEmpty and dropping the useless default result of "".  Thank you though for helping, it is appreciated.

 

Don't do that; there's a purpose-built function to test on empty / not empty (and some others that can be (mis-)used, too, depending on the circumstances).

 

 

While I really appreciate your attempts, this is exactly what I was going to say to you about using Count in the way you did.  It took me quite a bit to figure out why you were counting them and what you were trying to do which was test if empty.   It is a good trick to know so thank you for it but the next person who must read this calculation would be just as baffled as I was.  If the calculation is faster because of using Count over IsEmpty it would be worth using but I doubt that is the case.  Please correct me if wrong.  Also your Let variable 'l' has a semi-colon so it does not work but I knew to remove it.  

 

Also, your calc fails if there is no streetNum and streetName but it has a suite.  Like LaRetta mentioned, why would we ever have a suite without a street but I wanted to protect from all possible failures from empty fields.  It fails again if there is no name but there is a suffix.  Sometimes we know the person is a doctor so this suffix is used for MD or PHD.  Thank you for trying though.  Learning that Count works like that is a good thing to know elsewhere.

 

$nameFirst & " " &

 

I am pretty sure I read somewhere I think by Comment that using $ in calculations can cause problem if a script uses same name when the calculation evaluates or something like that.  I might be wrong there but I do not think I want to use $ in this calculation.  How was I supposed to set these variables - rename the fields like Eos did?  Also when I take your calculation and convert it back to straight field names it fails in many ways namely that if there is no suite, it puts a space-comma at the end of the line and if no City, it puts a comma before the state.  I appreciate the attempt though.  

 

Expression yes, field no. That's just waste of good RAM. If you find "f" more readable than "FirstName" (I don't), then rename the field.

 

 

When I first began using FM I named my table occurrences using old anchor buoy and the table occurrence names were very long because I had hundreds of them and if I was referring to that TO from a related table then the name was like this:  products_lineitems_invoices_CONTACT::First_Name or similar.  I forget now but it was terrible.  Then it might make sense to do as Eos suggests and shorten them in the Let.  Instead, from suggestion of LaRetta, I dropped AB graph style and long table occurrence names disappeared and life is much simpler.  

 

Also if I used the shortened one-letter naming in Let and when reading other people's calculations who did that, I would have to keep looking up at the single letter to know what they were referencing and I would lose my place in reading and understanding the calculation.  Maybe that is just my inexperience.  My field names used to be First_Name but now I have dropped underscore and switch field names to group by logic, such as nameFirst, nameLast.  Reading them in a calc is easier for me.  I always appreciate your input Comment.

 

It is wonderful that I get all the help I have received but I will stick with the calculation LaRetta taught me here.  I can now easily understand and adjust the calculation depending upon the owner's ever-changing requests because the principle is clear.  What a great forum this is.  Please correct me on anything if I misunderstand or I have made a misinterpretation of any of the calculations.  You all are great to spend your time helping.

[The Shadow]

I don't think that's what's happening. At least not with stored calculations, referencing local fields. I am convinced Filemaker is smart enough to acquire all the values in the list of dependencies once. And it certainly doesn't download them from the server more than once.

 

Incidentally, the same applies to most, if not all Get() functions. Though I too have been guilty of stuffing them into variables just to shorten. say "Get ( CalculationRepetitionNumber )" to "i", the idea that Filemaker somehow "calculates" these each time it encounters them is preposterous.

 

ALL calculations are tokenized internally.  Each function is a single byte.  Fields inside a calculation are stored as something like <Table#,Field#>.  This is why you can open an calculation that has been effected by a field delete and it can only show <Field Missing>.  It would be better to show the field name that used to be there as well, but the calculation didn't store it.

 

And yes, opening a file generally downloads a copy of the file's schema - which includes all table/field names.

[comment]

OMG, I almost missed this: it's the Return of the Shadow! Welcome back! May we hope, permanently (or at least more often than once in a blue moon)?

[The Shadow]

Thanks comment - I got distracted with other things - I'll try to pop in more regularly.