# 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?

#### RECENT POSTS

Integrations Have Gone Virtual
eOne is hiring! Open positions in sales, support and services
2021 Q3 Promotions
A Once in 20 Years Event
Advanced SmartConnect Bootcamp: REST Web Services Integration - August 2021

#### 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

#### 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.