Skip to content
+1-888-319-3663

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

1 Comment

  1. Alison on September 3, 2020 at 5:51 am

    Are these calculated columns available in Smart View?

Leave a Comment





RECENT POSTS


2021 Q4 Promotions
Connect with us at Directions EMEA 2021 in Milan!
eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release

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 partners Popdock release Reporting 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.