Placeholder

Customer Forum

Configuring a Dispatch Address using a Dynamic Picklist

Workbooks Support Posted: 2012-05-16 14:24

Out-of-the-box Workbooks doesn't have a Dispatch Address field for Order etc. You can add a Dynamic Picklist field which allows you to choose a Person and using that Person, fields to build a separate address field.

The Dynamic Picklist refers to a report; that report should have an 'Address' calculated column defined like this:

 

REPLACE(
  CONCAT(
    CONCAT(person_first_name, ' ', person_last_name,','), 
    IF(employer_name IS NULL OR employer_name = '', '', 
      CONCAT(employer_name, ',')),
    IF(main_location.street_address IS NULL OR 
       main_location.street_address = '', '', 
      REPLACE(CONCAT(
        REPLACE(REPLACE(main_location.street_address,'
',','),', ', ','), 
        ','), ',,', ',')
    ),
    IF(main_location.town IS NULL OR 
       main_location.town = '', '', 
       CONCAT(main_location.town, ',')),
    IF(main_location.county_province_state IS NULL OR 
       main_location.county_province_state = '', '', 
       CONCAT(main_location.county_province_state, ',')),
    IF(main_location.postcode IS NULL OR 
       main_location.postcode = '', '', 
       CONCAT(main_location.postcode, ',')),
    IF(main_location.country IS NULL OR 
       main_location.country = '', '', 
       CONCAT(main_location.country, ','))
  ),',','
')

Having defined this you now have a field which will be populated with the combination of whichever fields were populated in the Person record, separated by newlines and without extraneous commas.