Minimum Version Required: 25.04
Pre-Conditions
To perform the troubleshooting steps, you'll need the following permissions:
For Adding Users Locations or Users Companies:
Add, View, and Delete Permissions: 'Everyone' permission and 'Permission Edit' permission.
For Creating a New AR Account Map:
Add and Delete Permissions: 'General Accounting Management'.
View Permission: 'General Accounting View'.
Users must have administrative access to modify user settings and location configurations within the system.
Steps
This guide outlines two possible solutions to help resolve a "Syntax Error" that may occur when a user attempts to create a password and log in. This error typically occurs when the system cannot find the user's associated company.
System Error: "SQL Failed, Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY Companies.Company ASC' at line 7 QueryRunSeconds = 0.0xxx PageRunSeconds = 0.000 -- Page: api"
1. Identify the affected user: Note the name of the user(s) experiencing the syntax error during password creation.
2. Access User Record: Navigate to the Users table using the Universal Search bar.
3. Attempt Solution 1: Add a User Company:
Locate the profile for the affected user
Subtable "Users Companies"
Ensure a company is configured and assigned to this user. If not, add or select the correct company
Save the changes
Have the user attempt to create their password again
If the Users Companies is properly configured, a company record will exist, and creating the password should work successfully.
OR
4. Attempt Solution 2: Add an AR Account Map to the User's Location:
Identify the specific location associated with the user
Navigate to the affected users record, and subtable "Users Locations"
Then, navigate to the "AR Account Maps" table
Ensure an AR Account Map is configured for the user's primary working location
If an AR Account Map exists for other locations, you may need to create a new one for the problematic location.
Save the changes
Have the user attempt to create their password again
If the AR Account Map is configured for the location, the company will be recognized. This should result in successful password creation.
Please select one of the two options above, then proceed to create the password. Once the password is successfully created, the user will be able to log in.
If the issue persists:
Please contact support if the issue persists after attempting both solutions, and provide the following information for efficient troubleshooting:
The exact error messages or codes encountered (e.g., "SQL Failed, Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY Companies.Company ASC' at line 7").
Screenshots or video recordings of the issue, particularly of the error message and the locked webpage.
Log files (if accessible and relevant to user creation/login).
The exact steps taken by you and the new user that led to the issue.
The date and time the issue was first observed and any recurrence.
Any changes made to the system (e.g., new user added, location created) before the issue appeared.
Fields And Definitions
Syntax Error: An error in the structure or grammar of a command or statement, often in a programming language or database query (like SQL). In this context, it indicates a problem with how the system is trying to process information related to the user's company.
SQL (Structured Query Language): A standard language used to communicate with and manage relational databases. The error message indicates an issue with a database query.
Users Companies: A system setting or field that links a specific user account to an organizational company within the system. This is crucial for defining user permissions and data access.
AR Account Map: A configuration within the system that maps an Accounts Receivable (AR) account to a specific location. This mapping helps the system identify and associate financial transactions and company information with that location.
Best Practices
Verify Company Association: Always ensure that users (especially new users) are correctly associated with their respective companies and locations during the user creation process to prevent future login and password creation issues.
Complete Location Configurations: Before onboarding new employees to specific locations, confirm that all necessary configurations, such as AR Account Maps, are in place for those locations.
Test User Creation: After making system-wide changes or updates, it's a good practice to test the user creation and login workflow with a test account or site to identify potential issues proactively.