ERROR in cron job with condition from [current_user_id]

[pepe]

Hi Sergey,
using Version 2.5.2, I set up a new MySQL formula field to dertermine
1. the state (chosen option) from a dropmenu and
2. whether the current user is assigned to the task
The formula is:

CASE
WHEN get_value([902])>=4 THEN
IF ([1074]=(SELECT [current_user_id]),1,0)
ELSE 0
END

I have also tried to use [current_user_id] without the SELECT but it does not work either.
In the table listing the field shows correct values in the entier table .... everything fine.

The problem occurs when using the values from the MySQL Formula field in an autoatic status field.
I have used values from from MySQL formula field at different applications in Automatic Status fields and they always work okay. Therefore, the problem must be linked to the [current_user_id] variable.

The cron job delivers a long message, the key lines at the beginning are:

<app_db_error>
<div style="color: #b94a48; background: #f2dede; border: 1px solid #eed3d7; padding: 5px; margin: 5px; font-family: verdana; font-size: 12px; line-height: 1.5;">
<div><strong>Database Error:</strong> 1064 - 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 '),1,0)
ELSE 0
END) as field_1311 from app_entity_63 e where e.id&gt;0 having ((f' at line 12411</div>
<div><strong>Query:</strong> select e.* , ((select fcv.value from app_fields_choices fcv where fcv.id = .........

The problem about seems to be right at the position where I use the [current_user_id] variable.
Thank you for looking into this issue.
Best, Pepe

[support]

[current_user_id] is not supported in cron job because if fact there is no any users when cron runs.

[pepe]

This is not exactly correct, because a user can be online while cron jobs are run, for example, every 5 minutes.
The point is: If there was an option to deal with the variable [current_user_id] then we could deal with the problem of record ownerships.

EXAMPLE:
In tasks table there can be 2 lists of users:
1. One user assigned to a field called "Responsible" (Dropdown). This user will be responsible to get the task solved and get everything done in time. Fill in or confirm some start and fish dates etc.
2. None, one, two, or more users are assigned to a field called "Participants" (MultiSelect). These users will be involved in order to get the task solved. Their actions are being monitored by user "Responsible".

For simplicity, assume all of the above users are contractors, they are assigned to user group "Contractor".
Only "responsible" contractors shall be able to edit certain fields in task table, "participants" get notifications etc. but cannot alter dates and other fields.

On next task, the scenario might be different: Another contractor may be in the position to be "responsible" while the one who has been "responsible" for first task may now only be "participant".

SOLUTION:
1. As I have implemented above, MySQL Formula can be used determine whether the current user is the one who has the role "responsible".
2. I next step I wanted to use field access rules. To achieve that I needed to setup an automatic status field which takes care to determine field access rules (they depend on fields like dropdown, radio buttons, or automatic status).
3. If current logged in user is "responsible" then automatic status would switch to status "1" and the corresponding field rules would fire. Otherwise the value would be "0" and not field access rules are applied.
4. Yes, there is the problem with time gap between login and next time around cron runs on the server. This gap allows to use the old settings (wrong field access rules).
4. Therfore, an appropriate solution was to run a similar routine like cron just once right after login. That way we could assign "ownership" to records and monitor all access settings accordingly.

Best regards, Pepe