Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP forum
Suggested answer

How come the Financial ->Multidimensional Analysis Smartlist does not pick up PMTRX Credit Memos?

Posted on by 5

Is there a smartlist that will pick up the MDA coded journal entries with the source document PMTRX and Document Type Credit Memo?

When I run the Financial -> Multidimensional Analysis Smartlist, it does not pick up this Source document PMTRX entry that is Document Type Credit Memo.

The smartlist does pick up other Source document PMTRX entries that are Document Type Invoices. It also picks up Source Document GJ entries.

PayablesTrxEntry_5F00_rebates_5F00_creditMemos.jpg

Categories:
  • Suggested answer
    Hokuminaria Profile Picture
    Hokuminaria 2,950 on at
    RE: How come the Financial ->Multidimensional Analysis Smartlist does not pick up PMTRX Credit Memos?

    Hello Data Dude,

    With the information provided, i did the following and was still not able to get a 1900 date or not have it show on the MDA Smartlist.

    1. Created a 2nd Analysis Group so i have two to distribute against.

      pastedimage1619023031453v1.png

      pastedimage1619023058901v2.png

    2. I assigned both of these in the Analysis Detail window for my Account 000-2100-00 which is the Pay Distribution on a Credit Memo.

      pastedimage1619023122738v3.png

      pastedimage1619023140641v4.png

    3. Posted a new Credit Memo using both of the Analysis codes in the screenshots above.

      pastedimage1619023236157v5.png

    4. Posted the GL Batch created from posting the Credit Memo.

      pastedimage1619023312657v6.png

      pastedimage1619023352030v7.png
    5. Looking at the MDA Smartlist, it shows correctly.

      pastedimage1619023457633v8.png

    6. Looking at the DTA10100 and DTA10200, the data is there and no dates with a 1900.

    pastedimage1619023510039v9.png

    With the above, I would determine if they can recreate this with a new Credit Memo to determine if this credit is just a one off or it continue to happen. If it continues to happen, we will likely want you to create a support case so we can capture logs to dig into the processes in your system further to determine what might be causing the date to come in with 1900. Below are some possible causes that could be causing this.

    1. Third party.
    2. Damaged or missing stored procedures.
    3. Corrupt table. (unlikely)

    If they cannot recreate this on a new manually entered transaction, this could have been caused possibly by.

    1. Interrupted during the posting process which caused the tables to not get updated correctly.
    2. Imported the transaction data which had a 1900 date.
    3. Older version of GP.
      1. I have been testing on 18.3.1200.

    I hope this helps!

    Brandon Jarrett | Microsoft Support Engineer

  • Data Dude Profile Picture
    Data Dude 5 on at
    RE: How come the Financial ->Multidimensional Analysis Smartlist does not pick up PMTRX Credit Memos?

    I am a data person and did not enter this data so I probably used the wrong or misleading finance verbiage. Perhaps if I describe how I got to the scrreenshots below and that will clarify it.  If not, I can get the steps for how the Finance person who input the journal entry for you.

    This is a journal entry for a rebate. To get to the screenshots below, here are my steps:

    1. Journal Entry Inquiry: from Inquiry, financials and enter in the journal entry with a lump group amount
    2. Analysis detail inquiry zoom: selecting the Account 00-6001-004-00 rebates, I chose A (analysis) which gives me the different analysis code ids that group amount gets distributed to

    Thank you for being knowledgeable and methodical in trying to reproduce it.

    PayablesTrxEntry_5F00_rebates_5F00_creditMemos_5F00_analysis.jpg

  • Suggested answer
    Hokuminaria Profile Picture
    Hokuminaria 2,950 on at
    RE: How come the Financial ->Multidimensional Analysis Smartlist does not pick up PMTRX Credit Memos?

    Hello Data Dude,

    Would you be able to clarify more around 'gets distributed to multiple customers in dta10200 so there is no trx date nor docnumber in dta10200'? In Payables, we don't have Customers. In the DTA10100 and DTA10200, we don't hold the Vendor or Customer in this table. 

    I went and tested splitting this to more then one code and it still showed in the smartlist and there are no 1900 dates. I suspect, i am doing something differently then you are when entering this.

    pastedimage1618959531775v3.png

    pastedimage1618959545212v4.png

    There is not alternative smartlist for MDA. However, with Smartlist Designer you can create your own and the joins as you need.

    Below is the view that is used for the MDA Smartlist. You can also get this from SQL under the views folder under the company. You can modify this to have the join you want and then create a new Smartlist off of the new view. 

    support.microsoft.com/.../how-to-create-a-smartlist-designer-report-using-a-sql-view-f8c31ecf-9c63-9590-8ebd-f5bc3f65b4d1

    SELECT [dta10200].[groupid]          AS 'DTA_Group_ID',
           [dta10200].[docnumbr]         AS 'Document Number',
           ( CASE
               WHEN [dta10100].[groupamt] < 0 THEN
               '-'
               + (SELECT CASE
                           WHEN (SELECT cysymplc
                                 FROM   dynamics..mc40200
                                 WHERE
                           curncyid = 'Z-US$') = 0 THEN
                           Rtrim(crncysym)
                           + (SELECT CASE
                                       WHEN (SELECT
                                       inclspac
                                             FROM
                 dynamics..mc40200
                       WHERE
                 curncyid = 'Z-US$') = 1
                 THEN ' '
                 ELSE ''
                 END
                 FROM   dynamics..mc40200
                 WHERE  curncyid = 'Z-US$')
                 ELSE ''
                 END
                  FROM   dynamics..mc40200
                  WHERE  curncyid = 'Z-US$')
               + Ltrim(Str(Abs([dta10100].[groupamt]), 100, (SELECT decplcur-1 FROM
               dynamics..mc40200 WHERE
               curncyid = 'Z-US$')))
               ELSE(SELECT CASE
                             WHEN (SELECT cysymplc
                                   FROM   dynamics..mc40200
                                   WHERE  curncyid = 'Z-US$') = 0 THEN Rtrim(
                             crncysym)
                                                                       +
                             (SELECT CASE
                                       WHEN (SELECT
                                       inclspac
                                             FROM
                   dynamics..mc40200
                         WHERE
                   curncyid = 'Z-US$') = 1
                   THEN ' '
                   ELSE ''
                   END
                   FROM   dynamics..mc40200
                   WHERE  curncyid = 'Z-US$')
                   ELSE ''
                   END
                    FROM   dynamics..mc40200
                    WHERE  curncyid = 'Z-US$')
                   + Ltrim(Str([dta10100].[groupamt], 100, (SELECT decplcur-1 FROM
                   dynamics..mc40200 WHERE curncyid = 'Z-US$')))
                   + (SELECT CASE
                               WHEN (SELECT cysymplc
                                     FROM   dynamics..mc40200
                                     WHERE  curncyid = 'Z-US$') <> 0 THEN (SELECT
                               CASE
                                 WHEN (SELECT inclspac
                                       FROM
                                 dynamics..mc40200
                                       WHERE
                                 curncyid = 'Z-US$') = 1 THEN ' '
                                 ELSE ''
                               END
                                                                           FROM
                               dynamics..mc40200
                                                                           WHERE
                               curncyid = 'Z-US$')
                                                                          + Rtrim(
                               crncysym)
                               ELSE ''
                             END
                      FROM   dynamics..mc40200
                      WHERE  curncyid = 'Z-US$')
             END )                       AS 'DTA_Group_Amount',
           [dta10100].[jrnentry]         AS 'Journal Entry',
           [dta10200].[trxdate]          AS 'TRX Date',
           [dta10200].[codeid]           AS 'DTA_Code_ID',
           ( CASE
               WHEN [dta10200].[dtaqnty] < 0 THEN
               '-'
               + (SELECT CASE
                           WHEN (SELECT cysymplc
                                 FROM   dynamics..mc40200
                                 WHERE  curncyid = 'Z-US$'
                                ) = 0 THEN Rtrim(crncysym)
                                           +
                           (SELECT CASE
                                     WHEN (SELECT
                                     inclspac
                                           FROM
                 dynamics..mc40200
                       WHERE
                 curncyid = 'Z-US$') = 1
                 THEN ' '
                 ELSE ''
                 END
                 FROM   dynamics..mc40200
                 WHERE  curncyid = 'Z-US$')
                 ELSE ''
                 END
                  FROM   dynamics..mc40200
                  WHERE  curncyid = 'Z-US$')
               + Ltrim(Str(Abs([dta10200].[dtaqnty]), 100, (SELECT decplcur-1 FROM
               dynamics..mc40200 WHERE
               curncyid = 'Z-US$')))
               ELSE(SELECT CASE
                             WHEN (SELECT cysymplc
                                   FROM   dynamics..mc40200
                                   WHERE  curncyid = 'Z-US$') = 0 THEN Rtrim(
                             crncysym)
                                                                       +
                             (SELECT CASE
                                       WHEN (SELECT
                                       inclspac
                                             FROM
                   dynamics..mc40200
                         WHERE
                   curncyid = 'Z-US$') = 1
                   THEN ' '
                   ELSE ''
                   END
                   FROM   dynamics..mc40200
                   WHERE  curncyid = 'Z-US$')
                   ELSE ''
                   END
                    FROM   dynamics..mc40200
                    WHERE  curncyid = 'Z-US$')
                   + Ltrim(Str([dta10200].[dtaqnty], 100, (SELECT decplcur-1 FROM
                   dynamics..mc40200 WHERE curncyid = 'Z-US$')))
                   + (SELECT CASE
                               WHEN (SELECT cysymplc
                                     FROM   dynamics..mc40200
                                     WHERE  curncyid = 'Z-US$') <> 0 THEN (SELECT
                               CASE
                                 WHEN (SELECT inclspac
                                       FROM
                                 dynamics..mc40200
                                       WHERE
                                 curncyid = 'Z-US$') = 1 THEN ' '
                                 ELSE ''
                               END
                                                                           FROM
                               dynamics..mc40200
                                                                           WHERE
                               curncyid = 'Z-US$')
                                                                          + Rtrim(
                               crncysym)
                               ELSE ''
                             END
                      FROM   dynamics..mc40200
                      WHERE  curncyid = 'Z-US$')
             END )                       AS 'DTA_Quantity',
           ( CASE
               WHEN [dta10200].[codeamt] < 0 THEN
               '-'
               + (SELECT CASE
                           WHEN (SELECT cysymplc
                                 FROM   dynamics..mc40200
                                 WHERE  curncyid = 'Z-US$'
                                ) = 0 THEN Rtrim(crncysym)
                                           +
                           (SELECT CASE
                                     WHEN (SELECT
                                     inclspac
                                           FROM
                 dynamics..mc40200
                       WHERE
                 curncyid = 'Z-US$') = 1
                 THEN ' '
                 ELSE ''
                 END
                 FROM   dynamics..mc40200
                 WHERE  curncyid = 'Z-US$')
                 ELSE ''
                 END
                  FROM   dynamics..mc40200
                  WHERE  curncyid = 'Z-US$')
               + Ltrim(Str(Abs([dta10200].[codeamt]), 100, (SELECT decplcur-1 FROM
               dynamics..mc40200 WHERE
               curncyid = 'Z-US$')))
               ELSE(SELECT CASE
                             WHEN (SELECT cysymplc
                                   FROM   dynamics..mc40200
                                   WHERE  curncyid = 'Z-US$') = 0 THEN Rtrim(
                             crncysym)
                                                                       +
                             (SELECT CASE
                                       WHEN (SELECT
                                       inclspac
                                             FROM
                   dynamics..mc40200
                         WHERE
                   curncyid = 'Z-US$') = 1
                   THEN ' '
                   ELSE ''
                   END
                   FROM   dynamics..mc40200
                   WHERE  curncyid = 'Z-US$')
                   ELSE ''
                   END
                    FROM   dynamics..mc40200
                    WHERE  curncyid = 'Z-US$')
                   + Ltrim(Str([dta10200].[codeamt], 100, (SELECT decplcur-1 FROM
                   dynamics..mc40200 WHERE curncyid = 'Z-US$')))
                   + (SELECT CASE
                               WHEN (SELECT cysymplc
                                     FROM   dynamics..mc40200
                                     WHERE  curncyid = 'Z-US$') <> 0 THEN (SELECT
                               CASE
                                 WHEN (SELECT inclspac
                                       FROM
                                 dynamics..mc40200
                                       WHERE
                                 curncyid = 'Z-US$') = 1 THEN ' '
                                 ELSE ''
                               END
                                                                           FROM
                               dynamics..mc40200
                                                                           WHERE
                               curncyid = 'Z-US$')
                                                                          + Rtrim(
                               crncysym)
                               ELSE ''
                             END
                      FROM   dynamics..mc40200
                      WHERE  curncyid = 'Z-US$')
             END )                       AS 'DTA_Amount',
           Rtrim([gl00100].actnumbr_1) + '-'
           + Rtrim([gl00100].actnumbr_2) + '-'
           + Rtrim([gl00100].actnumbr_3) AS 'Account Number'
    FROM   two..[dta10200]
           LEFT JOIN two..[dta10100]
                  ON [dta10200].[dtaseries] = [dta10100].[dtaseries]
                     AND [dta10200].[dtaref] = [dta10100].[dtaref]
                     AND [dta10200].[actindx] = [dta10100].[actindx]
                     AND [dta10200].[seqnumbr] = [dta10100].[seqnumbr]
                     AND [dta10200].[groupid] = [dta10100].[groupid]
           LEFT JOIN two..[gl00100]
                  ON [dta10200].[actindx] = [gl00100].[actindx] 

    If you can provide more detail on how you are entering this to create the issue, i can use the same steps and see if i get the same thing as right now, i do not get the same results.

    I hope this helps!

    Thank you!

    Brandon Jarrett | Microsoft Support Engineer.

  • Data Dude Profile Picture
    Data Dude 5 on at
    RE: How come the Financial ->Multidimensional Analysis Smartlist does not pick up PMTRX Credit Memos?

    Figured it out: the trx date is 1900-1-1 in the dta10200 for what I'm looking for and the MDA report only looks at the trxdate in the dta10200

    Even though the trx date of of the jrentry in dta10100 is correct, when the groupamt gets distributed to multiple codeids in dta10200, the trx date is 1900-1-1 in the dta10200

    Is there a smartlist that does the equivalent of a join between dta100 and dta0200?

    If it helps to give context, I don't know the right term but this is one journal entry amount in dta10100 that gets distributed to multiple customers in dta10200 so there is no trx date nor docnumber in dta10200

    1256.PayablesTrxEntry_5F00_rebates_5F00_creditMemos2.jpg

  • Suggested answer
    Hokuminaria Profile Picture
    Hokuminaria 2,950 on at
    RE: How come the Financial ->Multidimensional Analysis Smartlist does not pick up PMTRX Credit Memos?

    Hello Data Dude,

    I went and setup a quick MDA to test a Credit Memo that was created in Payables and posted to the GL to see if it shows on my MDA Smartlist Report.

    In my testing, a Credit Memo will show on the MDA Smartlist. See my testing below.

    1. Created a Credit Memo with MDA info.

      pastedimage1618951443341v1.png

    2. GL entry before posting showing that the MDA is on the JE.

      pastedimage1618951637019v2.png
    3. Checked my Smartlist to see if it shows and it did.

      pastedimage1618951719163v3.png

    As i cannot recreate, i further tested to see what table this would pull from. From my testing, it appears to pull from the DTA10200 as if i delete this record, it no longer shows on the Smartlist.

    I would recommend that you check the DTA10200 table to see if a record exists for that Credit Memo in question. If it does not, this is why it is not showing. 

    select * from DTA10200  where docnumbr = 'Enter Document Number'

    If a record exists in this table for the Credit in questions, i would see if you have Smartlist Builder installed. If so, disable it through the customization Status window. Once disabled, re-open smartlist. You will likely see two folder of each module. The top one is eOne's Smartlist Builder and the second on is GP's Smartlist Designer. Test with the second folder to see if it shows.

    If it does, then the issue is with Smartlist Builder and can post on their forums or create a case with them to look into it further.

    I hope this helps!

    Brandon Jarrett | Microsoft Support Engineer.

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

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,558 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,645 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Product updates

Dynamics 365 release plans