Create a new calculation field on the account table that gets the info from the address fields and displays it in a concise form in one field that I can use whenever I need to display the address?
That's a good strategy – the same that is used when e.g. combining several name fields into a calculated full name field: do it once in the native table, then use that field wherever you refer to the table.
Don't forget there's a wrinkle here: in the case of an address, it may be a good idea to copy the data over to a shipping record, rather then reference it. (As you would with e.g. a product price, a VAT rate, or other data where you want to store a snapshot in time.)
erolst, good advice about grabbing a snapshot of the current address, instead of referencing the address.
I would only add that if there is the possibility of needing to search on this concatenated field, or if the developer really wanted to make sure the system was as fast as possible, an updating auto-enter calc for the address may be best. I suppose it's personal preference, but any time I create fields like this, I make them updating auto-enters, rather than straight calc fields.
By refer I meant display the related field, not a reference within a calculation field.
Except in certain scenarios (GetSummary() comes to mind) such calc fields simply aren't necessary, and for a search you can search in the original related, indexable field.
Ok so if I wanted to do a "complete address" field on the original account table to display on an order field....it should be an updating auto-enter field. Correct?
Also, can you get me started with the calculation I would need. I am just learning calculations.
I think is would start with a Let function. Is that right?
Here is one possibility for a concatenation which generates an address label:
theLabel = address_Street & "¶" & address_City & "¶" & address_State & " " & address_Zip & "¶" & address_Country
I like to use List ( ) when concatenating address fields, because if there is a line missing in the address fields it won't leave an empty line.
ditto. List() will only show non-blank values, so I use it, too.
; City & " " & State_Prov & " " PostalCode
So, if Company is empty, the "label" slides up.
Yep! Learn something every day. Makes better sense than my original version which includes If() conditions to cater for empty fields. Thanks you two.