GI - Customer Summary to MySQL statement

My only thought at this point is in GI the current date isn't really the actual system date and that is why I'm seeing more activity for some customers.

Acumatica maintains a "Business Date," which acts as its own current date. It is different from the system's actual date and is used for specific functions within the application, particularly for new data entry.

Good guess but that didn't solve the issue.

I figured out what the issue is. The customer results in GI (balance / overdue) are SUM()ed as a total. I’m showing the individual payments for each customer with my SQL statement..

​@JSpikowski you can use ‘Request Profiler’ screen in Acumatica to log actual SQL statements generated by any process.

It really helps in such situations.

How can I use the Request Profiler to capture the SQL statement generated with the GI Customer Summary preview?

A screenshot of what the Request Profiler needs to look like to capture the GI event would be great.

@dmitrii-naumov-56

I tried putting in the URL for the Preview of Customer Summary in GI but I can't seem to get any results. I tried all the the options on the Request Profiler screen with no luck.

An Acumatica developer friend helped me with getting results from the Request Profiler using the GI Customer Summary. The Statement doesn’t work in Oracle MySQL Workbench or phpMyAdmin. My guess is the statement is depending on internal Acumatica variables and functions. I was happy to see the profiler returning MySQL syntax rather than MS SQL.

I was able to get my GI Customer Summary MySQL statement to work. I also included what Acumatica Request Profiler generated as a SQL statement. 

GI Customer Summary - ScriptBasic MySQL

Request Profiler Generated SQL Statement

I'm not sure if this a bug like the duplicate Set Cookie bug but why is Acumatica generating two duplicate FROM references?

FROM    `ARRegister` `ARRegister`

I'm not sure why the MAX() function is being used on all the SELECT columns.

I initially created my MySQL statement from the GI Customer Summary using the Generic Inquiry definition which uses MS SQL based syntax. The LEFT JOIN is on ARRegister where the MySQL statement generated by the Request Profiler uses ARRegister as its FROM table. Using both of these tools allowed me to generate an efficient SQL statement that returns what the Customer Summary Preview displays. 

I tried this with my MS SQL Acumatica DB and was able to execute the query in MS SQL Management Studio. The MS SQL generated SQL statement looks cleaner than the MySQL generated version of it.

Did Acumatica outsource their MySQL library interface? I hope what I'm seeing is unique to Generic Inquiry and not how the MySQL code looks in standard accounting functions.

I'm hoping this isn't a repeat when Postgres SQL is released.

I converted the MS SQL GI Customer Summary SQL statement to MySQL using VS Code with Supermaven Copilot. The code didn’t error in MySQL Workbench like it did with Acumatica’s version , it just didn’t return any results. Why Acumatica didn’t take this approach is beyond me. 

MS SQL GI Customer Summary convert to MySQL

Oracle MySQL Workbench is a PoS. My converted MS SQL statement to MySQL worked fine in phpMyAmin under Linux. 

I updated my Oracle MySQL for Windows to 8.0.43 but there is no change getting my converted MS SQL to MySQL statement to return results.

I wasn’t unable to get any results from the MS SQL to MySQL statement in the updated mysql.exe console utility or as a ScriptBasic script.

I was able to get the script to work on Linux.

Converted MS SQL to MySQL - Linux MariaDB using ScriptBasic

UPDATE

I was able to get the converted MS SQL  to MySQL to work on Windows including Workbench by changing the CompanyID. 😖

The original MySQL statement generated by the Request Profiler only returned errors.

I heard that Acumatica uses YAQL, which stands for "Yet Another Query Language," which is an embeddable and extensible query language designed for performing complex queries against arbitrary data structures, particularly in-memory data. 

It is currently offered as a Python library but was originally developed in C# which must be the version Acumatica is using.

The excessive use of MAX() and terrible MySQL statement generation should motivate Acumatica to find another solution.

The worst part is it doesn't generate SQL statements as defined by the GI definition. My example using the GI definition looks nothing like what is being generated.

I wanted to confirm or not that YAQL was specific to Generic Inquiry or used globally. It seems that YAQL is Acumatica’s database access library. I Request Profiler captured the Customer initial list screen. This is the URL it used for the query.

~/genericinquiry/genericinquiry.aspx?id=8b5104f6-326b-4194-a29e-b424601e5b61

Here is a screen shot of the generate MySQL SQL statement for the Customer list in Workbench.

Strange that the goal is a Customer list and this SQL statement finds that Notes are the prime driver.

.

MS SQL isn't my primary database server of choice. MySQL (MariaDB) is the most used DB with Linux based servers. Postgres SQL is a close second.

I was curious why, Acumatica MS SQL statements contained so many [table / column] ​​​​​bracketed statement references.

[AI Response]

Many tools and wizards, including SQL Server Management Studio (SSMS), will automatically enclose all identifiers in brackets when generating scripts. This ensures that the code will work correctly even if an identifier happens to contain spaces, special characters, or reserved keywords, avoiding potential errors. It also provides a consistent way to reference objects.

If the Acumatica user has direct access to their DB, efficient SQL can be created to return what you are asking for and not a all inclusive possible association's of your request.

This becomes a huge benefit if you are front-ending your external interfaces.

I created a web page using the ScriptBasic web sever to display a Customer contact list using the Acumatica hosted DB under Linux  The script is using the ScrptBasic MySQL C extension module for direct access to the DB.

https://demo.clouds-r.us/home/acumatica/Customers

This is the ScriptBasic source for the web page.

https://demo.clouds-r.us/files/Customers.txt

There is no home/acumatica directory out of webroot. The ScriptBasic scripts reside in my user directory. Nginx proxies home to the ScriptBasic muti-threaded websrrver written C. Nginx also handles the http to https translation. Apache is used for images  file transfers, load leveling and security.

There doesn't seem to be any interest hosting an Acumatica database on Linux or front-ending external interfaces with scripting. Maybe the forum isn't the right platform for this topic.

I thought I would check out YAQL as a SQL front like Acumatica uses.

Run some queries:

yaql> $.customers...yaql> $.customers.orders...yaql> $.customers.where($.age > 18)...yaql> $.customers.groupBy($.sex)...yaql> $.customers.where($.orders.len() >= 1 or name = 

I'm actually surprised how well the REST responses are formatted after seeing how the SQL statements are generated with YAQL.