SQL statment in items placement tools
Answered
On SAP B1 9.1 Hana
On Items master data form:
I have a user defined field ( a SubGroup) that I want to be linked with the Item group field, so when you click on the drop down field it proposed value that are relevant with Item group.
This is why I put in the valid value of the item placement tool the following SQL Statment:
SQL:SELECT Code, Name FROM @VT_SGRP WHERE U_GRP =$[$39.0.NUMBER]
It does not work!
- Code is the code of the subgroup
Name is the name of the subgroup
@VT_SGRP is my table
U_GRP is the code of the group
$[$39.0.NUMBER] is the reference of the field item group on the item master data form
If instead I put:
SQL:SELECT Code, Name FROM @VT_SGRP WHERE U_GRP = 105
(105 is a item group value) it woks.
So I guess the reference to the field $[$39.0.NUMBER] (item group) is not right. How should I write it?
I also tried this statement:
SQL:SELECT Code, Name FROM @VT_SGRP WHERE U_GRP =OITM.ItmsGrpCod
and many more....
Please help me.
Thank you
-
Maybe that is a bug, because the SQL statement
SELECT Code, Name FROM @VT_SGRP WHERE U_GRP =$[$39.0.NUMBER]
works in a button.
-
I got an answer: it does not work this way.
The solution is:
1) To add a validation configuration that fire on click before action
2) In "No condition, just execute function" field, link it to a macro
3) Create a macro with this function: SetValidValues($[$item.col.0]|SQL:<sql>);
As an example,in my case it was:
SetValidValues($[$BOYX_7.0.0]|SQL:SELECT Code,Name FROM @MYTABLE WHERE U_MYFIELD = $[$39.0.NUMBER]);
This works as I expected.
Please sign in to leave a comment.
Comments
2 comments