logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
hmaneiro  
#1 Posted : Tuesday, May 22, 2018 9:19:46 AM(UTC)
hmaneiro

Rank: Newbie

Groups: Registered
Joined: 3/26/2018(UTC)
Posts: 1
Location: Elon, NC

I have a client who as they are starting up in AA have their codes set to optional. They recently have found then lines they have forgotten to allocate. I quickly threw together the following script to check for these items:

select
Year,
[Journal Entry],
[GL Posting Date],
[Transaction Source],
[Account Number],
[Account Description],
[Distribution Reference],
[Dimension1],
[Dimension2],
[Dimension3],
[Dimension4],
[Dimension5],
[Dimension6],
[Dimension7],
[Dimension8],
[Dimension9],
[Dimension10],
[Debit Amount],
[Credit Amount]
from AAOpenTransactions
where (([Dimension1] IS NULL) AND
([Dimension2] IS NULL) AND
([Dimension3] IS NULL) AND
([Dimension4] IS NULL) AND
([Dimension5] IS NULL) AND
([Dimension6] IS NULL) AND
([Dimension7] IS NULL) AND
([Dimension8] IS NULL) AND
([Dimension9] IS NULL) AND
[Dimension10] IS NULL)


AND

[Account Number] > '299999-9999' AND [Account Number] < '900000-0000'

Mission accomplished, but I decided I wanted to create a SmartList reminder. I figured rather than pushing my select to create a new view - since I was using an existing view I thought I could just filter the AAOpenTransactions view in SmartListDesigner for my new object. I knew NULL's were tricky, but thought I should be able to get something to work. I tried Condition Is and Value NULL. No results. I tried Condition Less than Value 0. Still no dice. In short, my team determined NULL's could not be evaluated through the front end. I pushed my select to a view and went from there.

My colleague, Paul, suggested in my new view - I could also set NULL's to blank making evaluation a bit easier through SmartList as such:

select
Year,
[Journal Entry],
[GL Posting Date],
[Transaction Source],
[Account Number],
[Account Description],
[Distribution Reference],
DimensionColumn1 = isnull(DimensionColumn1,'')
[Dimension1] = isnull([Dimension1], '') ,
[Dimension2] = isnull([Dimension2], '') ,
[Dimension3] = isnull([Dimension3], '') ,
[Dimension4] = isnull([Dimension4], '') ,
[Dimension5] = isnull([Dimension5], '') ,
[Dimension6] = isnull([Dimension6], '') ,
[Dimension7] = isnull([Dimension7], '') ,
[Dimension8] = isnull([Dimension8], '') ,
[Dimension9] = isnull([Dimension9], '') ,
[Dimension10] = isnull([Dimension10], '') ,
[Debit Amount],
[Credit Amount]
from AAOpenTransactions

Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.