EONE BLOG

Tech Tuesday: Creating Calculations in SmartList / SmartView


We get a lot of questions around creating calculations in SmartList and/or SmartView.  While neither of them will allow you to create a calculated field on their own, SmartList Builder can certainly add a calculated field to any list you currently have or create new.

With SmartList Builder, you can modify the default list and add the calculations you need. For example, if we modify the Account Summary list or try to create a new Summary Trial Balance, here are a few examples of calculations we could do.

A basic Net Change field: 
 {Account Summary:Debit Amount} – {Account Summary:Credit Amount}



If for some reason we wanted to calculate a Net Change only for the actual periods, and not the Period 0 as that is the Beginning Balance, we could do something like this:

(CASE WHEN {Account Summary:Period ID} <> 0
THEN {Account Summary:Debit Amount} ELSE 0.00 END) – 
(CASE WHEN {Account Summary:Period ID} <> 0
THEN {Account Summary:Credit Amount} ELSE 0.00 END)



If we wanted to get even fancier and calculate a Period Beginning Balance, we could do something like this that includes some SQL Scripting in it.

CASE WHEN {Account Summary:Period ID} = 0 THEN {Account Summary:Period Balance}  
WHEN {Account Summary:Period ID} > 0 THEN
CASE
WHEN ((select isnull(sum(Perdblnc),0) from GL10110 P1 where
P1.[Actindx] = {Account Summary:Account Index} and
P1.[Year1] = {Account Summary:Year} and P1.[Ledger_ID] = 1 and
P1.[PeriodID] <= {Account Summary:Period ID} – 1) +
(select isnull(sum(Perdblnc),0) from GL10111 P2 where
P2.[Actindx] = {Account Summary:Account Index} and
P2.[Year1] = {Account Summary:Year} and P2.[Ledger_ID] = 1 and
P2.[PeriodID] <= {Account Summary:Period ID} – 1)) is NOT NULL
THEN ((select isnull(sum(Perdblnc),0) from GL10110 P1 where 
P1.[Actindx] = {Account Summary:Account Index} and
P1.[Year1] = {Account Summary:Year} and P1.[Ledger_ID] = 1 and
P1.PeriodID <= {Account Summary:Period ID} – 1) +
(select isnull(sum(Perdblnc),0) from GL10111 P2 where
P2.[Actindx] = {Account Summary:Account Index} and 
P2.[Year1] = {Account Summary:Year} and P2.[Ledger_ID] = 1 and
P2.PeriodID <= {Account Summary:Period ID} – 1))
ELSE 0.00 END
ELSE 0.00 END



So as you can see with our calculations in SmartList Builder, we can add a new column that does a calculation.  It can be a basic add/subtract or can get detailed and do multiple calculations in one calculation. For a complete list of the calculations built into SmartList Builder, take a look at this KnowledgeBase.


If you have anymore questions about creating calculations in SmartList / SmartView, feel free to send us an email at sales@eonesolutions.com

Leave a Comment





RECENT POSTS


Tech Tuesday: Granting Security to Lists in Popdock
SmartConnect Office Hours for Friday, October 18th, 2019
SmartConnect Bootcamp in Atlanta, GA
eOne is now compatible with Microsoft Dynamics GP October 2019 Release
Change in eOne Product Registration Process

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

Business Central CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners Popdock release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com SmartConnect Bootcamp 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.