I am trying to create a report that shows the distributions for our sales invoices but for only Account Category Numbers 31 and 32, and only when both numbers exist. I can get the report to generate with all of the invoices but I can't figure out how to restrict it to say, if 31 has a 32 distribution account category number type then only populate that data. Please help!
*This post is locked for comments
You should be able to do that with some fancy SQL, however, what happens if you have a transaction that has more than one distribution for one of those categories?
I was able to get the data to pull perfectly, so thank you so much for your assistance! One last thing, Is it possible in Smartlist Builder to have the GL account for Act # 31 and then next to that have the GL account for Act #32, side by side, instead of underneath?
Sorry, I misread your post. You will need to link your results to the GL00105 table.
RM10101.DISTINDX = GL00105.ACTINDX
In GL00105 the field that holds the full account number is ACTNUMST.
I am able to return results but the issue that I am now having is that I need to see the actual GL accounts associated, and that information is what I cannot seem to get.
Your code works for me using our production data. A few thoughts:
I have created the view but none of the GL accounts are showing. Can you provide any advise as to what I am doing wrong?
select *
from RM10101
where DISTTYPE = 9 --only invoices
and DISTTYPE in --invoices that have acct with cat 31
(select DISTTYPE
from RM10101
where DISTTYPE = 9
and DSTINDX in
(select DSTINDX from GL00100 where ACCATNUM = 31))
and DISTTYPE in --AND invoices that have acct with cat 32
(select DISTTYPE
from RM10101
where DISTTYPE = 9
and DSTINDX in
(select DSTINDX from GL00100 where ACCATNUM = 32))
I don't know if you're going to be able to do this directly in SmartList Builder. I would recommend creating a SQL view with the logic you need, then using that view in SmartList Builder.
In SQL you can use something like the code below to get a list of all the distributions for invoices that have accounts from both categories:
select *
from SOP10102
where SOPTYPE = 3 --only invoices
and SOPNUMBE in --invoices that have acct with cat 31
(select SOPNUMBE
from SOP10102
where SOPTYPE = 3
and ACTINDX in
(select ACTINDX from GL00100 where ACCATNUM = 31))
and SOPNUMBE in --AND invoices that have acct with cat 32
(select SOPNUMBE
from SOP10102
where SOPTYPE = 3
and ACTINDX in
(select ACTINDX from GL00100 where ACCATNUM = 32))
Any reason you can't publish a data connection for this and then use Excel to build in the logic you need?
What would the code for that be in the calculation? I can get the gl code types to only show 31 and 32, the issue though, is that there are several transactions that use the account type of 31 that do not necessarily also have a posting account type of 32. But that is what I need, If account type 31 also has a posting account type of 32 I need that info only to populate.
create a calculation and choose the transactions where that calculated value is not zero?
Share
Report
All responses (
Answers (
22,768
