mysql - Load data into dropdown based off company assignment

Scriptcase 8 Discussion
#1

Hello.

Trying to do a automatic lookup on my field “company” in a search application that looks up work order records.

I have two scenarios.

1st…If your logged in as the admin company “TCM” you should show see ALL companies in drop down list.

2nd…If your NOT part of admin company then it should only show you your own company name.

I have…

SELECT
company, company, companyID, active
FROM
tb_companies
WHERE
active = 1 AND company <> “TCM”
OR
(active = 0 AND ‘{company}’ = companyID)
ORDER BY
company ASC

This brings back all companies that are active status and not equal to the admin company “TCM”.

How can i do a SELECT statement that will show all companies if i’m logged in as “TCM” and if not show only the logged in company.

I have a global variable [usr_company] that tells me what company it is but I’m sure how to format the SELECT statement to check for this?

Thanks for any help.

#2

Do a dinamic filter. Change the where clause for [companiesfilter]. Now, set this global as out.
Now onAppInit base your logic to build the right where

#3

I think you can create a global variable for SQL statement when the user is logged.
if ([usr_company] == ‘TCM’) {
[My_sql_statement] = “Your sql for TCM”;
} else {
[My_sql_statement] = “Your sql for user”;
}
and then this variable global [My_sql_statement] assign to your drop down in automantix SQL.

#4

Thanks guys…So…

Do I do the sql statement in the “Search Lookup” of the field?

I get the If statements that Alvagar shows but not sure what the sql statements would look like in his example?

Can you give a example?

Thanks really appreciate it.

#5

Your sql can do it in event onapplicationinit. [ATTACH=CONFIG]n66293[/ATTACH]

lookup_search.PNG