Skip to Content
avatar image
Former Member

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jun 16, 2011 at 06:02 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 16, 2011 at 06:13 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 20, 2011 at 06:11 AM

    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.............

    Add comment
    10|10000 characters needed characters exceeded