You can do this, but it'll take a fairly complicated substitute function.
Set field [table::address field; Substitute (table::address field; [", CA"; ":: CA"]; [", PA"; ":: PA"]....)]
(You need a bracketed expression for every state used in one of your addresses)
Substitute ( table::address field; ",", "¶")
to put in your line breaks.
Substitute ( table::address field; "::", "," )
To put back the comma.
You could nest all of this into one massive substitute function, but it seemed clearer to set this up one step at a time.
Well if the commas are always stable then you can use something like this.
Let ( raw = Substitute ( YourAddressField; ", "; ¶ );
Left ( LeftValues ( raw; 3 ); Length ( LeftValues (raw; 3 ) ) -1 ) & ", " & RightValues ( raw; 1 )
Thanks for the ideas.
PhilModJunk - yours is probably best solution for the problem though I hate to put in a substitute for every state plus DC.
mr_vodka - I like the idea but the content of the field we are substituting in is usually but not necessarily static.
etripoli - I have a little experience with regexp but not enough to try to use it right now. I'll take a look at the plug in as a follow up. Thanks.
I had a new thought that might reduce the complexity. Keep in mind that my earlier suggestion will break if there's an extra space or other characther between the comma the state abbreviation or if the state abbreviation isn't all caps.
Is the "state" comma always the third comma?
set field [addressfield; let (x = Position(addressfield; ",", 1, 3); Left(addressfield; x - 1) & "::" & Right(addressfield; x + 1))]
would avoid the 51 bracket expression substitute.
I think the best solution would be to normalize your data: have one record per address, with separate fields for StreetAddress, City, State and ZIP.
Another possible calculation:
L = Length ( place ) ;
rest = Middle ( place ; Position ( place ; "," ; L ; - 1 ) ; L ) ;
address = Substitute ( place ; [ rest ; "" ] ; [ ", " ; ¶ ] )
address & rest
where "place" is the name of your field.
PhilModJunk and Raybaudi
Thanks for the further ideas. I have already implemented the 51 item substitute, but I'll keep these ideas for streamlining down the road.
Thank you for your consideration. I was looking for a solution to the specific problem addressed here.
As I mentioned I have an alternate method to resolve this which is not convenient based on our current structure. That method is what you have suggested here. The source of address content for this field is a related address book table where what you are suggesting is already done. This field is a very special case and can often contain information and formatting beyond the basics I have outlined here.
The source of address content for this field is a related address book table where what you are suggesting is already done.
I am confused: if the source is normalized, why not get the data directly from there? It seems like you might have a redundancy issue here, and "the specific problem addressed here" is only a symptom.
In any case, if you really must process the data as given, there are simpler alternatives - but a lot depends on whether there always will be exactly two addresses. You should also state your exact version, so that we know whether a custom function is a viable suggestion.
I am adjusting the text format and paragraphing, not the content of this field. I do not want to get into a debate about the semantics of our system and its setup as it would take too much time to write and not be productive use of your time and anyone else who happens to read it.
Yes I am really processing the content of this specific field. Yes I am doing it this way for a reason. Yes there is a potential redundancy issue in relation to the master address in the address source table. Yes there are a variety of potential problems with what I am doing from a fundamental design standpoint. Yes this method is exactly what we need and what we want to do.
All of that being said, there may be one to as many as four addresses along with a variety of other pieces of information in the field. This field does not have a static volume of content, but it does have a static format to the addresses and other content entered.
We are using FileMaker Pro 10 and FMSA 10.
<shrug> You don't need to convince me (and anyway you'd have a hard time convincing me that putting a needle in a haystack, then looking for it is a good idea).