Data Validation: SQL Query with Grooper Table

kylesouza

I am trying to do a lookup on a value that is in a table in Grooper (Item).  I went with:
SELECT FROM I4L_MasterPriceList * WHERE [Item Number]=@Daily_Invoices_Table.ITEM
but that is resulting in:

when I do a Test Lookup.

And when I test the Data Model it isn't using that Lookup to Validate (at least, not what I want it to do), because I have a doc that should show a validation error and doesn't.
How do I need to write this query?

kylesouza

Okay, I figured out the solution: I need to add the lookup to the Grooper Table's Behavior properties, not the Data Model's.

strotelli

Kyle, select the each column and name it the field value you want tom app it, as shown below. 
SELECT Field2 AS 'field2' FROM [A Table] WHERE Field1 = @field1

kylesouza

@strotelli I am confused. I tried:
SELECT Daily_Invoices_Table AS 'Daily_Invoices_Table' FROM I4L_MasterPriceList * WHERE [Item Number]=@ITEM
and that didn't work (Exception has been thrown by the target of an invocation), so I tried 
SELECT ITEM AS 'ITEM' FROM I4L_MasterPriceList * WHERE [Daily_Invoices_Table]=@Daily_Invoices_Table
and that didn't work either (Sequence contains no elements).
I also tried the following to no avail:
SELECT Item_Number AS 'ITEM' FROM I4L_MasterPriceList * WHERE [Daily_Invoices_Table]=@Daily_Invoices_Table
SELECT ITEM AS 'Item_Number' FROM I4L_MasterPriceList * WHERE [Daily_Invoices_Table]=@Daily_Invoices_Table