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.
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.
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.
If they cannot recreate this on a new manually entered transaction, this could have been caused possibly by.
I hope this helps!
Brandon Jarrett | Microsoft Support Engineer
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:
Thank you for being knowledgeable and methodical in trying to reproduce it.
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.
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.
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.
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
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.
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (