The next version of Grooper - Grooper 2021 - will be entering beta soon! If you want to get a head start on some of our exciting new features, check out the  article over Smart PDFs  on the Grooper Wiki!

Data Validation: SQL Query with Grooper Table

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: 146 ✭✭
    Accepted 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: 40 mod
    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: 146 ✭✭
    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.