cancel
Showing results for 
Search instead for 
Did you mean: 

FMS to get Chapter of an item in A/R Invoice

Former Member
0 Kudos

Hi all.

I am a junior B1 consultant.......I need a FMS query to pick the chapter of an item(from oitm,ochp) in the A/R Invoice INV1 table when the item no ,description and the remaining details are filled...Kindly help me in getting that...

Regards,

Shyamsundar.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi shafi,

Thanks man......I got it...I know the query but i was searching for that dynamic number...like $[$8.1.0]...But what u said was simple..and neat.............

Former Member
0 Kudos

Hi ShyamSundar,

Try This....



SELECT T1.[ChapterID] FROM OITM T0  INNER JOIN OCHP T1 ON T0.ChapterID = T1.AbsEntry WHERE T0.[ItemCode] =$[inv1.ItemCode]

Thanks

Shafi

Johan_H
Active Contributor
0 Kudos

Hi Shafi,

Why don't you just do all his work for him, that way he won't ever have to learn this stuff ?

Thanks,

Johan

Johan_H
Active Contributor
0 Kudos

That type of FMS is quite easy. Here is the basic idea:

SELECT T0.[TheFieldYouWantToReturn] 
FROM [TheTableContainingTheFieldYouWantToReturn] T0 
WHERE T0.[KeyField] = $[TheTableTheFMSWillBeIn.CorrespondingKeyField]

For your requirement you will have to make the FROM clause a little more complicated as your key field can not be found directly from OCHP. So you will need to use OITM and use INNER JOIN to be able to return the chapter value based on the key field (ItemCode)

Good luck,

Johan

Edited by: Johan Hakkesteegt on Jun 16, 2011 8:02 AM