Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
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

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at
    RE: MR 2012 not filtering on blanks

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

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at
    RE: MR 2012 not filtering on blanks

    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
    RE: MR 2012 not filtering on blanks

    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.

  • Trenton Profile Picture
    Trenton on at
    RE: MR 2012 not filtering on blanks

    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
    RE: MR 2012 not filtering on blanks

    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.

  • Verified answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at
    RE: MR 2012 not filtering on blanks

    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
    RE: MR 2012 not filtering on blanks

    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.

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at
    RE: MR 2012 not filtering on blanks

    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
    RE: MR 2012 not filtering on blanks

    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.

  • Verified answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at
    RE: MR 2012 not filtering on blanks

    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.

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

News and Announcements

Check out the New Agent Creator Community

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Almas Mahfooz Profile Picture

Almas Mahfooz 3 User Group Leader

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans