Method:Classic General Q&A

Re: Checking for non-null values in table loop (query)

furrywombat — Thu, 31 Oct 2013 15:23:35 GMT

Perhaps I will proceed with the hidden pre-filtered grid concept to replicate the more advanced table queries. If at some point you offer a beta version or upgrade to the ability to define the SELECT query criteria for table loops, I would love to hear about it.

Thanks.

Charles

Re: Checking for non-null values in table loop (query)

Method_Nelson — Thu, 31 Oct 2013 15:20:33 GMT

Hey Charles,

I'm sorry the process has been so frustrating.

After doing some advanced testing I was able to replicate your scenario (thanks for the additional workflow details).

Unfortunately, at this time I am unable to provide you with a better workflow than you've noted above.

We appreciate the time and effort you took to document this issue.

Cheers,

Nelson

Re: Checking for non-null values in table loop (query)

furrywombat — Sun, 27 Oct 2013 15:28:25 GMT

Hey Nelson,

While I seriously appreciate the effort you've put into your answer, I believe the entire point of my question is still being missed.

OBJECTIVE: To define semi-complex table SELECT queries, which actually follow the criteria presented to them. As we only have the option to define two AND WHERE statements in the table loop action we're really not getting overly complex, but would like to know simply how to create a table loop query statement where I can define criteria such as IS EMPTY or IS NOT EMPTY. I would also like to define queries where linked values are able to be queried properly. For example, looping the SalesOrderLine table where (linked value from SalesOrder table) SOIsManuallyClosed = NO (this works just fine & dandy), however, when attempting to define WHERE (dropdown value, referencing RecordID of, where table dropdown value defined under table edit IS in fact defined as RecordID) APIResponseStatus <> 264 (integer value with a non-integer meaning) I get zero results, in spite of the fact that many results DO exist!

PROBLEM: This does not appear to be possible! While I can define grid filters for IS EMPTY or IS NOT EMPTY, this does not appear to be something that can be done with the current table loop query builder which just seems so frighteningly ridiculous. To further the problem, I am beyond frustrated with the inability to define certain criteria in my table loop queries... essentially needing to loop through 3,000 records when only 300 need to be queried, and eliminating 2,700 of them with an opening conditional statement based on the value of a returned column in each row (simple stuff... like IF X <> 264). It's still pretty quick, but not nearly as efficient as a properly-constructed SELECT query.

HALF-ASSED APPROACH: I have considered creating hidden grids on a screen with complex filters designed to simulate the table query that I am attempting to accomplish, but why God, why, would I need a hammer to open a darned banana?

PERFECT EXAMPLE OF ASS-BACKWARDS RESULTS: We have a fairly simple system that generates purchase order numbers based on a secondary column generated for strictly numerical values. Each time a new PO is generated, the numerical value of that PO number is saved in the secondary column in order to calculate the PO number for the next new purchase order. I am attempting to construct a simple loop, a fail-safe for instances where this numerical value is NOT saved for whatever reason and winds up with a blank, or NULL value that is processed before the "populate purchase order number" action set is initiated. The ONLY way that I can search for these values is by constructing a query like this... loop table `PurchaseOrder` where `RecordID` <> -1 (i.e. all purchase order rows). Then, perform a character function on the length of the field that I'm trying to see is blank or not (inside the table loop). If the length is zero, then the action set proceeds by populating the previously BLANK or NULL values with the necessary data. Wouldn't it be SOOOOO much easier if I could simply define this logic in the initial table query, i.e. Let's check the PurchaseOrder table and return rows where X field does not currently have an assigned value, or if the currently assigned value is empty? Am I just missing some OBVIOUS way of going about this and making this far more complex than it needs to be??

Lastly, NO I am not checking the alphabet for a numeral in constructing my query. I may be a bit slow, but come on...

Let me know if you have any ideas on this, or if this is something that the developers are working to implement at some point in the future. Greatly appreciate your efforts here.

Best,

Charles

Re: Checking for non-null values in table loop (query)

Method_Nelson — Wed, 23 Oct 2013 15:43:08 GMT

Hey Charles,

I may still need to probe you for a little more information before we can fully troubleshoot your scenario but here is what I was able to gather:

Trying to iterate through a table using Select (which is built into the loop action) but not getting the desired results
Cannot use linked table values when defining the criteria for a table loop (Why values are returned when you try and use a table loop? is it an integer ex. 3 but you want 'John Smith')

Case:

Loop through the Sales Order table
Trying to select a linked field within the table
One value is an API response for the web order status (What is the field value here? Is it coming from the API as text or an integer? How is it being stored in the Sales Order Table?)
Stated the only work around is to create a series of nested conditional statements
SQL is the preferred solution

So Charles based on the aforementioned here is a question I have for you :

How are you interested in seeing the results of your query?

If you are interested in viewing the results inside of a grid, you can leverage a handy little function called SQL Overide. To access it follow this workflow:

Customize the grid you are working with
In Step 2 of the wizard select the Advanced Link next to the field you would like to select by ex. Web Order Status
Select SQL Override+
Create a 'mock' SQL statement so you can understand how Method generates SQL statements
Modify it as necessary for your needs

ex. SELECT viewaccSalesOrder.WebOrderStatus_RecordID FROM viewaccSalesOrder WHERE viewaccSalesOrder.WebOrderStatus = 2 OR viewaccSalesOrder.WebOrderStatus <> 7

Another example can be found in this post.

Now the statement I wrote above was meant to be an to show you what your statement could look like.

I hope this helps,

Nelson

Re: Checking for non-null values in table loop (query)

furrywombat — Wed, 23 Oct 2013 01:17:21 GMT

Hey Nelson,

My apologies, I believe I may have confused you by not specifying the type of query I was referring to. UPDATE queries are not an issue as far as I can tell. The issue presents itself when I'm trying to loop through a table with the Method equivalent of a SELECT query.

To further the issue, I cannot seem to use linked table values when defining the criteria for a table loop either. For example, I'm trying to loop through the SalesOrder table and have some linked values there that I want to use in the SELECT. One of those values is the API response value from our website for the web order status. I am trying to create a SELECT query for a table loop that ignores sales orders with a particular value (SHIPPED). So... WHERE `APIStatusResponse` <> 'WHATEVER' seems to be ignored completely.

The only way around this seems to be to loop through the whole darn table then use multi-nested conditional statements IF X = 2 or IF X <> 7... seems to me I SHOULD be able to define this stuff in the SELECT query, a far more efficient method of doing things.

I'm really just trying to wrap my mind around the logic that you guys put in place to define the dummy-proof construction of plain-english SELECT queries. It sure would be a LOT easier if I could just write the SQL query myself rather than using the dummy system that appears to have far more layers of logic than it needs to (assumedly, to prevent non-techie-types from constructing impossible or overly-complex queries), and is screwing up my logically valid queries as a result.

What CAN I use as valid criteria when looping through a table? What CAN'T I use?

Best,

Charles

Re: Checking for non-null values in table loop (query)

Method_Nelson — Tue, 22 Oct 2013 13:21:08 GMT

Hey Charles,

In response to your original post, I created two loops which update a field in a table according to a blank & non-blank field in my table.

Here I was able to use the expressions I posted previously exactly as you'd expect them to work.

Updating a Blank Value

Updating a Non-Blank Value

In my experience with customization I've noticed a couple of things:

When users have created a button which is supposed to loop through a grid, they forget to refresh the grid after the actions are complete to see the changes.
Sometimes, their "Where" statement is incorrectly specificied or the loop is not set up correctly to interate through the desired values.
The value you want to place is a particular field may not be accepted because of database limitations ex. replacing the number '4' with '4th floor' when the field can only store the integer type.

Could you please provide me with some more information about what you're trying to do in your loop and what tables / fields are you working with.

In my example I was working with the Comments field in the Activities table.

Thanks,

Nelson

Re: Checking for non-null values in table loop (query)

furrywombat — Mon, 21 Oct 2013 21:17:24 GMT

Hey Nelson,

To clarify, I guess I'm not really searching for a NULL value per se... but a BLANK value. I've had a great deal of difficulty in determining how to construct table queries where I am searching for either BLANK or NON-BLANK values. Your suggested approach does not work. In fact, is backwards. In order to query a table where blank or null values exist in a field the only way that I have been able to do this is to run a query like this... WHERE X <> (type in = nothing... blank). This will return blank or null values, strangely enough. I learned this from the following post:

https://www.methodintegration.com/cs/forums/t/3779.aspx

But when I switch it around like so... WHERE X = (type in = nothing... blank), it doesn't work.

Am I making sense? The logic seems screwy, but do let me know if you have an answer on this. Thanks!

Best,

Charles

Re: Checking for non-null values in table loop (query)

Method_Nelson — Mon, 21 Oct 2013 21:04:49 GMT

Hey Charles,

I'll need more information about what a 'Null' value is in your definition.

Is it a field with the words Null? or a blank field? could it potentially be fields with a space?

Using the same logic you posted above, you can search for both null and non-null values using the queries below.

Checking for null values:

x = <blank value>

Non-Null values:

y <> <blank value>

Cheers,

Nelson

Checking for non-null values in table loop (query)

furrywombat — Sun, 20 Oct 2013 01:51:26 GMT

Checking for null values appears to be easy, if not counterintuitive (X not equal to ... type in... blank value). There does not appear to be any way to check for non-null values, though. What am I missing?