ACHISTDTL linked to PURCHORDER - LawsonGuru.com

Name	Points
Greg Moeller	4184
David Williams	3349
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1377
Roger French	1315
mark.cook	1244
Chesca	1042

TracyO

Veteran Member

Posts: 97

4/19/2010 3:50 PM

We are trying to create a Crystal report to create more meaningful invoices to our locations. The GL120/121 does not give enough details. I want to be able to somehow tie back to the PO line Descriptions. I am not sure if these are the correct files, but I see the information I need on ACHISTDTL for hte BR side and I have linked the PURCHORDER, POLINE & MMDIST tables to get what I need on that side. I am looking for the bridge to get me from one to the other. Has anyone created a report to try to combine the information. Any ideas suggestions would be greatly apprecaiated.
Thanks
Tracy

John Henley

Posts: 3364

New Poster

Engaged Reader

Avid Reader

4/19/2010 4:47 PM

You would go from ACHISTDTL (HDT) to ACTRANS (ATN) via ATN-OBJ-ID.
Then from ACTRANS (ATN) to MMDIST (MMD) via ATN-OBJ-ID.
Then from MMDIST to POLINE, and POLINE to PURCHORDER.

David Williams

Veteran Member

Posts: 1127

New Poster

4/19/2010 4:51 PM

And somewhere in there you get to Kevin Bacon?

TracyO

Veteran Member

Posts: 97

4/19/2010 4:54 PM

Thanks John!
I am hoping I can build this bridge without wanting to jump off.

TracyO

Veteran Member

Posts: 97

4/21/2010 6:29 PM

Okay my toes on are the edge of the bridge, and I think Kevin Bacon may be ready to push.
I can not seem to get ACTRANS nd MMDIST to link. As far as I can tell ACTRANS does not have a field called ATN-OBJ-ID. I tried to map OBJ-ID (ACTRANS) to ATN-OBJ-ID (MMDIST), I do not get an error when mapping, but my report results in no data. I should have data on both files for the one I am looking at. I have tired all version of the join and still get nothing. Any ideas?

John Henley

Posts: 3364

New Poster

Engaged Reader

Avid Reader

4/21/2010 7:02 PM

No..DON'T JUMP!

Are you using straight SQL? If so:

ACTRANS.OBJ_ID = ACHISTDTL.ATN_OBJ_ID

Then

MMDIST.ATN_OBJ_ID = ACTRANS.OBJ_ID
MMDIST.MMDSET2_SS_SW='Y'

TracyO

Veteran Member

Posts: 97

4/21/2010 7:17 PM

I am a wizard kind of person, but I have seen some things my IT dept has done using the sql statment. I bet with a little hlep from my IT friends I can do this

Thanks John!

TracyO

Veteran Member

Posts: 97

4/23/2010 5:30 PM

Still not linking for me, but at least now I do know why.
The ATN-OBJ-ID on the mmdist table is not being populted for some reason. I will bring this to CUE and see what answers I can get. Thanks for your help.

TracyO

Veteran Member

Posts: 97

5/20/2010 6:41 PM

The tables still do not link. I have looked at the ATN-OBJ-ID field on MMDIST & the OBJ-ID on ACTRANS at everystep of the process. Here is what I found:
MMDIST will not be populated until I have added and released the PO, but ACTRANS does not have any transactions yet. At this step MMDIST has a value of say 123 for line one of the PO and 124 for line two of the PO. After I add the invoice on AP20.1 and release it on AP25.1 the values on MMDIST gets set to zero. THh record still does not show on ACTRANS yet.
I then run AP175, after this has run the record now shows up on ACTRANS with an OBJ-ID of 125
Wiht all that said, you can not link MMDIST & ACTRANS on ATN-OBJ-ID. Is there maybe an issue with some of my set up? When I was at CUE I talked to a BR Lawson person and they had told me that the reason my ATN-OBJ-ID was zero was because I did not have the ACcommit flag set to yes on GL01.4. I did set that flag before I did this testing, but I don't think that really mattered. Any ideas?
Thanks