RW and Account Lists

You can run mx210 to get the listing where object ype is ACCNT, Put in the list type as automatic or manual and also the list name and you get a listing of the accounts used.

Do you know what table this information is stored in within Lawson. I am thinking I need to join from the MXLISTMBR table to GLCHARTDTL via GLCHARTSUM but I do not seem to be getting all the accounts???

You can use the RWGRPDET to connect to GLSYSTEM or the RWHEADER. the account list name and details accounts are stored in the RWGRPDET. You can then connect the GLSYSTEM to the GLCHARTDTL.

hth

I still can not see the structure exactly. I have a record in RWROWDET that has an ACCOUNT_LIST with value ACCT TYPE 4. RWROWDET has a HEAD_NAME field but that does not seem to relate to the HEAD_NAME field in RWGRPDET. Can you help me understand how these tables join together?

Thanks,
Julia

read the Lawson documentation on the tables and on RW100. It will give you joins and definitions.

I started with the documentation and still do not see the links between the RWROWDET that tells me the account list is ACCOUNT TYPE 4 with from matrix value of 4 and a through matrix value of 4.  I do not see any links (that work in our environment) to get from the matrix definition to the list of accounts that are in this list.  Any ideas would be great - I am reading all documentation I can find but nothing has helped as yet.

Julia Hennelly

Select
rrd.head_name,
rrd.line_nbr,
rrd.account_desc,
rrd.account_list,
gdt.account,
gdt.sub_account
From rwrowdet rrd
left outer join mxlistmbr mlm
on 'ACCNT' = mlm.obj_type
and rrd.account_list = mlm.matrix_list
left outer glchartdtl gdt
on mlm.obj_id = gdt.obj_id
where rrd.head_name = 'YOURNAME'
order by rrd.head_name, rrd.line_nbr

And of course, YSMV (your syntax may vary)
Adam

Oops - please not I left "join" between left outer - and glchartdtl

Try this.

Select

rrd.head_name,

rrd.line_nbr,

rrd.account_desc,

rrd.account_list,

gdt.account,

gdt.sub_account

From rwrowdet rrd

left outer join mxlistmbr mlm

on 'ACCNT' = mlm.obj_type

and rrd.account_list = mlm.matrix_list

left outer glchartdtl gdt

on mlm.obj_id = gdt.obj_id

where rrd.head_name = 'YOURNAME'

order by rrd.head_name, rrd.line_nbr





Adam Jacobson

Red Three Consulting, Inc. - The reports you need from the software you already have.

255 West 36th Street - Suite 1006 - New York, NY 10018

Voice: 646-736-0060 x101

Cell: 917-848-7284

Fax: 646-365-3262

adam@redthree.com

www.redthree.com

LinkedIn <http://www.linkedin.com/in/ajredthree>





From: forums-epm-lbi@lawsonguru.com [mailto:forums-epm-lbi@lawsonguru.com]
Sent: Thursday, July 30, 2009 1:45 PM
To: Adam Jacobson
Subject: RE: RW and Account Lists [00006346:00008335]




<https://www.lawsonguru.com/>
Lawson Business Intelligence/Reporting/Crystal Forum Notification
A message was posted to a thread you were tracking.

Julia Posted:07/30/2009 01:34 PM

Subject: RE: RW and Account Lists

I started with the documentation and still do not see the links between the RWROWDET that tells me the account list is ACCOUNT TYPE 4 with from matrix value of 4 and a through matrix value of 4. I do not see any links (that work in our environment) to get from the matrix definition to the list of accounts that are in this list. Any ideas would be great - I am reading all documentation I can find but nothing has helped as yet.

Julia Hennelly

________________________________


To view the complete thread and reply via your browser, please visit:
https://www.lawsonguru.co...fc/8335/Default.aspx

You were sent this email because you opted to receive email notifications when someone posted and/or responded to a message on this forum.
To unsubscribe to this thread please visit your user profile page and change your subscription options.

Thank you,
LawsonGuru.com

Hi,

I just joined lawsonguru. I always struggled with getting this information from RW. Now I am empowered. Thank you big time for sharing your knowledge!

This is exactly the syntax I have been using although I have to go through GLCHARTSUM as when I link to GLCHARTDTL on obj_id I do not get records.  I also have now discovered that we have a hierarchy of accounts and so need to add a hierarchical query to get all accounts.  Once I have the SQL together I shall share in case any one else has a similar type environment.  SO my initial query is as follows to get the sumry_acct_id and depth.  I then need to add the hierarchical query to make sure I get all accounts.

Select rrd.head_name, rrd.line_nbr, rrd.account_desc, rrd.account_list, gdt.sumry_acct_id, gdt.depth
From LAWSON.rwrowdet rrd left outer join LAWSON.mxlistmbr mlm
on 'ACCNT' = mlm.obj_type and rrd.account_list = mlm.matrix_list
JOIN LAWSON.glchartsum gdt on mlm.obj_id = gdt.obj_id
where rrd.head_name = 'EXPSTRUCTURE'
order by rrd.head_name, rrd.line_nbr Thanks for the help.

Julia

Account lists are confusing because they can be composed of summary accounts or detailed accounts.
so, that's why someone would need to go through glchartsum.