Quick search function help

[MarionT]

Re: Quick search function help

Don - it's ok - I'm new to all of this too, but I just happen to know how to do this...

forget about A5 for the moment

Get the query working in MySQL; if it doesn't work in MySQL, it won't work in A5

in My SQL, can you do a select as shown in Post #14?

Like :
Select
E_date,E_month, person, category
from
Table
where E_month = 'March'

We're not trying to have A5 populate the month column; we're trying to have MySQL at the source.

How did you create the e_month column? Is it a calculated column?

Assuming that you are trying to follow my suggestion #1 from post #5, the column in MySQL needs to be a calculated column

You want the definitiion of E_month to be: MONTHNAME(e_Date)

Or, did you just make a character based column called E_month? If so that won't do the trick. E_month needs to be a calculated column, defined as: MONTHNAME(e_Date)



PS -you can also create the e_month column via my suggestion #3 in post #5. I am NOT saying that A5 cannot create the month column for you. I'm just responding to the fact that it appears that you are trying to follow my suggestion #1 from post #5

[MarionT]

Re: Quick search function help

I don't know MySQL.... perhaps you do not have the option of creating a calculated column....

if that is true - do so within A5's SQL Genie - as per suggestion#3 of post5

[RichCPT]

Re: Quick search function help

Don, it sounds like you are right on track with your first post. I suspect the piece of information you might be needing is the Query property for the grid. Do not use "Table" as your source. Use a "View" and setup an expression in the VIEW to return MONTHNAME([date]) as an extra column. As suggested earlier, avoid SQL reserved names as field names at all cost. There are hundreds of reserved names, though. I like prefixing each field name with a two-characters prefix that represents the table the field name is in. This way I avoid reserved words and avoid having use qualified field names when joining two tables together in a view that have common column names.

If you need to update records with your grid then I highly suggest using the "SQL Select Statement" option for the source of the grid. Beware of the training tutorials that suggest using the "Table or View" option as the data source for an updateable grid. Alpha is just so much friendlier if you use the "SQL Select Statement" option. Alpha needs to know about each table separately, so it can create an update command that will not violate SQL restrictions about updating more than one table at a time. Also, make sure you constantly look at the popup dialog for the "Select Primary Key(s)" button.
1) Only one of the tables in your "SQL Select Statement" should be updateable; it should have its primary key indicated and the option for "Primary key is auto-increment?" needs to be properly set.
2) All other tables should be marked as "Read-only"

If you use SQL Views in your "SQL Select Statement", rather than acessing the tables directly, then Alpha will constantly forget which field is the key field - it is rather annoying. People have asked Alpha to fix this, but it seems no one has made a convincing case for them to bother fixing this yet. I think it is important to fix this as part of making web applications that are cloud enabled, enterprise wide and highly secure. For SQL databases with multiple access points it is quite conceivable that VIEWS could be used to enforce very high level of security access against the database, especially if different applications will be accessing the database. There could be an enterprise procedural requirement that separate applications accessing the same database, must do so by only using VIEWS.

-Rich

[MarionT]

Re: Quick search function help

Rich -
I'm so glad to know this happens to someone else too!

If you use SQL Views in your "SQL Select Statement", rather than acessing the tables directly, then Alpha will constantly forget which field is the key field - it is rather annoying.

I use 'Table or View' for:
view as the datasource when the component is readonly
table as the datasource when the component is updateable and doesn't need any other data

Otherwise, I use 'SQL select Statement' with 1 table updatable.... and, I find (but Selwyn cannot confirm) that I run into trouble if I edit the default alias names of the linking fields, from within the SQL Genie.


Don -
if you are still having trouble, be sure to come to the (free) Friday webinar - I'm sure one of the presenters will be able to show you how to make the month column from within A5's SQL Genie -
http://www.alphatogo.com/support.a5w...3d2f9b709d0656