Grooper 21.00.0082 is available as of 12-12-2023! Check the  Downloads Discussion  for the release notes and to get the latest version.
Grooper 23.1.0016 is available as of 03-15-2024! Check the  Downloads Discussion  for the release notes and to get the latest version.
Grooper 23.00.0042 is available as of 03-22-2024! Check the Downloads Discussion for the release notes and to get the latest version.

Data Validation: SQL Query with Grooper Table

kylesouzakylesouza Posts: 156 ✭✭✭
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?
Kyle Souza
Data Wizard
P&P Oil & Gas Solutions

Best Answer

  • kylesouzakylesouza Posts: 156 ✭✭✭
    Answer ✓
    Okay, I figured out the solution: I need to add the lookup to the Grooper Table's Behavior properties, not the Data Model's.


    Kyle Souza
    Data Wizard
    P&P Oil & Gas Solutions

Answers

  • strotellistrotelli Posts: 42 admin
    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
  • kylesouzakylesouza Posts: 156 ✭✭✭
    edited November 2020
    @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

    Kyle Souza
    Data Wizard
    P&P Oil & Gas Solutions
Sign In or Register to comment.