Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: Creating a Summarize List by States Between Two Systems


Popdock – A Tale of Two States


In my recent testing, I ran into a real-world problem with data formatting that you’re probably facing right now. You’re asked to create a list of customer balances by State which must include customer data from two different systems. No problem, you think, take the lists from both systems, and combine them into a single list by creating a Merge list. Then, you can create a Summary list from that Merged list, and group it by the State field. Sounds simple, right?

After looking at the data from the base lists, you notice there’s a big problem with the data in one of the fields. Does anyone see the problem?



I didn’t until I created a merge list using lists from the Sample data connector and the Dynamics GP (Sample data) connector. The Merge list isn’t looking like I expected. Since there are multiple references for each state, grouping by state will not work correctly, and that will lead to errors in the Summary list.




Problem


The problem is that the Sample data list is using the State Names and the Dynamics GP (Sample Data) list is using the State Abbreviations. I need a way to get the two different systems to use one or the other, either the State Names or the State Abbreviations. I cannot go into either system to change how they handle state names vs abbreviations. This is where Popdock has the solution to my problem. Let me get the Popdock toolbox out and grab a tool, Calculated fields.


Solution


I need to do a data transformation on the State data from one connector to match the data in the other connector. I decided I wanted to use State Abbreviations. Since the Sample data/Customer list is using State Names, I need to create a calculated field there to convert from State Name to State Abbreviations. If you want State Names, create the calculated field in the Dynamics GP (Sample Data)/Customer list, then flip the abbreviations and names around in the formula below.

Go to: Connectors >> edit Sample Data >> Lists >> edit Customers >> Fields.

Create a new calculated field, named: ‘State-Abbr’

1. Select
  • Field type: String
  • Field language: Popdock script
2. Click ‘Add function’

3. Select
  • Category: Choice
  • Function: Case
  • Test value: Field and State
Fill out the Compare values / Return values for every State



NOTE: Quotes are required around both the State Name and the State Abbreviation

4. Clicking the ‘+’ to add a new line for the each State

5. Click Save.

SHORTCUT: Instead of adding all 50 states one by one, before inserting the function, copy and paste the formula in the Appendix.


Conclusion


Now that I have both Customer lists returning State Abbreviations, I had to fix the Merge list by updating the ‘State’ Merge fields to use the new calculated field, ‘State-Abbr’.



After that, the Summarized list (create a Summarized list) worked great since it combined Customers from both lists by State Abbreviations!



This example used a string field to perform the data transformation, but calculated fields can be used on all the other data types as well. Hopefully this was helpful and sparked a thought of how you could use a calculated field to transform data so different sets of data can be combined effectively.


Appendix


Code – ‘State-Abbr’ Function (Copy/Paste):

Cases({State}, "Alabama","AL", "Alaska","AK", "Arizona","AZ", "Arkansas","AR", "California","CA", "Colorado","CO", "Connecticut","CT", "Delaware","DE", "Florida","FL", "Georgia","GA", "Hawaii","HI", "Idaho","ID", "Illinois","IL", "Indiana","IN", "Iowa","IA", "Kansas","KS", "Kentucky","KY", "Louisiana","LA", "Maine","ME", "Maryland","MD", "Massachusetts","MA", "Michigan","MI", "Minnesota","MN", "Mississippi","MS", "Missouri","MO", "Montana","MT", "Nebraska","NE", "Nevada","NV", "New Hampshire","NH", "New Jersey","NJ", "New Mexico","NM", "New York","NY", "North Carolina","NC", "North Dakota","ND", "Ohio","OH", "Oklahoma","OK", "Oregon","OR", "Pennsylvania","PA", "Rhode Island","RI", "South Carolina","SC", "South Dakota","SD", "Tennessee","TN", "Texas","TX", "Utah","UT", "Vermont","VT", "Virginia","VA", "Washington","WA", "West Virginia","WV", "Wisconsin","WI", "Wyoming","WY")

We hope this helps you while you’re using Popdock! If you have any more questions, please reach out to our support team at support@eonesolutions.com

Leave a Comment





RECENT POSTS


eOne Sessions at Directions North America 2023
Connect with eOne Solutions at Directions North America 2023!
eOne Sessions at Days of Knowledge UK - Live from Birmingham!
Employee Spotlight: Ally Tronson, Business Development Representative
Meet the eOne Team at Days of Knowledge UK - Birmingham

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.