Query Troubleshooting and Tips
If you have a tip that should be considered core documentation, please Contact Us.
Query Tips
- Use the Clear Query button in the Query Tool or reset your iWeb session before attempting to create or edit a query
- Make sure you save any query you're creating or editing before trying to run it
- If you run it once it will usually still work fine, but if you run it several times and keep using the Back button to get back to it, things often start acting strangely
- In general the back button should not be used. If you want to get back to the query tool, click the Run Query link again and you should be returned to the Query Tool with the current query loaded
- For creating a new query from an existing one, update the name, save as new query, and then reset your session to make sure both are showing up
- Editing subqueries might not work reliably
- The most consistent solution is to create a new subquery from scratch and delete the old one
- There have been some bugs associated with this fixed in recent builds, so this might be more reliable now
Query Troubleshooting
Q. Why are some tables not in the Column to Query List?
A. These tables are based on the List Table/From Table setup.
Q. OK, so how can I add more tables in the Column to Query List?
A. See Table Join Wizard for instructions; you will need a Toolkit license to do this.
Q. Why can I not select a column with the uniqueidentifer data type in the Column to Output section?
A. netFORUM does not want to output GUID columns because most end users would not want to deal with GUID data, so GUID columns are not available in the output columns list. You might be trying to output a code. Example, you want to output Membership->Member Status. You will need to look for Member Status->Member Status instead.
Q. But I really need to output GUID data types. I need to do so for a particular data routine.
A. You can customize the SQL as described in Query Create SQL. To force the query tool to output GUID columns, you might need to convert the data type to a varchar.
Q. It seems that the Query is applying a filter on my results. Example, I am trying to get a list of past, present and future committee people, but it's only showing me current people.
A. In some cases, the underlying table setup in the List Table area is applying a filter. If this is the case, then it might be necessary to change the filter conditions in the List Table setup.
Q. Nothing is coming up in my query.
A. If you have distinct checked and a column with the data type of text or av_text (such as bio, long descriptions, notes, etc.), then nothing will show up. Uncheck distinct or remove this column from your results list.
Q. I see some columns in a table but others are missing. Where are they?
A. It could be that your User does not have privileges for the column. It could be that the column definition has the query select flag cleared. Or it could be that on the Query Columns list, the column doesn't appear because its data type is uniqueidentifier.
Q. A new custom client table does not display in the list of fields to query upon. Where are they?
A. If on version 2011 or later, it could be that the column definition has the query select flag cleared. It is cleared by default when adding a new table.
Dates
Suppose you want all dates in February. You should have the following two conditions:
- Date >= 02/01/2010
- Date < 03/01/2010
This ensures that the query returns anything that happens a split second into 2/1/2010, and anything that happened up to the stroke of midnight on the last day of February.