I have used the combination of ExecuteSQL and ValueCount quite a few times throughout my databases before, with almost no issues. Generally what I will do is set a variable by running an ExecuteSQL, then take a ValueCount of that variable. Then with that ValueCount I will loop through my original ExecuteSQL variable.

However, I am now trying to do something different with this combination. Instead of using the ValueCount variable for a loop, I'm trying to use it as a Global Variable that will be displayed on the main menu. Here is an example of the code I'm trying to use:

Set Variable [$source; Value: ExecuteSQL ( "SELECT UUID FROM Inspections WHERE Inspector = ? AND Accepted ≠ Accepted" ; "" ; "" ; $$fullName )]

Set Variable [$$accepted; Value: ValueCount ( $source )]

The UUID field is, as expected, an auto-entered "Get ( UUID )" field. The Inspection table is a table containing all of our part inspections. Each inspection begins with it's Accepted field having a value of "Rejected", if the correct parameters are met this field will become "Accepted". The Inspector field contains the first and last name of whomever conducted the part inspection. The $$fullName variable is set previously in this same script by comparing the user's Username to a Table that contains all of our user's Usernames and Full Names.

What I was hoping to do is use a Merge Variable on our main menu that displays our $$accepted variable, showing the user how many unaccepted inspections from that particular user in our database.

However, it only ever displays a value of "1". One of our users has 6 unaccepted inspections in our database currently, but this script only shows that he has 1.

I am wondering if you can't use Merge Variables or ExecuteSQL in this way, or if I'm simply missing something obvious.

Please let me know of any more information I should provide. Thank you for any assistance you may offer!

Edited October 26, 20178 yr by NewBoard

ESQL does not support ≠.

Try IS NOT or <>

Also, I would strongly suggest that you store an id for the related Inspector in your Inspections table, rather than trying to match on names.

$$accepted = "Select Count(1) FROM Inspections WHERE id_inspector = ? and Accepted <> 'Accepted' " ; "" ; "" ; $id_inspector"

 

Bev Voth's indispensable SQL guide