Problems with SmartLists for Account Transactions after GP2013 Upgrade

(0) Share

Report

Posted on by Kimberley Stevens

720

I have a client who is experiencing two issues after upgrading to GP2013. Both issues occur with the non-edited Account Transactions SmartList as follows:

1. Clicking on a transaction now takes you to a blank transaction entry window rather than taking you to the greyed-out transaction itself. You can key the relevant journal entry number into that screen for unposted transactions such as recurring transactions. However, you receive the message, "Journal Entry xxx has already been posted" if the transaction has already been posted. In other words, you get the same behavior as if you had simply opened the transaction entry screen and tried to look up a JE number.

2. The boolean "between" only works for an account range for which all accounts have transactions. For example, assume accounts 1270, 1271, 1275, 1276 and 1277 all have transactions, but accounts between them do not. If you search for account number with filter "between" 1270 & 1285, no results are returned. The same is true if you search between 1270 & 1277. If, however, you search between 1270 & 1271 or between 1275 & 1277, you get results.

Can anyone explain or provide guidance on these issues? We do not yet have R2, and I understand that some date corrections were made for this SmartList in R2.

Cheers!

*This post is locked for comments

All responses (14)

Answers (2)

Jerry Mehler 60 on at

Like (0)

Report

RE: Problems with SmartLists for Account Transactions after GP2013 Upgrade

I created a modification to the Account Transactions in Smartlist Builder intentionally and

than my between range failed. Other than filtering on each segment separately (this seems to work but it's limited by the 4 search criteria limitation in Smartlist) is anyone aware of a real solution ?

Jerry Mehler
Suggested answer

Kimberley Stevens 720 on at

Like (0)

Report

RE: Problems with SmartLists for Account Transactions after GP2013 Upgrade

Jeff, you are absolutely right! Thank you! This was our issue as well. I don't know how or when that Smartlist got modified. However, as soon as I removed the modification, the functionality worked as it was supposed to. Out of curiosity, I drilled down on the Account Number field options in the modified report before removing it, and it was actually using the Account Index.
Verified answer

Community Member on at

Like (1)

Report

RE: Problems with SmartLists for Account Transactions after GP2013 Upgrade

Well, my problem is resolved. It seems a user had accidentally modified the standard Account Transactions* smartlist with SLB. I am not sure what was actually wrong with what they had done, but removing the modified Smartlist reverted back to the out-of-the-box version which is now working correctly.
Bill Campbell 12 on at

Like (1)

Report

RE: Problems with SmartLists for Account Transactions after GP2013 Upgrade

Dan, did I miss that or what - sorry about the confusion, I did not make the leap from Item Master to Item Master and the posting Account in that transaction window.

Yes, I now understand and as confused by the response / lack there of / from Microsoft. If the logic you show is correct, then this is going to be a huge problem.

I will watch with you - as I am not a developer - to see how this is resolved. Again, sorry I missed the connection but it truly makes sense now and it is a problem.
Community Member on at

Like (0)

Report

RE: Problems with SmartLists for Account Transactions after GP2013 Upgrade

Work with me...I've been a network/server guy for 15 years, and my former DBA life is resurfacing as I learn to better support GP!

Not sure what you mean by Item Master. It is not the item numbers which are at issue, but the account numbers.

What we are seeing is if we tell it to search on Inventory Account Numbers between 130-5353-00 and 130-9999-00, the SQL which is ran is this:

SELECT TOP 1000

T1.[ITEMNMBR] K1,

T1.[ORD] K2,

T1.[PONUMBER] K3,

T1.PONUMBER,

T1.FSTRCPTDT,

T1.VENDNAME,

T1.ITEMNMBR,

T1.ITEMDESC,

T1.EXTDCOST,

T1.INVINDX,

T1.LOCNCODE

FROM (SELECT *

FROM slbPurchaseOrderLines WITH (NOLOCK)) T1

WHERE T1.FSTRCPTDT BETWEEN '20150101' AND '20150131'

AND T1.INVINDX BETWEEN '391' AND '0'

Problem 1 is that 130-9999-00 has no entry in the Account master table, so it returns 0.

Problem 2 is that it is searching with values that can not return results, since the test is >391 AND < 0. I didn't do that good in math, but I know nothing will match that :)

Problem 3 is that it is searching for index values, not account numbers anyway.

Our solution was to join the query of slbPurchaseOrderLines with the GL00105 table, and then search for segment1=130 and segment2 between 5350 and 9999, or alternately where the account string is between 130-5350-00 and 130-9999-00
Bill Campbell 12 on at

Like (0)

Report

RE: Problems with SmartLists for Account Transactions after GP2013 Upgrade

Dan. You having the same search between issue for the Item Master?

I can concur with Kimberley that there is no Index assigned for an item number so I am not clear how the same error can be happening with this seach.

Can you post more details on this.
Suggested answer

Kimberley Stevens 720 on at

Like (1)

Report

RE: Problems with SmartLists for Account Transactions after GP2013 Upgrade

Hi Dan,

Yes, it's definitely a good idea to make sure you pick valid qualifiers as one step in working around the "broken" functionality.

The fix was actually my suggestion, not theirs. They didn't seem interested enough in helping to offer a fix!

Because the index field generally represents something else and is seldom (if ever) something that you actually see from the front end, my experience is that there is generally at least one other unique field in the table (usually whatever the index field is representing). I mentioned Account or Main Account Segment in the case of account, but in the case of inventory, it might be Item Number. However, you can still follow the same logic: find the other unique field(s) and filter on it/them instead of the current GP defaults (index).

As for Purchasing Line Item, it seems like a slightly different scenario. I'm not sure why it would not be working as I don't believe that there's a master table that assigns the line items different IDs. I'd have to fiddle with it to figure out what's going wrong and the best way to approach it.

Surely they're going to run into this functionality issue themselves eventually, and realize that it needs to be fixed!

Good luck!

Kimberley
Community Member on at

Like (0)

Report

RE: Problems with SmartLists for Account Transactions after GP2013 Upgrade

Thanks for the followup Kimberley - I hope your partner can help :) What you stated is what we just found. Also, if the account doesn't exist, such as using 130-9999-00 as a catchall, it just puts 0 in as the index value, giving you something like "select between 309 and 0", which will always fail.

The fix they suggested is not going to work for the Purchasing Line Item (or any other one where the result does not include the account number as an option). We're going to look at cloning their view to our own for a fix until they "designed as broken" default is corrected.
Suggested answer

Kimberley Stevens 720 on at

Like (1)

Report

RE: Problems with SmartLists for Account Transactions after GP2013 Upgrade

Hi Jeff and Kayla,

Here is the detail of the issue from Connect, which explains why it's happening. Notice in this description that I suggest how to work around it as well.

It would appear that the Account Transactions SmartList was edited in GP 2013 such that Account Number is now pulled based on Account Index. Because the account index is numbered in the order that the accounts are added as opposed to the logical order of the accounts, this is creating undesired results when using the "between", "greater than" and "less than" booleans.

Here is an example:

This is a truncated list of an account range from the GL account master table:

ACTINDX ACTNUMBR_1 ACTNUMBR_2 MNACSGMT

436 1270 015 1270

438 1271 015 1271

439 1272 015 1272

19 1275 015 1275

20 1276 015 1276

21 1277 015 1277

437 1278 015 1278

22 1279 015 1279

Notice, for example, that the account index for account 1278 is not between the account index for account numbers 1275, 1276, 1277 & 1279 because it was added after the initial setup. When you qualify the Account Transactions SmartList by the range of 1275 – 1279, while it does return data, it doesn’t return any data for account 1278 even though there is data in this account. It appears that this is because it is actually looking for the range between 19 & 22, and 437 is not in this range. When you qualify the same SmartList by 1270 – 1279, it appears to think that the beginning of the range is 436 and the end is 22. Since the beginning is after the end, it returns nothing. However, all accounts in the range actually contain data. This is just one example, but I've done fairly extensive testing of the logic that I describe here, and it holds true every time.

It is possible to produce correct results by using Account or Main Account Segment as the qualifier instead of Account Number. However, because Account Number is the column that shows in this SmartList by default, it is the one that users are normally selecting. Users are getting erroneous data, and aren't necessarily even realizing it.

Microsoft closed the with the following comment: "Thank you for your posting, your issue looks to be a support issue and not a suggestion. Please contact your partner or Microsoft support for additional help on this issue. Best regards."

Of course, I am the partner and I'm certain that they are incorrect, but I was extremely busy at the time and didn't have time to push back. I can still click through to the link, and can see that three other people have indicated that they're having the same issue. I'm actually surprised there aren't more. I have seen a few cases where partners have rebutted and either requested that the case be reopened or simply started a new one, but I have not done that to date. My client is still experiencing this issue, but is using the work-around noted above.

The client is using the Historical Journal Entry Lookup window under the Inquiry menu in order to work around item #1 in my original post.

Hopefully this helps!

Kimberley
Community Member on at

Like (0)

Report

RE: Problems with SmartLists for Account Transactions after GP2013 Upgrade

We are seeing a similar issue - on 2013R2.

If I filter on the Inventory Account Number begins with and use, say 130 for the first segment I get back all the transactions for that account. If I then follow Kimberley's finding and limit my between second segment to ones which have transactions for the date range, it works.

HOWEVER, I tried that logic with another account and found it returned accounts whose first segments were all over the place. It is almost making me thing it is de-referencing the account to the account index in GL00105 and using that as the between, which would only work if all your accounts were in sequential order in the account table.

Needless to say, we're also quite interested in a solution, or more correctly, our finance department is :)