Code Placement

[at_rcc]

Hi all , i have a form invoice which have the invoice (tra_number) field , i have a table called customer that contains customer_id, and i want when the invoice form load , depending on the tra_number being displayed on the form ,it goes to customer database and select the fields i need and display them

customer( customer_id ,cus_mobile,cus_phone)
transaction( transaction_id, tra_number,tra_customer_id)

i want when the form load, it automatically detects the current tra_number being displayed in a display component and then run the sql " select cus _phone,cus_mobile from customer inner join transaction on transaction.tra_customer_id = customer.customer_id where tra_number= " code being displayed in the component"


my problem is how to get the current id from the display on the current form and the use that value in an sql to be triggered in another display on the same form to display the sql results

i am including a screen shot

[admin]

at_rcc,

If I understand you correctly, the answer to your problem is simpler than you think.

The answer is to use a display object for the fields you want from the customer table (like phone number).

forum1.png

The sql would look like this.. (#id# is the primary key for this invoice form)

Code: Select all

SELECT cus_phone FROM customer 
INNER JOIN transaction ON customer_id = tra_customer_id 
WHERE transaction_id = '#id#'

and you would put it here..

forum2.png

Now this will work for any invoice now being viewed once it has already been saved.

But you can have this field automatically change this display object when choosing the customer from a lookup.

You do this by adding a line to the subform on the lookup tab of the customer lookup object..

forum3.png

(cus_phone would need to be available from FMCustomer)

I hope this helps.

Steven