Live Query Not Working

[Abu_Bakr_Siddique]

Hi, I wrote an SQL query and attached it to a menu. I have already run the query in the query editor as follows:

and received results as shown.

I’ve attached it to the following Menu using the settings:

However, as I am logging in and going to the menu I encounter the following error:

Can you please help to identify what this error might be from?
Thank you

[mark]

Hi,

I believe the error is caused because of the ‘;’ at the end of the query. When you execute queries for display we limit the results to 5000 and it is not handling the ‘;’ being there. We will correct this but in the meantime if you just remove it you should be fine.

On a seperate note, I noticed that you are using a fixed value for the iUserKey. If you would like to have this automatically work for any user you could replace the value you have with a ? and then add a parameter to the query. Set it as an expression with a value of {{five.currentUserKey()}}. This will then make the query filter by whoever the current logged in user is.

Hope this helps!

Mark

[Abu_Bakr_Siddique]

Hi Mark,

Thank you. I have decided on this alternative that you mentioned and regarding using JS function to perform this SQL query, to which event should I attach this JS function?
Currently in the Menu Item, Sales History, I do not have any forms in there since I intended to show the SQL query values instead of a form. Is there anything I could add maybe in the Menu Item that allows me to automatically run my JS function containing the SQL query?

[mark]

You can attach the query directly to a menu item that will run as soon as it is selected.

If you have other parameters you want to set in the query these can be done using a function connected to the OnSelection event for the query. I tend to use variables for live queries as these can be easily set in JavaScript by calling five.setVariable(). The variables can be referenced in the query by using ? and then setting a parameter for each one with {{five.variable.}}. The query can then be refreshed by calling five.refreshTable() and giving it the name of any table used in the query. If the query is set to live it will automatically refersh whenewver any data in one of the tables it uses changes or when five.refreshTable() is called.

Mark

[Abu_Bakr_Siddique]

Hi Mark,

I have been trying it for some time. Here’s some screenshots, I don’t think it is working as it is not showing anything in my Menu Item - Sales History.
Below is the SQL query (SalesHistorySeller) with the parameter settings:

I am attaching this query to the Menu called Sales History:

It seems that when I log in with the appropriate user. I am not really getting any records in the Sales History Menu

[Abu_Bakr_Siddique]

It seems upon further inspection that the parameter is actually not being set at all. As shown using the Inspect to run the application and see Query Result in Sales History menu.

Therefore, I changed the query parameter to:

Then I get the appropriate iUserKey for the logged in user that I checked with the CSV files and there should be exactly those 2 records shown in the Results of the SQL Editor initially in the original question of this thread.

Although I am getting those 2 records but those are not being displayed onto the page of the Sales History Menu as shown. The Results from the Inspect shows I am getting those 2 records.

I feel that I am doing something wrong, but I can’t figure out, as to what it is.

Abu.

[Abu_Bakr_Siddique]

I think I may have resolved the issue for anyone facing similar circumstances. Basically, the Query’s Parameter ID and the SQL variable that we are setting to ‘?’ should have the same name (I am guessing with the Casing too). So, since in the last line of my SQL query I am writing:
iu.iUserKey = ?
Then the Parameter ID for this query also needed to be iUserKey, as shown:

This resolved the issue.

I apologize for the long thread and thank you @mark for your help.

Abu.