SQL statment in items placement tools Answered

Patrice Vigier

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

 

 

Comments

2 comments

Date Votes
  • Comment author
    Patrice Vigier

    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.

     

    0
  • Comment author
    Patrice Vigier

    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.

     

    0

Please sign in to leave a comment.