Charts custom query in RadSystems 8.5.9

Hello friends,

I have built a charts through querying my table that basically lists type of expense (food, services, etc). The chart works perfectly… But I have not been able to condition it to the data entered by each user. User independent shows all table data on expense type. Through User Records Management I have configured it so that each user sees his own records… but the chart shows him all the data, not what he has recorded.

I have tried configuring the query with WHERE and AND, through the User ID, I tried through Query_params but it hasn’t worked for me. I appreciate if you can give me some help. @willvin

Hi,
What project type are you working with?

LARAVEL + bootstrap :slight_smile:

Add this to your query

WHERE field = :field

and add this to the query parameter section

$query_params['field'] = auth()->user()->user_id;

Replace the field with the table field name you want to filter the record by, and user_id is the field from the user table.

Hello my friend, Thank you!!! It worked perfectly following your recommendation. However, I tell you that it worked for me in the Record Count module, I applied the query that you indicated and it works instantly… But I try to use a Chart and in the chart if it doesn’t work, and I’m using the same table, I don’t know If it’s because I’m grouping the field options there… could you shed some light on it, because it didn’t work there.

SELECT COUNT(Viaticos.id) AS count_of_id, Viaticos.asignacion FROM Viaticos GROUP BY Viaticos.asignacion WHERE user=:user

$query_params[‘user’] = auth()->user()->id;

Hello friend: UPDATE. I adjusted the script by prepending the WHERE before the GROUP BY and now it works!

I am using the same way to record user’s account with the record method shown above. However, If the user is new and never had any record, it should return 0. However, I am getting error message for the null response. I wanted to add a code to check the output is null or number and couldn’t do that from the window above to add the code. How to handle such issues?

Read here sql - Detect if value is number in MySQL - Stack Overflow and see how you can rewrite your query to work the way you want.

Sorry to join this chat,

I have something to ask you.

What if a parameter takes a value from another table?

$query_params['field'] = tax()->tax_id;

it is possible if you can write your query to pull the specific data you want to use and then pass it to the parameter. Note, that this auth()->user()->id is automatically handled by the app, more like a session. you cannot call tax()->tax_id unless you have created such a function to pull your tax data.

1 Like