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.