Spreadsheet Designer error: The range you have specified is invalid

unknown

I am trying to query PO lines with the CNCode field set to "Bill Only".  When I query PurchaseOrderLine I can include that field in my query and I see lines where that field displays in ISD as "Bill Only".  I suspect its actually a numeric field so its really stored as "3" or something like that.  At any rate, if I try to query that field I receive this error:

Error Encountered: The range you have specified is invalid.  The headers must remain in the same row and the resulting table must overlap the original table.  the table must contain a header row, and at least one row of data.  Please specify a valid table.

I am only pulling the PurchaseOrderLine table and I included several fields (company, purchaseorder, purchaseorderline, etc, and cncode).  the ONLY criteria I set is CNCode = "Bill Only".  I've also tried CNCode Begins with B, CNCode <>"", CNCode = 3, etc.  No matter how I try to query that field I get the error I showed above.  I can query this table other ways, and include that field in the results and then filter it.  However, what I'm trying to find is any open PO that has Bill Only on the line.

I am also getting that error if I try to pull all PO's where Closed = No, or ClosedDate > [today]....I queried this table by pulling a specific vendor that I know has open Bill Only lines and it worked fine by only pulling the vendor #, then filtering.  So I know the program itself is working but querying these fields is getting me nothing.

don-peterson

Hi Donna,



What you may be running into is a "State|Value" field. "State" is a developer term for description and value is what is stored in the database. So for your example, the "State" would be "Bill Only".



If you notice the heading in the Filter section, it has "Value". So you will need to filter on the value saved in the database. To see what these are, notice the dropdown "Show State" or "Show Value" located to the top right of the fields. With "Show State" selected running the query will show "Bill Only" for example. Note the row(s) that have a "State" of "Bill Only". Then change the dropdown to "Show Value" and run the query again. Take a look at the row(s) that had "Bill Only" and you will now see the value stored to the database.



Place that value into your filter and then run the query and you should only get those records. You can then change the dropdown from "Show Value" to "Show State" and you will see "Bill Only" in the query results. Leave the filter set to the actual value, of course.



Hope this gets you closer to what you want.



Don