nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Help needed to configure "DQL Read activity" followed by "SMTP activity"

athati

Hi,

I am trying to configure "DQL Read activity" (of Process Builder) in my workflow.

Under DQL Configuration how exactly the DQL goes in to "DQL To Run: " field? Next to my "DQL Read activity", I have "SMTP activity". The DQL activity must generate a variable consisting of all email addresses and pass on to "SMTP activity".

My DQL is as follows:
select user_address from dm_user where user_state=0 and user_name in (select i_all_users_names from dm_group where group_name like 'ecm_%')

I tried the following way:

I put the DQL in "DQL To Run: " field, validated and clicked on Next. I see user_address as a Collection Object. From here I need to pass on all values in this collection object to SMTP activity's "To: " filed. How can i do this. I am facing difficulty at this step. Please help me.

Please share your knowledge and experiences for figuring this out.

Thanks.

Find more posts tagged with

Documentum

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Button

Accepted answers

CláudioGil

First, the join function. You use the join function to take N values (repeating attribute, for example) from the source and put 1 value in the destination. You connect the join function to the left and right, double click it to add a second argument with the +. I you add / as the second argument, for example, all values will be joined together and a / will be placed between each of them.

Now, in the example we are using, you do not need the join function because you are always taking N values from the source and puting N values in the destination.

I took the time to setup a DQL Activity, here is the layout you need:

I really cannot help you more than this. You must take a step back and think what you want to do and what each pice does.

Also, it would be nice if you marked any post as answered or comment as helpful...

All comments

CláudioGil

You basically have 3 options:

Map the addresses to a basic STRING process variable with a Join operator using the addresses obtained from the query and a separator like ";" and then use the Split operator to map the process variable to the mail recipientes.
Map the addresses to the object_name for an attachment, for example, and then map that property back to the mail recipients.
- In this scenario attachments will be created for each mail address and you may need to clean them afterwards, when the workflow ends.
Map the addresses to the repeatable property of a custom structured data type.

Cheers,

Cláudio

athati

Thanks Claudio,

I am thinking of going for the 1st approach, but I am trying to understand how I can map the addresses. Where can I find Join operator, Split operator etc? It would be a GREAT help if you give the details step by step along with which document I can find the information.

Thanks.

CláudioGil

I'm using "operator" but maybe the training or documentation uses other

name for it; "function" maybe...

If you configured a DQL Query activity, in a process, you have seen them in

the data mapping step in the top drop down. The rest is using the tool and

not really solving a problem. You should also know how to get the documents

for each product and you even have integrated help in tools like Process

Builder.

athati

Thanks Claudio.

I am using Process Builder 6.7.

in DQL Configuration of "DQL Read activity" using "Join" function I could map 'user_address' into a variable $A then into 'subject' attribute of the attachment. The value from 'subject' I could copy (using 'Copy' function) into "To" filed of SMTP activity. It is working fine, but sending only one email where as my DQL gets more than one emails. I think I am pulling only one. How can I pull all email addresses from the Collection 'user_address'? You mentioned in your first post that I need to use separator like ";", but I am not able to figure it out how I can use and get all email addresses in the Collection 'user_address'.

Please advise me.

Thanks.

CláudioGil

Nota that in "join" and many other functions you can double-click the

function and add fixed arguments if the function supports it. In the "join"

you can add the separator that way.

Anyway, "join" takes N values and creates 1. So you are taking N mails and

creating 1 attachment which subject is the "join" of all addresses. If you

are using attachments you should use "Copy" to copy N values to N

subjects/attachments.

athati

Thanks Claudio.

I could add a seperator ";" after $A. I can add as many as I need, but I do not know how many user_address's are there in the Collection "user_address". Where can I stop?

Please advise me.

Thanks.

CláudioGil

You could figure it out with some trial and error. You just need 1

separator. The first argument is the repeating attribute and the separator

to put between each value. So "join" with a repeating value and ";" (do not

include the " in the configuration) produces 1 string with values separated

by ; like "a@b.com;c@d.com;...".

athati

Thanks Claudio.

I would like to let you know that still I am trying by trial and error.

If you have any other clue, please let me know.

Thanks.

CláudioGil

I'm probably not understanding your problem because you should be able to get what you want. If you use the attachments you have a sequence like:

DQL Activity
- DQL: "select user_addresses ..." (the query you mentioned before)
- Output message mapping:
  - Function: copy
    - Source: user_addresses
    - Destination: /attachments/attachment[0].subject
<other activities>
SMTP Activity
- ...
- Input Message mapping:
  - Function: copy
    - Source: /attachments/attachment[ALL].subject
    - Destination: EMailMessage.To[0]

So you don't use the join function at all because you store the email addresses in multiple subjects (the attachment's subjects). You would only need the join if you wanted to store the addresses in a single value, like a string process variable.

athati

Thanks Claudio.

I did all the 3 steps you mentioned exactly. MyCollection of email addresses (the attribute 'user_address' of dm_user) must have more than one address, but what I am getting into

Destination: EMailMessage.To[0]

is just one email address.

I am not able to understand clearly what you say last:

So you don't use the join function at all because you store the email addresses in multiple subjects (the attachment's subjects). You would only need the join if you wanted to store the addresses in a single value, like a string process variable.

Do I need to use join function for each email address? I do not know how many email addresses are there in the collection.

Please advise me. still i am trying to understand you last statement about 'join' function.

Thanks.

CláudioGil

Now, in the example we are using, you do not need the join function because you are always taking N values from the source and puting N values in the destination.

I took the time to setup a DQL Activity, here is the layout you need:

I really cannot help you more than this. You must take a step back and think what you want to do and what each pice does.

Also, it would be nice if you marked any post as answered or comment as helpful...

athati

Thanks Claudio.

I did the same. Please see the attachment.

Thank you for your help and sorry to bother you. I know you are trying your best, but I am not able to figure it out, becasue I still think I am doing exactly the same way.

I will redo everything now.

CláudioGil

Yes, with screenshots it is much easier. Your difference is that you are still using the "join" function instead of a "copy" function in the DQL activity. Notice the litle icon has two "legs" in the left and 1 in the right (hover mouse or double click it and you will see the name). You need a "copy" function in this scenario, as mentioned.

You also need to use attachments instead of a package. I don't even know if multiple documents will be created (for each of the addresses). Also packages are used for more persistent and important data. Information that is relevante to the user during the process. This information (the addreses), for example, should even be cleared after you sent the email.

athati

Thanks Claudio for figuring it out.

I used "join' function because input has multiple email addresses and 'copy' function is not taking 'user_address'es as input and 'subject' as output. Please see the screen shot and advise me.

Thanks.

CláudioGil

You did not read all that I wrote:

Claudio Gil wrote:You also need to use attachments instead of a package. [...]

When you use a package you have 1 object, only 1 object, only 1 subject. Copy fails because it does N to N or 1 to 1. Not N to 1.

Use attachments/attachments[0].subject as destination and it will work because (and this is the only thing you could not know), by default, an attachment is created for each value.

athati

Thanks Claudio.

I used 'Copy' function and Attachments[0].subject, but still I am not getting. Please see the attachment and advise me where I am doing wrong.

Thanks.

CláudioGil

Did you also change the mapping in the SMTP activity?

athati

Thanks Claudio.

You are GREAT !!! It worked. I am a fool to forget such an simple issue.

I never forget your help.

Thanks thanks again and again.

athati

Claudio,

Currently I am pushing all the 'user_address'es into package's 'subject' attribute in DQL activity. then I am pushing 'subject' into "To" filed of SMTP activity. This is working fine, but is it a good idea to use 'subject' attribute? Instead we are thinking of using a temp_attribute of the attached document in the workflow, but the temp_attribute of my custom_type when attached to the 'copy' function, the 'copy' function is not being validated. I see red cross mark when temp_attribute is linked to 'copy' function's icon.

Please advise me.

Thanks.

CláudioGil

Well, probably the temp_attribute is a single-value attribute and not a repeating-attribute, therefore not being able to receive the multiple values form the "copy".

Since you think using the "subject" is not the best way I would suggest using process variables. Create a new custom structured data type, with a repeating attribute, and define a process variable with that type. Then you can map the addresses into the repeating attribute of the variable. This will change no object in the repository (temporary or not).

athati

Once again THANK you for the help Claudio. It worked.