Prospect Status and Days Elapsed

Edward Hohlbein

Howdy good folks!

I'm trying to get Prospect Status and Elapsed Days out of RE NXT using Power Automate. I'd like it in CSV or Excel. Below is what I have so far.

When I add the Constituent ID from the list in the first action into the Get constituent prospect status it creates an Apply to Each. Then later in the flow I'm not able to grab it in my Select action.

I've tried this about a gazillion different ways and haven't hit on the right combo.

If anyone can tell me what I'm doing wrong and offer some guidance, I sure would be grateful.

Thank you!

Ben Regier

@Edward Hohlbein
Because you need to make an additional call for each Constituent, you're going to need to construct your array manually inside of your Apply to Each loop instead of using the Select action. Do you have experience writing JSON?

Edward Hohlbein

Thank you @Ben Regier! This was so helpful. Really appreciate you.

Okay This is where I got today. I successfully have the column headers I want (for now) in the CSV, but ONLY the headers and not the actual data.

Any guidance you are willing/able to provide would be so awesome. I'm just about there it feels.

Ben Regier

@Edward Hohlbein
It's difficult to see for sure what you're doing because the screenshot get shrunk so much, but it looks like you're using a CSV Table action inside of your Apply to Each loop, and trying to set the source to a variable. I'm afraid this method won't work either, for a number of different reasons.

What you're trying to do seems like it should be simple, but it's deceptively complex in Power Automate. I don't know of a way to do it without building your array manually in JSON. I'll try to outline how that would look.

First, you want to initiate an array variable at the top of your flow. Leave it blank for now.

Next, add an Append to Array action inside your Apply to Each loop, after you retrieve the prospect information.

What I'm doing here is basically using JSON to build a “row” in the table that you'll eventually put into your CSV action. Because I'm inside the Apply to Each loop, I can access data from my “Get constituent prospect status” action, and I can also access data from the “List constituents” action.

The text that is in quotes will become the column headers once you turn this into a CSV. So the format is like this:

{

“Header name 1”: “[Dynamic Content 1]”,

“Header name 2:” “[Dynamic Content 2]”

}

Note that each attribute has a comma at the end, except for the last one.

Now, all you have to do is use your array variable to create a CSV table. Because the header information is already contained in the JSON, you don't have to do anything with headers in this action.

Then save or email the file or whatever you need to do with it. Hopefully that all made sense.

Edward Hohlbein

@Ben Regier!!! You are the absolute BEST! I can't tell you how much I appreciate you. This worked perfectly. Please know how thankful I am. ????? Have a GREAT weekend!