SUB QUERY help needed.. from another table.. HELP!!! Please.

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

SUB QUERY help needed.. from another table.. HELP!!! Please.

Post by toconnell » 2013-04-26 12:35

Should be simple but not working..

$combo_SubAssigned->Query = "SELECT `Name`, `Name` FROM `compliance` WHERE `compliance.ACTIVE`=TRUE ORDER BY 2";

Gives me syntax error..

WHAT IS WRONG WITH THIS?
I just want that field to have a drop down that will show a lookup of names of drivers from another table called compliance that are in ACTIVE status, meaning the ACTIVE field in the compliance table is shows TRUE.. the ones that are False show up otherwise.
Help Pretty Please.
Tina O'Connell
Web Dev & Appgini FAN

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: SUB QUERY help needed.. from another table.. HELP!!! Ple

Post by toconnell » 2013-04-26 12:37

I tried ACTIVE=TRUE and ACTIVE='TRUE' and `ACTIVE`=TRUE or `ACTIVE`='TRUE' and all don't work.
Tina O'Connell
Web Dev & Appgini FAN

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: SUB QUERY help needed.. from another table.. HELP!!! Ple

Post by KSan » 2013-04-26 15:57

Is Compliance.Active a check box in AppGini? If so checked state is usually a 1 in the db and un-checked is Null. So you could test for '1'.

Your general query looks incomplete though. Just post the list of fields from both tables here and indicate the join fields if not obvious by name and I'll see if I can help.

Best.

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: SUB QUERY help needed.. from another table.. HELP!!! Ple

Post by toconnell » 2013-04-29 15:00

Hi KSAN.. thanks for your help.

the only fields that are in both and that are unique data is the NAME

routes table has these fields...

County
School
SchoolContact
SchoolContactNumber
TripNumber
Driver Assigned
Unit Assigned
Yard Start
Spare Busses
Subs
Route Number
Miles
KML File Link
Date Driver Assigned
Date Driver Unassigned
Route Type
Notes
Route Start Time
Route End Time
First Stop On Route
Last Stop On Route
BusHavePerYard
DriverStatus
SubAssigned
DateSubAssigned
TripSubAssigned

Compliance has these fields...

Name
FirstName
LastName
Mail City
HomePhone
WorkPhone
CellPhone
ACTIVE
ApplicationforEmployement
BackgroundCheckClearance
Comments
Complaints
CopyofApprovedVendorBadge
DateInactive
DexterityExpiration
DexterityReceived
DOB
DOTStatus
DriverNotes
Drug Test Chain of Custody Results
Email
EmployeeHandbook
Gender
HireDate
LastUpdatedby
Last Updated Date
License Number
LicenseClass
LicenseEndorsements
License Expiration Date
LicenseRestrictions
LicenseState
LocalID
Logof40HrTrainingCourse
Logof8HrRefresherCourse
Mail County
MailState
MailStreet1
MailStreet2
MailZipCode
MI
PhoneIssued
PhoneReturned
PhysicialExamExpired
PhysicialExamRcvd
RecordofRoadTest
SSN
TurberculosisTest
UniformIssued
Vendorbadge
ASInsurance
ClearedCounties
DriversLicenseIMAGE
Tina O'Connell
Web Dev & Appgini FAN

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: SUB QUERY help needed.. from another table.. HELP!!! Ple

Post by toconnell » 2013-04-29 15:01

Active is a drop down.. not a check box.. so that is not a 1, it is TRUE. There is data in both.
Tina O'Connell
Web Dev & Appgini FAN

KSan
AppGini Super Hero
AppGini Super Hero
Posts: 252
Joined: 2013-01-08 20:17

Re: SUB QUERY help needed.. from another table.. HELP!!! Ple

Post by KSan » 2013-04-29 15:44

Got it. I added a text field called Active and filled it with TRUE and FALSE in a parent table similar to your drivers and found a way to make it work. Here's what you need.

1. Go into your Routes table. Go to Driver Assigned field. Click Lookup Field tab in the lower panel. Choose Compliance in the Parent Table drop down. Choose Name in the Parent Caption field part 1. Save your project.

2. While you are still in the same screen click advanced.

3. Uncheck box that says Show All Records of parent table (default). THis will make the SQL query box editable.

4. You should now be seeing a default query that reads like : SELECT `Name`, `Name` FROM `Compliance` ORDER BY 2

5. Add the following WHERE constraint and you should be good to go. SELECT `Name`, `Name` FROM `Compliance` ORDER BY 2
WHERE ACTIVE = 'TRUE' ORDER BY 2

6. Save, upload & test. It worked for me.

Please note I used the ` ` around ACTIVE but after saving and coming back to the same window I noticed that AppGini removed them. No idea why. It works for me though. I see only the lookup records where Active = TRUE.

PS, your original post refers to $combo_SubAssigned->Query... which tells me you're perhaps trying to edit the code generated by AppGIni. My suggestion above is completely achieved using the AppGini user interface so no code edit is required.

Hope this works for you. All the best!

User avatar
toconnell
Veteran Member
Posts: 204
Joined: 2013-04-09 19:29
Location: Oklahoma City, OK
Contact:

Re: SUB QUERY help needed.. from another table.. HELP!!! Ple

Post by toconnell » 2013-04-30 18:21

I did it to a test database, EXACTLY as you said above and when I went to open that table from the application..
This error was given..

Error: Unknown column 'ACTIVE' in 'where clause' .
If you haven't set up the database yet, you can do so by clicking here.
very strange.. ???
Tina O'Connell
Web Dev & Appgini FAN

Post Reply