matt_stratton Absent Member.
Absent Member.
10019 views

Get results from JOIN query in AppScript?


I'm trying to do some logic on a transition using AppScript based on the user's role. This is the SQL query that returns the values I need:

select g.ts_name as groupname, r.ts_name as rolename, sc.TS_SUBJECTTYPE

from ts_securitycontrols sc

inner join ts_users g on sc.ts_subjectid = g.ts_id and g.ts_status = 0

inner join ts_roles r on sc.ts_permissionid = r.ts_id

inner join ts_projects p on sc.ts_contextid = p.ts_id

where sc.ts_subjecttype = 1

AND

r.TS_NAME like 'IM Administrator'

AND

g.TS_NAME like 'Stratton, Matt'

For example, that will return rows if the User "Stratton, Matt" has the role "IM Administrator".

However, I cannot figure out how to get this into AppScript using AppRecordList as it only takes the WHERE clause of the SQL query. How can you get at the results of a join query in AppScript?
Tags (1)
0 Likes
4 Replies
Not applicable

Re: Get results from JOIN query in AppScript?


you'll most likely have to use this as a subselect such as where TS_NAME = (Select etc..)
0 Likes
matt_stratton Absent Member.
Absent Member.

Re: Get results from JOIN query in AppScript?


That makes sense.

However, I just realized that this might not accomplish what I want to do. The theory is to have a different Incident submit form based on the user role (regular users get a subset of CI's to choose from, IT people get them all).

However, since in SRC I have to specify the form used by the item, I don't think it will work to do it this way, as I think SRC loads the form regardless (my idea was to put a transition before submit that shoved a value into "IT submit" field and then had a rule, "if it submit then load this form, otherwise the other").
0 Likes
Not applicable

Re: Get results from JOIN query in AppScript?


another idea is to use JavaScript look up current user find their group then display appropriate tab based on group. I think there's some code in this forum where that is being done
0 Likes
matt_stratton Absent Member.
Absent Member.

Re: Get results from JOIN query in AppScript?


I was thinking about doing that. Right now the way we handle the limiting of the CI field is via a JavaScript that is bound to document load via jquery. I could conditionally fire that javascript if the user is not IT.

Of course, the problem is that that field is over-ridden to be a dropdown, and when it's an IT person it needs to be a searchable field.

What really needs to happen is for SRC/SSM to support multiple submit forms for Incident the same way they do for Service Request.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.