Hi,
I have created a view that displays an Audit Trail of a Table down to a Record in that Table that's launched from UX via a button that uses the Open a UX Component Action Scripting feature, and it has two arguments, one is the Table Name and the other is the GUID for the current Record.
It works fine when launched from another UX, but I also want to be able to just load up the same UX without any filtering by Table or Record GUID (ie, see all of the Audit Trail records) and then let the user use the Search part of the List to find what they're interested in.
Here is the current SQL:
SELECT aud_action, aud_table, aud_field, aud_value_old, aud_value_new, usr_login, usr_fname, usr_mi, usr_lname, aud_new_dt, aud_ipaddress, aud_text, aud_fk_users, aud_guid, aud_fk_table
FROM vw_audit_table
WHERE aud_table = :AUDIT_TABLE AND aud_fk_table = :AUDIT_TABLE_FK
ORDER BY aud_new_dt DESC, aud_table, aud_field
I'm using MySQL, is there a way to add an IF statement in the Where Clause, on the order of IF(!empty(:AUDIT_TABLE),aud_table=:audit_table,aud_table<>'') and IF(!emtpy(:audit_table_fk),aud_table_fk=:audit_table_fk,aud_table_fk<>'')?
I'm transitioning from VFP where I could do something similar and haven't gotten comfortable with MySQL yet ... and I don't know if this is possible in AA, since the filters are added post creation of the view ...
Any help would be appreciated ...
I have created a view that displays an Audit Trail of a Table down to a Record in that Table that's launched from UX via a button that uses the Open a UX Component Action Scripting feature, and it has two arguments, one is the Table Name and the other is the GUID for the current Record.
It works fine when launched from another UX, but I also want to be able to just load up the same UX without any filtering by Table or Record GUID (ie, see all of the Audit Trail records) and then let the user use the Search part of the List to find what they're interested in.
Here is the current SQL:
SELECT aud_action, aud_table, aud_field, aud_value_old, aud_value_new, usr_login, usr_fname, usr_mi, usr_lname, aud_new_dt, aud_ipaddress, aud_text, aud_fk_users, aud_guid, aud_fk_table
FROM vw_audit_table
WHERE aud_table = :AUDIT_TABLE AND aud_fk_table = :AUDIT_TABLE_FK
ORDER BY aud_new_dt DESC, aud_table, aud_field
I'm using MySQL, is there a way to add an IF statement in the Where Clause, on the order of IF(!empty(:AUDIT_TABLE),aud_table=:audit_table,aud_table<>'') and IF(!emtpy(:audit_table_fk),aud_table_fk=:audit_table_fk,aud_table_fk<>'')?
I'm transitioning from VFP where I could do something similar and haven't gotten comfortable with MySQL yet ... and I don't know if this is possible in AA, since the filters are added post creation of the view ...
Any help would be appreciated ...
Comment