[Solved] Help me to filter log by id

Scriptcase 9 General
#1

Hi,

I manage information sheets on users. When I edit a form, I want to be able to display (master/detail) in a tab, the logs specific to the current form. How can I filter the content of the sc_log table according to the ID of the current record?

Example of the content of my sc_log table:

sc_log_example
sc_log_example591×596 15.1 KB

Query in my “ap_log” grid:

SELECT id, inserted_date, username, application, creator, ip_user, `action`, description
FROM sc_log
ORDER BY id DESC

I don’t know what to write in my WHERE query?

Maybe with the “sc_select_where” macro and the preg_match function?

Thanks

#2

Finally I think I will create a blank application because I have a lot of master/detail type forms that depend on my information sheet, and the IDs are different in the description column.

#3

Also, other idea :

  1. Use initial query (get all data)
  2. Add new column with ID
  3. Filter on this new column

What you say about it ?

Thanks

#4

description like '%id : [ID]%'

1 Like
#5

Oh yes! As easy as that!

On the other hand, in this field, I have IDs for my user sheet, but also IDs for other related content.
So it won’t work, thank you anyway

#6

You can also filter the other content at the same time

description like '%id : [produitID]%' and description like'%filter_for_user_sheet%'

2 Likes
#7

hmmm, It would rather be something like:

If (application = form_user) description like '%id : [ID]%' 
if (application = form_notes) description like  '%id : (select id FROM users_notes WHERE userid = [ID])%' 
if (application = form_ref) description like  '%id : (select id FROM users_ref WHERE userid = [ID])%'

I do not have the choice to go through a blank application? or filter by after?

Thanks

#8 
(application = 'form_user' AND description like '%id : [produitID]%' ) OR
(application = 'form_notes' AND description like concat('%id : ',(select id FROM users_notes WHERE userid = [ID]), '%')) OR
(application = 'form_ref' AND description like concat('%id : ',(select id FROM users_ref WHERE userid = [ID]), '%'))
3 Likes
#9

Hmmm so simple and interesting, thanks

#10

Merci Jean-Luc ! Ça marche super bien ! hihi

#11

Hi,

Finally I have another problem. All my subqueries are returning multiple values. Do you have any idea how I could change my SQL query?

Erreur
Erreur d'accès à la base de données :
Subquery returns more than 1 row
select count(*) from ( SELECT id, inserted_date, username, application, `action`, description FROM sc_log WHERE (application = 'formulaire_usagers' AND description like '%id : 8563|%') OR (application = 'formulaire_notes' AND description like concat('%id : ',(select id FROM notes WHERE usagerid = 8563), '%')) OR (application = 'formulaire_gouvernement' AND description like concat('%id : ',(select id FROM gouvernement WHERE usagerid= 8563), '%')) OR (application = 'liste_references' AND description like concat('%id : ',(select id FROM org_references WHERE usagerid = 8563), '%')) OR (application = 'Formulaire_troudeservice' AND description like concat('%id : ',(select id FROM troudeservice WHERE usagerid = 8563), '%')) OR (application = 'liste_famille' AND description like concat('%id : ',(select id FROM coordo_famille WHERE usagerid = 8563), '%')) OR (application = 'liste_fichiers' AND description like concat('%id : ',(select id FROM fichiers WHERE usagerid = 8563), '%')) OR (application = 'liste_intervenant' AND description like concat('%id : ',(select id FROM coordo_intervenant WHERE usagerid = 8563), '%')) ) nm_sel_esp

Initial request :

	SELECT
   id,
   inserted_date,
   username,
   application,
   `action`,
   description
FROM
   sc_log WHERE (application = 'formulaire_usagers' AND description like '%id : [userID]|%') OR
(application = 'formulaire_notes' AND description like concat('%id : ',(select id FROM notes WHERE usagerid = [userID]), '%')) OR 
(application = 'formulaire_gouvernement' AND description like concat('%id : ',(select id FROM gouvernement WHERE usagerid= [userID]), '%')) OR 
(application = 'liste_references' AND description like concat('%id : ',(select id FROM org_references WHERE usagerid = [userID]), '%')) OR 
(application = 'Formulaire_troudeservice' AND description like concat('%id : ',(select id FROM troudeservice WHERE usagerid = [userID]), '%')) OR 
(application = 'liste_famille' AND description like concat('%id : ',(select id FROM coordo_famille WHERE usagerid = [userID]), '%')) OR 
(application = 'liste_fichiers' AND description like concat('%id : ',(select id FROM fichiers WHERE usagerid = [userID]), '%')) OR 
(application = 'liste_intervenant' AND description like concat('%id : ',(select id FROM coordo_intervenant WHERE usagerid = [userID]), '%')) 
ORDER BY 
    id DESC

Thanks

#12

Do you want them to return only one or they have to return more than one?

#13

Return all data.

I understand that for a single result, there is a row in the sc_log table

#14

I will have to think about it

#15

Thank you. I am testing by adding a new column ID_USER and I will try to apply a filter when displaying :

$log_output = sc_log_split({description});
if (is_array($log_output)) {
	{ID_USER} = $log_output['keys']['id'];
	if ({application} == "liste_references") {
		$sql = "SELECT usagerid FROM org_references WHERE id = {ID_USER}";
		sc_lookup(rs, $sql);
		{ID_USER} = {rs}[0][0];
	}
	if ({application} == "liste_famille") {
		$sql = "SELECT usagerid FROM coordo_famille WHERE id = {ID_USER}";
		sc_lookup(rs, $sql);
		{ID_USER} = {rs}[0][0];
	}
#16

After several tests, it is not possible to apply an advanced filter on a field added dynamically and generated from php code.

So I will opt for a new blank application.

Thanks

EDIT: First problem, I can’t call a blank application from another master/detail form!

#17

Hello,

Finally, I will stay with the original request. I realize that if I display the changes of the other related forms, it will be too much data

Thanks