automatically fill data in a column from one table to another

[Former Member]

Hi experts,

I would like to automatically fill data in a column from one table to another.

I'll explain the situation and I hope you understand what I mean:

When customers order products, they're stored in the table RDR1. The products and their properties are stored in the OITM table, but because these products are in warehouses, there is a table for too, named as OITW. In this table there is this UDF named U_LOCATION, which is the <b>location</b> in the warehouse where a product is stored.

U_LOCATION is what I want to reflect in the RDR1 table. So I made a UDF in the RDR1, named U_MAGLOC.

My goal is: when I insert an order for a specific product, I want to see automatically the warehouse location in the U_MAGLOC field.

I am fairly new in the query business, so I read the Step by Step book about MS SQL Server, looked up all the notes at the SAP portal about queries and tried several SELECT queries myself. The latest one I tried was:

SELECT $[$38.U_LOCATION.0] for browse

That didn't work either... So now I'm quite desperate and any help would be very much appreciated!

[rasmuswulff_jensen · Accepted solution]

Try

[code]SELECT T0.U_LOCATION FROM OITW T0 WHERE T0.ItemCode = $[$38.1.0] AND T0.WhsCode = $[$38.24.0][/code]

That should do the trick

$[$38.1.0] = Item 38, Column 1 = ItemCode

$[$38.24.0] = Item 38, Column 1 = Warehousecode

[Former Member · Accepted solution]

Hi Rasmus,

Thank you very much for your quick answer. This is almost what I want!

The only thing is still that the location doesn't automatically appear in the U_LOCATION field in the RDR1, but it does when you use the Shift+F2.

So do I have to add something like auto-actualise in the formatted search area?

[rasmuswulff_jensen · Accepted solution]

- Go to the field where you have the formatted search and press ALT + SHIFT + F2 to edit your formatted search.

- Check the checkbox "Auto Refresh When Field Changes" (two comboboxes appear)

- In the combo to the right you choose "When Exiting Alterd Column"

- In the larger combo you choose Item No.

- Finally in the two radiobuttons you choose "Display Saved Values"...

The above should autofill your field each time a item is chosen... if any problems with this try changing the radiobutton to the other setting (for some reason needed som times)

[Former Member · Accepted solution]

I already did that but in this version/patchlevel (16) it doesn't work for some reason. I did the same trick on another patchlevel (27) and in the 2007A version and then it does do the trick without any problems.

So again Rasmus, thank you very much for your time/help!

[Former Member · Accepted solution]

PS.

It isn't the patchlevel but this peticular db. When I tried it in the sports-db there was no problem at all.