cancel
Showing results for 
Search instead for 
Did you mean: 

Formatted Search on form designed using Screen Painter

Former Member
0 Kudos

Hi,

I have a form with several text boxes and would like the formatted searches assigned to them, to work dynamically, based on the contents of the preceding text box.

My form is 2000060017 and the item is ITM2. Can somebody explain why the following syntax is incorrect/not working?

SELECT U_Bin, U_OnHand FROM [@BLOITW] WHERE U_ItemCode = $[2000060017.ITM2]

Also, is there any issue with using the selected value from a combo (ITM17) on this same form?

Thanks for any help,

Robin

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

The form ID has no place in the sql syntax. If referencing by item the syntax is $[$ItemUID.ColUID.X] where X is either (0 for text), NUMBER or DATE. Try something like this:-

[code]SELECT U_Bin, U_OnHand FROM [@BLOITW] WHERE U_ItemCode = $[$ITM2.0.0][/code]

NB. A good way to debug it to see if the substitution is taking place is to open up your form, enter the data into your first field, and then run your query from Tools/Queries/User Queries - this way it shows the SQL statement that is actually being run after any substitution has taken place. If it still shows $[$...] then you know you have the wrong syntax for the variable. If it shows the value from your form and still doesn't work, then it is something else in your query that is wrong.

Hope it helps,

John.

Former Member
0 Kudos

Hi,

Thanks to all for the responses.

That works great John...saved me alot of hassle.

Cheers,

Robin

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi

try this

SELECT U_Bin, U_OnHand FROM [@BLOITW] WHERE U_ItemCode = $[$yourtablename.yourfieldname]

example if your itm2 corresponds to a field U_name in the table OINV

SELECT U_Bin, U_OnHand FROM [@BLOITW] WHERE U_ItemCode = $[$oinv.U_name]

is this or something like this

Kind regards

Salvador Biot

Former Member
0 Kudos

Hi,

try with this:

SELECT U_Bin, U_OnHand FROM [@BLOITW] WHERE U_ItemCode = $[$2000060017.ITM2.0]

this is the right FS with form field reference (the sentence $[table.field] refers to database table and field related to the form field)

The problem with the combobox is that with $[$2000060017.ITM17.0] you have the "sometable.code" and you want to find "sometable.name", so you can make something like:

SELECT name FROM sometable WHERE code = $[$2000060017.ITM17.0]

Hope this helps,

Kind Regards,

Fabio Salucci

Former Member
0 Kudos

Hi Fabio,

Thanks for your reply. I tried the following syntax...

SELECT U_Bin, U_OnHand FROM [@BLOITW] WHERE U_ItemCode = $[$2000060017.ITM2.0]

but I am getting error (3006).

Robin

Former Member
0 Kudos

Hi,

this is my last chance:

SELECT U_Bin, U_OnHand FROM [dbo].[@BLOITW] WHERE U_ItemCode = $[$2000060017.ITM2.0]

another possibility is:

SELECT T0.U_Bin, T0.U_OnHand FROM [dbo].[@BLOITW] T0 WHERE T0.U_ItemCode = $[$2000060017.ITM2.0]

Hope this works...

Kind Regards,

Fabio Salucci

Former Member
0 Kudos

Hi Fabio,

Thanks for your help...uUnfortunately, this doesn't work.

Does anyone else have a solution for this? - under real pressure!

Thanks for any help,

Robin