EONE BLOG

Popdock: Moving Beyond Basic Calculated Fields


If Your Reporting Software Can Do This

Then It’s Awesome

Else Get A Popdock Subscription

 

Data manipulation is a very important tool when dealing with your data and how you want to see it.  In some cases going beyond the normal calculations can help you gather important data that you actually want to see.  With Popdock’s calculated field you can do more than just add the values of two columns, here’s a few examples of what you can do:

 

Formatting a date field:

 

({Created date}).ToString(“x”)

 

This will convert a date field to a string in the specified format x.  Here is a list of some of the formats available.  Use one of the following letters in quotes to replace the x to see the format displayed:

d: 6/15/2008

D: Sunday, June 15, 2008

f: Sunday, June 15, 2008 9:15 PM

F: Sunday, June 15, 2008 9:15:07 PM

g: 6/15/2008 9:15 PM

G: 6/15/2008 9:15:07 PM

m: June 15

o: 2008-06-15T21:15:07.0000000

R: Sun, 15 Jun 2008 21:15:07 GMT

s: 2008-06-15T21:15:07

t: 9:15 PM

T: 9:15:07 PM

u: 2008-06-15 21:15:07Z

U: Monday, June 16, 2008 4:15:07 AM

y: June, 2008

   

You can also customize how you want to display your date string.

 

Example:  ({Created date}).ToString(“yyyy-MM-dd”)

Calculated Field Type:  String

 

 

 

Find the number of days a date is from the current date:

 

(DateTime.Now – {Created date}).TotalDays

 

Since this uses DateTime.Now, it will always update to the present date minus the Created date and give you the number of days.

 

Example:  (05-25-2019 – 05-15-2019) = 10

 

 

 

Returning a portion of a text string:

 

{Item description}.Contains(“-“) ? {Item description}.Substring(0,{Item description}.IndexOf(“-“) – 1) : {Item description}

 

This will look at a string of text, determine where in that text a dash is and then return to you the text from the beginning of that string to the character right before the dash.

 

Example:  “Popdock – Smartconnect” returns just “Popdock”

 

 

 

If-Then-Else formulas:

 

{Field1} == “Return” ? {Field2} * -1 : {Field2}

 

This is a more complicated calculation where it uses an if-else statement to accomplish a calculation. The way the formula is constructed is comparing a field and whether the compare is true or false to complete a specific calculation. In the above example if {Field1} is equal to “Return” then take {Field2} and multiply it by a -1 and that will be the data it shows for the calculated field. Now if {Field1} does not equal “Return” it performs the calculation that is after the colon which in the above example is to just keep the same value as {Field2}.

 

Example: {SOP type} == “Return” ? {Extended price} * -1 : {Extended price}

Calculated Field Type: Will vary depending on the fields in the calculation.

 
Have additional questions about Popdock? Contact us at support@popdock.com. 

Leave a Comment




RECENT POSTS


SmartConnect Office Hours for Friday, June 21st, 2019
Free Samples (ie Sample Data!) with Popdock
2019 Summer Webinar Schedule
SmartConnect Bootcamp in Raleigh, NC – Don’t Miss It!
Tech Tuesday: Issues to be aware of when creating maps that use text ODBC drivers

POPULAR POSTS


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

CATEGORIES

ARCHIVES

open all | close all

TAGS

CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight ERP Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration integration manager Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners Popdock release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com SmartConnect Office Hours SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.