Quering document lines in user define values

[ssierraSBI]

Hello! Im trying to get the "Quantity" field on the document lines in a "User defined values" query. 

SELECT $\[$38.11.0]
FROM DUMMY

I've been trying to use the previous query, and after reading through other questions on the subject I've tried:
Removing the backslash
Leaving only the select line
Changing "$38" for "RDR1"
Changing "0" for "number"
Changing "11" for "Quantity"

None of these have worked for me so far, and it either gives me internal error 3006 or syntax error near "$"

I'd appreciate any help on the subject

Thanks!

[WirkKarl]

Hi! It sounds like you're close but hitting syntax limitations common with user-defined values in SAP B1. When dealing with User-Defined Values (UDVs), the syntax must strictly conform to what SAP's UI API expects. The $syntax works only in formatted search expressions, not in raw SQL queries like your example.

If you're trying to retrieve the "Quantity" from the line in a formatted search, your original $ is actually correct—but only when used in the right context (like linking the field to a formatted search in the UI, not directly running in Query Generator).

If your goal is to populate a field with values dynamically, consider restructuring your business logic. Platforms like Phonexa use structured data flow and custom field mapping to handle this type of dynamic value injection without the strict limitations of SAP’s query tools—might be worth looking at for more scalable automation.

Let me know if you need help crafting the correct formatted search or want an example for RDR1 joins!

[ssierraSBI]

Hi! Thanks for the answer.

I'll start by clarifying that I'm working with SAP B1 in Spanish, so I might get lost on some of the translations for the different functions and all.

With that out of the way, what I'm trying to do is applying a formated search in one of the lateral fields (a User-Defined one) which takes the amount in the Quantity of one specific line of the document and multiplies it by 24. I'm still trying to get the query to return anything, so I still haven't tried to get any filters or modifications on the value going.

I hope my explanation was clear enough. By your comment I understand that the $ syntax should be working properly in this case, but it's giving me internal error -3006

[mgregur]

Hi,

I don't know where you got this syntax, but you cannot use backslash. The correct version would be 

SELECT $[$38.11.0] FROM DUMMY

BR,

Matija

[ssierraSBI]

 Hi Matija, thanks for answering so quickly!

This syntax gives me internal error -3006 when trying to use this query for the user defined values in a lateral field.

[mgregur]

Hi,

I see from your last comment that you are trying to multiply the quantity. That is the reason for your syntax error, as the query I wrote only takes the field value, so it treats it as a string. To multiply with 24 you need this:

SELECT ($[$38.11.Number] * 24) FROM DUMMY

BR,

Matija

[ssierraSBI]

HI Matija,

As I said in my other comment, I wasn't trying to get the value to change yet since I'm still trying to get the query to return any value at all. 
However, my lateral field is of a numeric type so changing the type might help. I'm still getting internal error 3006 with this new query though.

Thanks!

Santiago

[mgregur]

Hi,

try using this FMS in an nvarchar field and see if it populates correctly. If yes, that means your result is coming back in a different type than expected, so most likely you need to cast it as integer or use substitute to change (usually its problems with comma(,) and dot(.) being misinterpreted).

BR,

Matija

[ssierraSBI]

Hi Matija,

I tried using it as it for a NVARCHAR field and it still giving me internal error 3006. 

Thanks again for the help!

Santiago

[mgregur]

Hi,

Just to clarify, are you using this query in the Query manager or manually triggering the FMS with the magnifying glass? Because this works for me (HANA version, FP2502).

BR,

Matija

[ssierraSBI]

Hi Matija,

I'm both using the shortcut Shift + F2 and manually using Tools > Queries > User Queries each time to see if any of those options returns any value.

Regards, 

Santiago

[mgregur]

Hi,

Did you make sure that the form you pull data from is open and the matrix is populated with data before running the query? Does your query window pull data from the matrix? Maybe it would be best if you try it now and post a screenshot when it gives you the error (blank out any sensitive data).

BR,

Matija

[ssierraSBI]

Hi Matija,

Here is a screenshot from the moment I get the error message. I'm trying to get data from the line ALQC-002, and the field is "Suma Asegurada" which has at the moment running the query the value 1000. I'm not sure what you mean by the form being open tho. And the field I'm running the query in is "Monto del Alquiler".

Regards,
Santiago