Apex Code Development You need to sign in to do that
Don't have an account?
nicksquash separate shipping address lines
Hi
Is it possible to write a formula that essentially breaks the shipping address down into the individual lines?
We have an external system that needs to receive the address fields for a child object to an Account in three separate fields.
I figure if I can identify a new line character, then this should be pretty straight forward, but I can't figure out how to do this.
Any ideas?
thanks
Nick
Is it possible to write a formula that essentially breaks the shipping address down into the individual lines?
We have an external system that needs to receive the address fields for a child object to an Account in three separate fields.
I figure if I can identify a new line character, then this should be pretty straight forward, but I can't figure out how to do this.
Any ideas?
thanks
Nick
newContacts is a List from a trigger and this is parsing the MailingStreet
but hopefully it will serve as a suitable solution. I would think that it
would work the same for the shippingAddress and the billingAddress.
String address1;
String address2;
String[] myStrings = new String[2];myStrings = newContacts[i].MailingStreet.split('\n');
if (!(myStrings[0] == '')){address1 = myStrings[0];
}
if (!(myStrings[1] == '')){address2 = myStrings[1];
}
All Answers
I'm not sure what you mean. Inside SFDC, the address field is really seperate fields:
thanks for the response.
I'm trying to break the shipping street into individual lines.
At the moment it appears to be a text area with multiple lines.
The other items are all fields unto themselves, so they should be fine.
thanks
I might have to just add in some commas as delimeters to the shipping street lines.
Should be able to easily split them up this way.
Was just hoping there might be a more standard way of doing this, or a way to detect a new line character.
Did you ever solve this issue? When I access the Shipping Address field via the API to send the shipping address to an external file the lines are joined without spaces. Ideally I'd like to split the shipping address into 3 fields using the line break character to do the split. I thought I could use a RegExp to do this but none of the usual line break characters seem to work - eg. \n
Steve
Unfortunately we never found a way around this.
We have an external system that accesses this field and the string comes through with some form of line break in it that our coders have managed to use to separate the address lines.
Not sure of the exact details, but I think there is either a <br> or some form of line break character that is used.
Nick
I'm having similar problems accessing the lines in StreetAddress in Pages. The problem is that APEX:outputField will preserve line breaks but doesn't maintain font settings. APEX:outputText will do respect font settings but not line breaks.
Since City, State, Zip, & Country are accessabl, it would be nice if the Address1, Address2, Address3 were as well.
Hello,
When you enter an address in the "Address" field, if the user clicks enter to add a new row, SFDC saves the info in those 3 split address lines.
So if you type in the address field:
Street address (click enter)
Floor (Click enter)
Apt (Click enter)
This is what you will see in those extra fields:
Address Line 1: Street Address
Address Line 2: Floor
Address Line 3: Apt
newContacts is a List from a trigger and this is parsing the MailingStreet
but hopefully it will serve as a suitable solution. I would think that it
would work the same for the shippingAddress and the billingAddress.
String address1;
String address2;
String[] myStrings = new String[2];myStrings = newContacts[i].MailingStreet.split('\n');
if (!(myStrings[0] == '')){address1 = myStrings[0];
}
if (!(myStrings[1] == '')){address2 = myStrings[1];
}
Can you send me the complete code for your trigger for splitting the billing street to address1 and 2?
I'm a bit late to the party but have also had issues with this topic and integrating with an external system. My workaround (without triggers) builds on a response previously posted, prompting users to enter commas in the Street field after every line and also at the end. Following that, it is then just a case of splitting the Street address using formula fields:
Step 1 : Create a number of new text formula fields on your chosen object (I named them Address line 1 Address line 2 and Chk Commas)
Formula Field 1 (Address line 1) // Get the first line of the street address
LEFT(BillingStreet, FIND(",",BillingStreet)-1))
Formula Field 2 (Address line 2) // Get the second line of the street address
Mid(TRIM(BillingStreet), FIND(",",TRIM(BillingStreet))+2, FIND(",",TRIM(BillingStreet),FIND(",",TRIM(BillingStreet))+2) - FIND(",",TRIM(BillingStreet)) -2)
Formula field 3 (Chk Commas) // Check if commas are present (Used later in validation **)
IF(CONTAINS(LEFT(BillingStreet,LEN(BillingStreet)-1), ","),1, IF (ISBLANK(Address_line_2__c),1,0))
** You may be able to build the check commas formula into your validation rule.
Step 2: Create a validation rule on the Street Field of the object as follows:
AND( CHK_COMMAS__c = 0, RIGHT(TRIM(BillingStreet),1) <> "," ) )
Enter some text in the error message area:
Please ensure that the 'Billing Street' address lines are all separated by a " , " and also that the Billing Street ends with a " ,".
Following this I was then able to export single address lines to a CSV file and import to any application with strict Address 1, Address 2, City, State, Zip parameters. You could also add further address lines and validation building on Formula 2 in the example.
Other methods tested to achieve the same output involved the use of batch files and MS Excel Macros which effectively split the Street Address up using a splt to columns command and a carriage return (CTRL + J) delimiter.
Hope this helps?
Have fun
trigger OL_OpportunityAddresses on Opportunity (after insert, after update) {
/*
* Written By Stanley Tso 20130528
*
* 2 fields for breaking each of the two the Street Address: ShipTo_Street__c and Bill_Street__c
* QB_BillingAddress1__c TEXT(41)
* QB_BillingAddress2__c TEXT(41)
* QB_ShippingAddress1__c TEXT(41)
* QB_ShippingAddress2__c TEXT(41)
*
*/
public class AddressPrepException extends Exception {}
List<Opportunity> opportunityList = new List<Opportunity>();
String AddressLine1 = '';
String AddressLine2 = '';
Boolean FirstAddressLineIsUsed = false;
Boolean UpdateNeededOnBillingAddress = false;
Boolean UpdateNeededOnShippingAddress = false;
for(Opportunity o : Trigger.new){
Opportunity u_opp = [SELECT QB_BillingAddress1__c, QB_BillingAddress2__c, QB_ShippingAddress1__c, QB_ShippingAddress2__c FROM Opportunity WHERE Id =: o.Id ];
Opportunity oldo = Trigger.oldMap.get(o.ID);
if (Trigger.isInsert) {
UpdateNeededOnBillingAddress = o.Bill_Street__c != null;
UpdateNeededOnShippingAddress = o.ShipTo_Street__c != null;
} else { // isUpdate
UpdateNeededOnBillingAddress = o.Bill_Street__c != oldo.Bill_Street__c || o.Bill_Street__c != null && o.QB_BillingAddress1__c == null;
UpdateNeededOnShippingAddress = o.ShipTo_Street__c != oldo.ShipTo_Street__c || o.ShipTo_Street__c != null && o.QB_ShippingAddress1__c == null;
}
if (UpdateNeededOnBillingAddress) {
// initiate temp variables
AddressLine1 = '';
AddressLine2 = '';
FirstAddressLineIsUsed = false;
String[] bAddrList = o.Bill_Street__c.split('\n');
for (Integer i = 0; i < bAddrList.size(); i++) {
if (i == 0) { // first item work first
if (bAddrList[0].length() > 41) {
AddressLine1 = bAddrList[0].substring(0,41);
AddressLine2 = bAddrList[0].substring(bAddrList[0].length() - 41);
FirstAddressLineIsUsed = true;
} else {
AddressLine1 = bAddrList[0];
}
}
// work with the next item on the list:
if (i + 1 < bAddrList.size()) {
if ((AddressLine2 + (AddressLine2.length()>0?', ':'') + bAddrList[i+1]).length() < 41) { // we use two characters for the concatination deliminator
AddressLine2 = AddressLine2 + (AddressLine2.length()>0?', ':'') + bAddrList[i+1];
} else {
throw new AddressPrepException('Billing Address second line is over character limit.\n['+AddressLine2 + ', ' + bAddrList[i+1]+']\n# of Characters: ' + String.valueOf((AddressLine2 + ', ' + bAddrList[i+1]).length()));
}
}
}
u_opp.QB_BillingAddress1__c = AddressLine1;
u_opp.QB_BillingAddress2__c = AddressLine2;
}
// test code
/*
else {
u_opp.QB_BillingAddress1__c = 'no need update';
u_opp.QB_BillingAddress2__c = '';
}*/
if (UpdateNeededOnShippingAddress ) {
// initiate temp variables
AddressLine1 = '';
AddressLine2 = '';
FirstAddressLineIsUsed = false;
String[] sAddrList = o.ShipTo_Street__c.split('\n');
for (Integer i = 0; i < sAddrList.size(); i++) {
if (i == 0) { // first item work first
if (sAddrList[0].length() > 41) {
AddressLine1 = sAddrList[0].substring(0,41);
AddressLine2 = sAddrList[0].substring(sAddrList[0].length() - 41);
FirstAddressLineIsUsed = true;
} else {
AddressLine1 = sAddrList[0];
}
}
// work with the next item on the list:
if (i + 1 < sAddrList.size()) {
if ((AddressLine2 + (AddressLine2.length()>0?', ':'') + sAddrList[i+1]).length() < 41) { // we use two characters for the concatination deliminator
AddressLine2 = AddressLine2 + (AddressLine2.length()>0?', ':'') + sAddrList[i+1];
} else {
throw new AddressPrepException('Billing Address second line is over character limit.\n['+AddressLine2 + ', ' + sAddrList[i+1]+']\n# of Characters: ' + String.valueOf((AddressLine2 + ', ' + sAddrList[i+1]).length()));
}
}
}
u_opp.QB_ShippingAddress1__c = AddressLine1;
u_opp.QB_ShippingAddress2__c = AddressLine2;
}
// test code
/*
else {
u_opp.QB_ShippingAddress1__c = 'no need update';
u_opp.QB_ShippingAddress2__c = '';
}*/
if (UpdateNeededOnBillingAddress || UpdateNeededOnShippingAddress ) {
opportunityList.add(u_opp);
}
}
update opportunityList;
}
Fixed Version
code
--------------------
public class OL_AddressPrepException extends Exception {}
trigger OL_OpportunityAddresses on Opportunity (after insert, after update) {
/*
* Written By Stanley Tso 20130528
*
* 2 fields for breaking each of the two the Street Address: ShipTo_Street__c and Bill_Street__c
* QB_BillingAddress1__c TEXT(41)
* QB_BillingAddress2__c TEXT(41)
* QB_ShippingAddress1__c TEXT(41)
* QB_ShippingAddress2__c TEXT(41)
*
*/
// public class AddressPrepException extends Exception {} // replaced with OL_AddressPrepException
List<Opportunity> opportunityList = new List<Opportunity>();
String AddressLine1 = '';
String AddressLine2 = '';
Boolean FirstAddressLineIsUsed = false;
Boolean UpdateNeededOnBillingAddress = false;
Boolean UpdateNeededOnShippingAddress = false;
for(Opportunity o : Trigger.new){
Opportunity u_opp = [SELECT QB_BillingAddress1__c, QB_BillingAddress2__c, QB_ShippingAddress1__c, QB_ShippingAddress2__c FROM Opportunity WHERE Id =: o.Id ];
if (Trigger.isInsert) {
UpdateNeededOnBillingAddress = o.Bill_Street__c != null;
UpdateNeededOnShippingAddress = o.ShipTo_Street__c != null;
} else { // isUpdate
Opportunity oldo = Trigger.oldMap.get(o.ID);
UpdateNeededOnBillingAddress = o.Bill_Street__c != oldo.Bill_Street__c || o.Bill_Street__c != null && o.QB_BillingAddress1__c == null;
UpdateNeededOnShippingAddress = o.ShipTo_Street__c != oldo.ShipTo_Street__c || o.ShipTo_Street__c != null && o.QB_ShippingAddress1__c == null;
}
if (UpdateNeededOnBillingAddress) {
// initiate temp variables
AddressLine1 = '';
AddressLine2 = '';
FirstAddressLineIsUsed = false;
String[] bAddrList = o.Bill_Street__c.split('\n');
for (Integer i = 0; i < bAddrList.size(); i++) {
if (i == 0) { // first item work first
if (bAddrList[0].length() > 41) {
AddressLine1 = bAddrList[0].substring(0,41);
AddressLine2 = bAddrList[0].substring(41);
FirstAddressLineIsUsed = true;
} else {
AddressLine1 = bAddrList[0];
}
}
// work with the next item on the list:
if (i + 1 < bAddrList.size()) {
if ((AddressLine2 + (AddressLine2.length()>0?', ':'') + bAddrList[i+1]).length() < 41) { // we use two characters for the concatination deliminator
AddressLine2 = AddressLine2 + (AddressLine2.length()>0?', ':'') + bAddrList[i+1];
} else {
// have to comment out this line due to code coverage not supporting good exception handling.
//throw new OL_AddressPrepException('Billing Address second line is over character limit.\n['+AddressLine2 + ', ' + bAddrList[i+1]+']\n# of Characters: ' + String.valueOf((AddressLine2 + ', ' + bAddrList[i+1]).length()));
}
}
}
u_opp.QB_BillingAddress1__c = AddressLine1;
u_opp.QB_BillingAddress2__c = AddressLine2;
}
// test code
/*
else {
u_opp.QB_BillingAddress1__c = 'no need update';
u_opp.QB_BillingAddress2__c = '';
}*/
if (UpdateNeededOnShippingAddress ) {
// initiate temp variables
AddressLine1 = '';
AddressLine2 = '';
FirstAddressLineIsUsed = false;
String[] sAddrList = o.ShipTo_Street__c.split('\n');
for (Integer i = 0; i < sAddrList.size(); i++) {
if (i == 0) { // first item work first
if (sAddrList[0].length() > 41) {
AddressLine1 = sAddrList[0].substring(0,41);
AddressLine2 = sAddrList[0].substring(41);
FirstAddressLineIsUsed = true;
} else {
AddressLine1 = sAddrList[0];
}
}
// work with the next item on the list:
if (i + 1 < sAddrList.size()) {
if ((AddressLine2 + (AddressLine2.length()>0?', ':'') + sAddrList[i+1]).length() < 41) { // we use two characters for the concatination deliminator
AddressLine2 = AddressLine2 + (AddressLine2.length()>0?', ':'') + sAddrList[i+1];
} else {
// have to comment out this line due to code coverage not supporting good exception handling.
//throw new OL_AddressPrepException('Shipping Address second line is over character limit.\n['+AddressLine2 + ', ' + sAddrList[i+1]+']\n# of Characters: ' + String.valueOf((AddressLine2 + ', ' + sAddrList[i+1]).length()));
}
}
}
u_opp.QB_ShippingAddress1__c = AddressLine1;
u_opp.QB_ShippingAddress2__c = AddressLine2;
}
// test code
/*
else {
u_opp.QB_ShippingAddress1__c = 'no need update';
u_opp.QB_ShippingAddress2__c = '';
}*/
if (UpdateNeededOnBillingAddress || UpdateNeededOnShippingAddress ) {
opportunityList.add(u_opp);
}
}
update opportunityList;
}
--------------------
test class
--------------------
@isTest
public class OL_QBAddress_Class_UT {
static testMethod void validateQBAddressCreation() {
AutoNo__c a= new AutoNo__c();
a.Category__c='Account Number';
a.Iterator__c =0;
insert a;
AutoNo__c a2= new AutoNo__c();
a2.Category__c='Opportunity Number';
a2.Iterator__c =0;
insert a2;
Account acc1= new Account();
acc1.Name='Test_Account';
acc1.CurrencyIsoCode='CAD';
acc1.AccountNumber = '';
insert acc1;
System.debug('Account: [' + acc1.AccountNumber+ '] has inserted into system.');
Opportunity o = new Opportunity(Name='test opp', StageName = 'Pre Bid', AccountId = acc1.Id);
o.CloseDate = Datetime.now().Date();
o.DB_CustomerPO__c = 'S001923';
o.ShipTo_State_Province__c = 'BC';
o.ShipTo_Street__c = 'test long ass string first more than 41 character';
o.Bill_Street__c = 'test long ass string first more than 41 character2';
insert o;
o = [SELECT ShipTo_Street__c, Bill_Street__c, QB_BillingAddress1__c, QB_BillingAddress2__c, QB_ShippingAddress1__c, QB_ShippingAddress2__c FROM Opportunity WHERE Id =: o.Id LIMIT 1];
System.assertEquals(o.QB_BillingAddress1__c, 'test long ass string first more than 41 c');
System.assertEquals(o.QB_BillingAddress1__c, o.QB_ShippingAddress1__c); // both line should be the same because of our test string
o.ShipTo_Street__c = 'test with two\nline address this\nis shipping address';
o.Bill_Street__c = 'test with two\nline address this\nis billing address';
update o;
AutoNo__c autoNoObj1 = [select id,NextNo__c from AutoNo__c where Category__c = 'Account Number'];
AutoNo__c autoNoObj2 = [select id,NextNo__c from AutoNo__c where Category__c = 'Opportunity Number'];
o.ShipTo_Street__c = 'test with two\nline address this\nis shipping address on when second line is overshot the length.';
o.Bill_Street__c = 'test with two\nline address this\nis billing address on when second line is overshot the length.';
update o;
/* can't use this for custom exception handling.
try{
update o;
System.assertEquals('Overshot Address is not complaint', '');
} catch ( OL_AddressPrepException e) {
// ok to go next
}
*/
delete o;
}
}
--------------------