Re: Help with FORMs accessing 2 tables
>
> Hi all,
> I want to create a FORM that can be queried to output a despatch date
> based on two other fields matching between two tables. Two tables created
> as follows:
>
> create table A ( ordnum CHAR(20), linnum SMALLINT );
> create table B ( ordnum CHAR(20), linnum SMALLINT, despdate DATE)>
> The following data is entered into table A:
>
> ordnum linnum
> order1 10
> order1 20
> order1 30
> order2 10
> order3 10
> order3 20
>
> The following data is entered into table B:
>
> ordnum linnum despdate
> order1 10 150796
> order1 20 160796
> order1 30 170796
> order2 10 150796
> order3 10 150796
> order3 20 160796
>
> The form to be created will look something like this:
>
> ordnum [ ]
> linnum [ ]
> despdate
>
> I wish the user to enter an order number, line number or both, that should
> match between tables A and B and output a despatch date from table B. I
> have looked at composites, joins etc...and am getting nowhere.
>
> Any help with constructing the required FORM would be extremely very much
> appreciated.
>
> Cheers, Ian.
>
Hi ,
I hope u can find out the data entered in the form fields
using input clause / construct clause ...
After getting the field buffers prepare a select statement
which should look like this ,
// get_fldbuf / field_touched will be useful to decide
"select A.ordnum,A.linnum,B.despdate from A,B
where A.ornum = B.ordnum and A.linnum = B.linnum " ,
if ( $ordnum is not null and $linnum is not null )
" and A.ordnum = $ordnum and A.linnum = $linnum "
else if ( $linnum is null )
" and A.ordnum = $ordnum "
else if ( $ordnum is null )
"and A.linnum = $linnum "
"order by 1,2"
//
I hope it helps ....
Bye.
Peter.
\\|||/
Keep in touch ( O-O )
*-----------------*-----------*--------.ooo0--(_)-0ooo.--------*--------*
Residence : Office :
2409 Otb Appartements Siemens Nixdorf
Ottobrunner str 92 Informationsysteme AG
81737 Munich Office Automation Business Unit
Germany Otto-Hahn-Ring 6
81739 Munich
Germany
Ph : 49-89-6800062409/ Ph : 49-89-4144-7114
49-89-6800060 Extn 2409
e-mail : mpeter.muc@sni.de .oooO
( ) Oooo.
*-----------------*-----------*------------\\ (----( )---------*--------*
\\_) ) /
(_/