web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / MR 2012 not filtering ...
Microsoft Dynamics GP (Archived)

MR 2012 not filtering on blanks

(0) ShareShare
ReportReport
Posted on by Richard Wheeler 75,848 Moderator

I recently came across a GP installation where the segment structure is defined as 3-2-4-2-2. When creating row definitions filtering on the first segment I noticed the reports always brought back no data. I can filter on all the other segments just fine. When I looked at the accounts I discovered that even though it is defined with a  width of three there is only one character in all of them. I tried to use wildcards but that did not help either. Does MR have a problem with blanks when filtering?

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    Hi Rich,

    This could be quite a problem since I believe that MR doesn't query with 'like' statements but '='... thus it could well not return any data if the field is expecting 3 characters and MR finds only 1, but doesn't consider the blanks..

    This remembers me always at Excel when doing MS-Query requests and you then try to compare the data with other sources and Excel can't match them because the GP retrieved data has 'blanks' filling the data field up to the maximum allowed space.. The retrieved data has to be truncated before you could actually 'compare' it.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at

    Thanks Beat. I am going to move this data into a test environment. Our goal was to run the FRx and MR reports side by side but this issue is in the way. I tried to shrink the width but then FRx stops working. This is because their 5th segment never had any values and was dropped by running reconcile on the account format setup. When you try to run FRx now you get index out of range and it crashes. I have a few ideas but I would rather try them in a test environment.

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    Sounds a good plan... definitely not good to break FRx... we too had run FRx for almost a year after we implemented MR2012, just to make sure the numbers were on par both sides. It's hard to leave a system in which people had put trust so many years.

    About shrinking the segment length, don't quite remember, but  wasn't there an option in FRx to reload the Chart of account ?  or would that imply too many changes in the reports ?

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at

    I thought deleting the g32 files was that but apparently not so. There is a table called frl_acct_code. Do you know how that table gets populated? If I could empty that table and repopulate it after removing the 5th segment that might do the trick.

  • Verified answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    Rich,

    Now that you mention the table, my memory has some flashes about running some SQL scripts to re-populate that table from your companies..

    Digging into my SW repository where my old FRx 6.7 setup files are stored, I found from the SP12 that there is a ZIP archive containing directions about a script called FRx_67_Server_Based_Index_Scripts_Combined_GP.sql.

    You are supposed to run this against your GP installation and it will read thru the SY01500 table to collect the company's ID and than populate the frl_acct_code table (you don't even have to delete it, it's part of the script).

    It will read out the SY00300 table for the segment definitions and the GL00100 for the account numbers.
    Good luck with that.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at

    Beat, you and I found the same script. I think if I were not deleting the fifth segment this would work. I ran that script and when I launched FRx and attempted to access the company I would get a subscript index value out of range messsage and in this table I did not see any changes. I can see what is in there and it was wrong. In my test world I will run reconcile again and then delete what is in that table. Hopefully it will get populated with the correct values. Thanks for your input.

  • Trenton Profile Picture
    Trenton on at

    Running the SBI scripts and re-building the G32 files should resolve this issue as long as values greater than the length do not exit in GP.

    For the MR issue, try creating a report that uses Dimension=[ ], Dimension=[*] to see if the data shows.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at

    Good advice. We recently inherited this client and when I asked why their GL was set up this way I was given the usual 'It always has been this way'. So for the past 15 years this is the way it has been. No one uses the 5th segment and no one ever has used it and they just got used to the extra spaces on the first segment. My goal is to load this onto a test image and try to get both FRx and MR successfully reporting off the same data.

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    Rich,

    If the customer wants to get rid of one segment,  you could use the PSTL to rename or merge some of the accounts...

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at

    Beat, the accounts are already unique and this fifth segment is completely unused. Reconciling Account Format Setup very nicely removed this unused segment.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans