FMS with column identification is not working

[former_member557244]

Sir while using SAP B1 Sales Order, on header level, I am trying to call CardName with FMS, so I used these codes

SELECT distinct T0."CardName" FROM OCRD T0 INNER JOIN ORDR T1 ON T0."CardCode" = T1."CardCode" WHERE T1."CardCode" =$[ocrd."CardCode"]<br>

The codes work fine no issue but when I use below codes then codes do not work

SELECT distinct T0."CardName" FROM OCRD T0 INNER JOIN ORDR T1 ON T0."CardCode" = T1."CardCode" WHERE T1."CardCode" =$[$139.4]<br>

I used correct column identification $[$139.4] like shown in image

Please help

[Johan_H]

Hi,

• The FMS parameter syntax gets a value from the form, not from the table.
• The named parameter syntax: $[table.field] is specific to the form's table. I.e. $[ORDR.CardCode] will only work on the Sales Order form.
• The index parameter syntax: $[$field index.column index] is specific to the field only. It can be used on multiple tables. When the required field is on the form header, you do not need the column index, and can just put 0. I.e. $[$4.0] will get the CardCode on all document forms.

Your first query is unnecessarily complicated. This will suffice:

SELECT T0."CardName" FROM OCRD T0 WHERE T0.CardCode = $[ORDR.CardCode]

Your second query does not work because you have used the table index instead of the field index. When you look at the system information, you only need the item number:

The syntax would be $[$4.0] to get the CardCode. This query will work on all document forms (sales order, delivery notes, invoices, etc.):

SELECT T0."CardName" FROM OCRD T0 WHERE T0."CardCode" = $[$4.0]

Regards,

Johan