SmartlIst GL Trial Balance

[Merle Schwickerath]

I'm trying to create a Smartlist GL trial balance, but I can't seem to get
the balance of the GL account. I get the period net amount & I can get the
debits & credits. Anyone know what field (or fields) I need to use?

[Mariano Gomez]

Can't you accomplish the same with the prebuilt Account Summary Smartlist?
Anyways, if you want to replicate the trial balance do the following:

1) Open SLB (fill in the usual fields)
2) Add the Account Current Summary Master table, accept all defaults
3) Highlight the Account Current Summary Master table, click the edit icon
4) Click on Matched Tables, then select the Account Summary History, accept
the defaults and go back to the main SLB window
5) Highlight the Account Index field and click the Blue arrow next to the
Display Name header
6) Click on Account Index - Show Account Number, click Ok
7) Save your SLB and open SmartList, click on Yes to rebuild

Hope this helps,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

[Merle Schwickerath]

I set the SL builder report up & I am only getting the net activity for the
month, not the balance of the account. Any thoughts on this?

[Victoria [MVP]]

GP tables don't actually store the ending balances, just the net changes, so
you would have to calculate the balances to be able to show them in SLB.

For what it's worth, this is a piece of cake in FRx, I do it all the time
for clients that want to export a trial balance to Excel.

--
Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports
http://www.flex-solutions.com/gpreports.html

"Merle Schwickerath" <MerleSch...@discussions.microsoft.com> wrote in
message news:CAFD885C-361C-453B...@microsoft.com...

[Mariano Gomez]

Yes! The trial balance will require you to setup a running total for the
Account Balance at the end of each period, which can only be accomplished via
a SQL view. If you want to minimize the effort of doing this, you can export
to Excel and group by account to obtain a total.

Best regards,

[Merle Schwickerath]

Un-believable! I can't believe that there is not a field that holds the
balance! I guess I just add this to the list of short comings of Micro Soft.

[Mariano Gomez]

I guess the logic is, if you have the beginning balances and the net changes,
you can arrive at the ending balances.

[Merle Schwickerath]

For one of my companies I had over 18,000 records because I had to pull in
the period balances for July thru June. Oh I forgot the beginning balance,
so that is probably another 1500 records just to get a table that I have to
calculate what the ending balance is. Now I would have a trial balance.

Add to this equation, I have 11 companies that I would need to do this for.
Where do I contact MS with a suggestion?

[Victoria [MVP]]

Merle,

Doing this in FRx is completely out of the question?

Here is a link for posting product suggestions:

http://www.microsoft.com/Businesssolutions/Community/Newsgroups/dgbrowser/en-us/wizard.mspx?dg=microsoft.public.greatplains&lang=en&cr=US&type=suggestion

--
Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports
http://www.flex-solutions.com/gpreports.html

"Merle Schwickerath" <MerleSch...@discussions.microsoft.com> wrote in

message news:44DC8E3B-5F50-4C56...@microsoft.com...

[Merle Schwickerath]

My intention of doing this in GP was thru a smartlist, pull year-end balances
of each GL account along with its posting type. If I do a "sumif" on the
posting type I would be able to confirm that the sum of the "profit & loss"
types match the net income for the year and that amount would be the closing
entry amount to retained earnings.

This was a year-end confirmation for me that all posting types were
correctly marked prior to me running the closing procedure.

I got burnt this year-end & had to restore a db

Merle

[Victoria [MVP]]

Merle,

There is no other way to determine that the posting types are correct?
Typically, it's something like, if the natural is between 1000 and 3999, the
posting type should be Balance Sheet....otherwise it should be P&L.
Exceptions will be accounts with a Category of 0, which are unit accounts.

--
Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports
http://www.flex-solutions.com/gpreports.html

"Merle Schwickerath" <MerleSch...@discussions.microsoft.com> wrote in

message news:F0E70FB5-844C-4D1D...@microsoft.com...

[Merle Schwickerath]

And how do you verify that, but by eye balling every account number setup. I
just wanted to run a report & look at a single total number to compare to net
income.

[Victoria [MVP]]

Merle, I agree, eyeballing a long list is not the way to go. I typically
create 2 favorites in SmartList under Accounts that will only give me the
exceptions. So if my Balance Sheet accounts are 1000 through 3999, and P&L
are 4000 through 9999, then I would create the following 2 favorites:

1. Balance Sheet exceptions: Account < 4000 and Posting Type = P&L
2. P&L exceptions: Account > 3999 and Posting Type = Balance Sheet

--
Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports
http://www.flex-solutions.com/gpreports.html

"Merle Schwickerath" <MerleSch...@discussions.microsoft.com> wrote in

message news:D4B13951-0CF9-4DC0...@microsoft.com...

[Mark Polino [MVP] (DynamicAccounting.net)]

Just to echo Victoria, that's exactly what I've done every year after
I got burned once.

Mark

On Aug 5, 1:07 pm, "Victoria [MVP]" <victo...@flex-solutions.com>
wrote:

> Merle, I agree, eyeballing a long list is not the way to go.  I typically
> create 2 favorites in SmartList under Accounts that will only give me the
> exceptions.  So if my Balance Sheet accounts are 1000 through 3999, and P&L
> are 4000 through 9999, then I would create the following 2 favorites:
>
> 1. Balance Sheet exceptions:  Account < 4000 and Posting Type = P&L
> 2. P&L exceptions: Account > 3999 and Posting Type = Balance Sheet
>
> --
> Victoria Yudin
> Dynamics GP MVP
> Flexible Solutions - home of GP Reportshttp://www.flex-solutions.com/gpreports.html
>

> "Merle Schwickerath" <MerleSchwicker...@discussions.microsoft.com> wrote in
> messagenews:D4B13951-0CF9-4DC0...@microsoft.com...

>
> > And how do you verify that, but by eye balling every account number setup.
> > I
> > just wanted to run a report & look at a single total number to compare to
> > net
> > income.
>
> > "Victoria [MVP]" wrote:
>
> >> Merle,
>
> >> There is no other way to determine that the posting types are correct?
> >> Typically, it's something like, if the natural is between 1000 and 3999,
> >> the
> >> posting type should be Balance Sheet....otherwise it should be P&L.
> >> Exceptions will be accounts with a Category of 0, which are unit
> >> accounts.
>
> >> --
> >> Victoria Yudin
> >> Dynamics GP MVP
> >> Flexible Solutions - home of GP Reports
> >>http://www.flex-solutions.com/gpreports.html
>

> >> "Merle Schwickerath" <MerleSchwicker...@discussions.microsoft.com> wrote
> >> in
> >> messagenews:F0E70FB5-844C-4D1D...@microsoft.com...

> >> > My intention of doing this in GP was thru a smartlist, pull year-end
> >> > balances
> >> > of each GL account along with its posting type.  If I do a "sumif" on
> >> > the
> >> > posting type I would be able to confirm that the sum of the "profit &
> >> > loss"
> >> > types match the net income for the year and that amount would be the
> >> > closing
> >> > entry amount to retained earnings.
>
> >> > This was a year-end confirmation for me that all posting types were
> >> > correctly marked prior to me running the closing procedure.
>
> >> > I got burnt this year-end & had to restore a db
>
> >> > Merle
>
> >> > "Victoria [MVP]" wrote:
>
> >> >> Merle,
>
> >> >> Doing this in FRx is completely out of the question?
>
> >> >> Here is a link for posting product suggestions:
>

> >> >>http://www.microsoft.com/Businesssolutions/Community/Newsgroups/dgbro...

>
> >> >> --
> >> >> Victoria Yudin
> >> >> Dynamics GP MVP
> >> >> Flexible Solutions - home of GP Reports
> >> >>http://www.flex-solutions.com/gpreports.html
>

> >> >> "Merle Schwickerath" <MerleSchwicker...@discussions.microsoft.com>
> >> >> wrote
> >> >> in
> >> >> messagenews:44DC8E3B-5F50-4C56...@microsoft.com...

[Mariano Gomez]

Merle,

Mark and Victoria have provided you with a number of good reasons why you
should follow other methods to check your account balances. However, if you
are still pursuing the SmartList option, the following query can be built as
a view in SQL server and used in SLB as a trial balance:

SELECT actindx,
year1,
periodid,
perdblnc,
perdblnc+COALESCE( (SELECT SUM(perdblnc)
FROM gl10110 b
WHERE a.actindx = b.actindx and b.year1 = b.year1
and b.periodid < a.periodid),0)
AS RunningTotal
FROM gl10110 a
ORDER BY a.actindx, a.year1, a.periodid

The only thing I can think of it may not work on consecutive open years
since Period 0 (beginning balance) is missing.

[Patrick [MSFT]]

Actually, the suggestion posting location has changed and I'm not sure where
if anywhere using the link below will give you.

The new tool is "Connect for Microsoft Dynamics". The link is below and
there is a good faq on the site.

https://mbs.microsoft.com/customersource/productsuggestions/productsuggestion.htm

I was looking at the database where this stuff goes today - not much in
there for GP yet. So resubmit your favorites.

patrick
dev support

--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Victoria [MVP]" <vict...@flex-solutions.com> wrote in message
news:eyPYdiw9...@TK2MSFTNGP05.phx.gbl...

[Victoria [MVP]]

Patrick, thanks, I will update my list.

--
Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports
http://www.flex-solutions.com/gpreports.html

"Patrick [MSFT]" <pr...@online.microsft.com> wrote in message
news:eKUmC229...@TK2MSFTNGP04.phx.gbl...