SAP Business One Retrieving the Ship-To State for a Sales Order

It was important to one client to be able to do sales reporting by state (which was done using the query tool). In SBO, the address on the marketing documents is one large field and you cannot get the state unless you get the information from the BP (Business Partner) master for scan through the field for specific characters and pull out the state information.

1. Create a UDF (User Defined Field) for the marketing document header.
2. Create a custom query and insert the code below into the query statement area. The query below will take the Ship-To Code selected and go to the Business Partner Master and get the correct Ship-To State. It will also look for a comma on a custom address (where a ship to code is not selected) and pull the state code from the two characters after the ", ":
   Select Case When $[ShipToCode] = ‘ ‘ Then Case When CHARINDEX(‘,’,$[Address2])= 0 Then ‘??’ Else Substring($[Address2], CHARINDEX(‘,’,$[Address2])+1,2) END Else (Select ADR.State From CRD1 ADR Where $[CardCode] = ADR.CardCode and $[ShipToCode] = ADR.Address and ADR.ADRESTYPE = ‘S’)END
3. Now you can tie a query to the UDF (User Defined Field) you created on a Marketing Document. You will have to assign the query on each marketing document you want the query to run on (e.g., Sales Order, Delivery Doc, etc.). Note: This query was assigned to run automatically when the Ship to field changed. That can be assigned on the Search Definition on the field.

Thanks to Ed Monk of SBONotes.com

© 2009, Mark Chinsky. All rights reserved. Formed in 2005. the 90 Minds Consulting Group is a collaboration of 100 Sage partners who independently join together online in a 24 x 7 private communication network to solve difficult issues for their customers. The group is not affiliated with Sage and our collaboration provides for unparalleled member access to early warnings of bugs and other issues which members in turn are encouraged to use to provide an exceptional customer experience.

Mark is a partner at Clients First Business Solutions, LLC. He is based out of the NY/NJ office. Mark has been in ERP sales and service for more than 20 years and at least 15 with Sage

Mark Chinsky

View all posts by Mark Chinsky
Mark's website

« SAP Business One Creating a User Defined Field for Marketing Documents – Basic | Home | SAP Business One Alerts Check for BP Tax Codes »

• 
  
  
  
  
  
  
• Ask a Question on Ask The 90 Minds Group