Join two tables in a Smartform

[Former Member]

Hi Gurus,

Anyone please tell me how to extract data from 2 inter-related tables and display it in a smartform.

(e.g. custno. from kunnr and his corresponding orders from vbak)

It will do a great help for me.

Thanks and Regards,

Raj.

[Former Member]

Hi there,

Check out the following program it might give you an idea

PROGRAM FOR PRINTING ORDER DETAILS FOR A RANGE OF CUSTOMERS Z7FORM2

REPORT Z7FORM2.

TABLES : VBAK, KNA1.

DATA : BEGIN OF ITAB2 OCCURS 1,

KUNNR TYPE KNA1-KUNNR,

NAME1 TYPE KNA1-NAME1,

VBELN TYPE VBAK-VBELN,

ERDAT TYPE VBAK-ERDAT,

NETWR TYPE VBAK-NETWR,

END OF ITAB2.

DATA W_ITAB2 LIKE ITAB2.

SELECT-OPTIONS : CUSTNO FOR KNA1-KUNNR.

SELECT K_{KUNNR K}NAME1 V_{VBELN V}ERDAT V~NETWR

INTO TABLE ITAB2

FROM KNA1 AS K

INNER JOIN

VBAK AS V

ON K_{KUNNR = V}KUNNR

WHERE K~KUNNR IN CUSTNO.

SORT ITAB2 BY KUNNR.

CALL FUNCTION 'OPEN_FORM'

EXPORTING

FORM = 'Z7FORM1'

LANGUAGE = SY-LANGU.

CALL FUNCTION 'WRITE_FORM'

EXPORTING

ELEMENT = 'HEADING'

FUNCTION = 'SET'

TYPE = 'TOP'

WINDOW = 'MAIN'.

LOOP AT ITAB2.

ON CHANGE OF ITAB2-KUNNR.

CALL FUNCTION 'CONTROL_FORM'

EXPORTING

COMMAND = 'NEW-PAGE'.

ENDON.

CALL FUNCTION 'WRITE_FORM'

EXPORTING

ELEMENT = 'BODY'

FUNCTION = 'SET'

TYPE = 'BODY'

WINDOW = 'MAIN'.

SUM.

ENDLOOP.

CALL FUNCTION 'WRITE_FORM'

EXPORTING

ELEMENT = 'SUM'

TYPE = 'BOTTOM'

WINDOW = 'MAIN'.

CALL FUNCTION 'CLOSE_FORM'.

and in the main window when you click on create element,enter following:

/E Heading

Customer no &itab2-kunnr&

Customer name &itab2-name1&

Order no Order date Net amount

/E Body

&itab2-vbeln& &itab2-erdat& & itab2-netwr&

/E sum

&itab2-netwr&

/E PAGE

new-page

[Former Member]

Hi,

In the print program write the logic for joining two tables using inner join and fetch the data into a single internal table and pass this table in the function module to call the smartform.

bye.

[Former Member]

Hi,

yes you can do.

write join condition for those two tables and declare one internal table with required fields .

retrieve data to internal table based on kunnr field from kna1 and vbak.

fill it in form interface

in tables option.

it will work fine.

reward points.