on 04-17-2014 10:34 AM
Hi,
I need help to get all my sql result in .csv file.
My scenario:
I select a privilege --> Choose UI task to generate user list per privilege as .csv file
my sql request is working fine but I cannot generate the .csv correctly. It's empty.
function users_per_priv(Par){
Var userMSKEY= Par.get("MSKEY");
var SQL="";
var SQL = "SELECT
L.mcOtherMSKEYVALUE Privilege,
U.mcDisplayName DisplayName,
FROM idmv_entry_simple U inner join idmv_link_ext2 L on U.mcMSKEY = L.mcThisMSKEY
WHERE L.mcThisOcName='MX_PERSON'
AND L.mcOtherOcName='MX_PRIVILEGE'
AND L.mcLinkState=0
AND L.mcOtherMSKEY=" + userMSKEY;
var result="";
result= uSelect(SQL);
result = uReplaceString(result, "!!", "|");
return result;
}
My problem is how to get the result in .csv in To Ascii File pass, and to manage the delimiter correctly?
Many thanks,
Mia
Mia,
I am not somewhere where I can test this or get screenshots for you, but I'd consider something like this:
There's an option to set the delimiter called CSV delimiter in the ToASCII pass. In your screen shot it's set to ; just change it to ,
Matt
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Mia,
What Tero mentions is very likely the cause of the empty file problem. You're using the entry script as if it was an attributescript and this does not work. You need to add the result of your query to the Entry object that is returned from the script. See the helpfile and documentation for details.
An easier solution might be to remove the entry script setting and add it to the destination grid instead:
PRIVILEGE=%MSKEYVALUE%
DISPLAYNAME=%DISPLAYNAME%
MEMBERS=$function.users_per_priv(%MSKEY%)$$
(you might want to change the name of your userMSKEY variable to privMSKEY in the script to avoid confusion at a later time).
Another very much easier solution is to use:
PRIVILEGE=%MSKEYVALUE%
DISPLAYNAME=%DISPLAYNAME%
MEMBERS=%MXMEMBER_MX_PERSON%
And no script at all, but this only gives a list of the members mskeys.
Br,
Chris
Many thanks to all,
As you suggest it Per, I am using MEMBERS=%MXMEMBER_MX_PERSON%
I am getting the file as:
PRIVILEGE | DISPLAYNAME | MEMBERS |
PRIV:ADS123:PRIV123 | PRIV_ADS123_PRIV123 | 978|1234|2676|7686|6567|4234 |
How to format the output as:
PRIVILEGE | DISPLAYNAME | MEMBERS |
PRIV:ADS123:PRIV123 | PRIV_ADS123_PRIV123 | 9780 |
PRIV:ADS123:PRIV123 | PRIV_ADS123_PRIV123 | 1234 |
PRIV:ADS123:PRIV123 | PRIV_ADS123_PRIV123 | 2676 |
PRIV:ADS123:PRIV123 | PRIV_ADS123_PRIV123 | 7684 |
PRIV:ADS123:PRIV123 | PRIV_ADS123_PRIV123 | 6567 |
PRIV:ADS123:PRIV123 | PRIV_ADS123_PRIV123 | 4234 |
Thanks you very much,
Mia
If you want one line per member the implementation is easier if you do it as a job rather than a workflow action. The action will try to generate just one line of data.
Job with To Ascii pass
If you need this to be initialized by a user from the workflow then you need to look into creating a script that first retrieves the members, then loops through the resultset and gets the displayname and other data and writes it line by line using the uToFile function. The action task is by design unable to do this for you.
The query for reference:
select L.mcOtherMskeyValue Privilege,P.mcDisplayName PrivilegeDisplayName,L.mcThisMskeyValue "User",E.mcDisplayName UserDisplayName
from
idmv_link_ext_active L
inner join idmv_entry_simple E on E.mcmskey = L.mcThisMSKEY
inner join idmv_entry_simple P on P.mcmskey = L.mcOtherMSKEY
where mcOtherOcName='MX_PRIVILEGE' and mcOtherMSKEYValue = 'PRIV:ADS123:PRIV123'
Br,
Chris
Hi,
if you change:
result = uReplaceString(result, "!!", "|");
to:
result = uReplaceString(result, "!!", "\n;");
didn't have a chance to test it but if you're expecting to get two columns in CSV, this would add a new line for each member and add empty cell for the first column (privilege name).
regards, Tero
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.