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; } } }

RE and Power Platform - prospect history API

Pippi Reader

Hello

I heard that on RE NXT, they have released the prospect history API endpoint recently which includes days lapsed which is something I have been desperate for. I'm new to linking RE with Power BI though and I'm not sure how I set this up so that I can get the information flowing from RE to Power BI, does anyone have any information that might help me? I've set up the Blackbaud Connector but I don't think you can access this from there can you? @Alex Wong I know you often have some useful posts on this so wondered if you had any advice? Thanks

https://developer.sky.blackbaud.com/api#api=56b76470069a0509c8f1c5b3&operation=GetConstituentProspectStatusHistories

Find more posts tagged with

Microsoft Power Platform

Comments

Alex Wong

@Pippi Reader
Since Get Prospect History is per single constituent, not ONE call to get all prospect history of all constituent, you are going to run into trouble with Power BI connector, assuming that you are using the unofficial Blackbaud connecto supported by the community.

Assuming you are not trying to just get ONE constituent's prospect history, rather all (or at least a bunch of them) for reporting, use of a “iterator” in power query potentially can be created in the BI connector, however, isn't very “friendly” (at least in my opinion, since I have not seen anyone actually done it).

Using Power Automate will help do this, though still “one at a time”. Depending on how many constituent you are potentially trying to get prospect history, you can create a power automate flow that either does all constituent (that has a prospect status to get history for), or take in a bunch of constituent system record id, and get prospect history for them only. In Power Automate, you can run using concurrency, making it much faster to retrieve even though still only ONE at time. then the flow either save the data into a cloud location (i.e. SharePoint list or excel) that Power BI can read from, or directly pass the data into Power BI.

Pippi Reader

@Alex Wong great thanks, I'll try that

Jessica Kalista

@Pippi Reader did you get this figured out? I'm BRAND NEW to power automate and don't really know where to start with this one. Prospect Status History is one thing that we've talked about for years and are having to go manually go to records to find the information we need. I would love to be able to figure out how to set up a flow, from start to finish, that can help pull some of this data for me. Any thoughts?

Alex Wong

how many constituent you have prospect status for in your database.

less than 50K, should not take too long to get it all.

1s per call to get status, and 1s to process (estimated). so 2s per constituent = 100,000s

running with concurrency of 5 would be 20,000s which is ~5.5 hour.

once you have all the status, you can handled new info by using webhook or based on constituent modified since date/time.

Jessica Kalista

We have less than 3k constituents with a prospect status. My current problem is I just don't know where to start with Power Automate to pull the information out.

Alex Wong

when you say "pull the info out" where you intent to store the info

Jessica Kalista

I think the hope is to have it stored in a SharePoint list that could be refreshed as statuses change.

Alex Wong

3k…2s each constituent = 6000s, 5 concurrent runs = 3000s which is ~50min. Very doable.

Power Automate flow to get initial set

Trigger = Manual
Get Constituent List, make sure limit is set to 5000, since you only have 3000, there will be no looping issue that you need to deal with
Apply to each constituent in list (go into setting of the action and set concurrency to be 5 or 8)
- get prospect status (or get prospect status history if you want the full list of history)
- add the data to SP list (you will need to do another apply to each IF you get prospect status history)

Power Automate flow to get update

Trigger = scheduled (every X hour or every X day)
Get Constituent List, make sure limit is set to 5000, and last modified is from when you last run (this depends on your schedule above)
Apply to each constituent in list (go into setting of the action and set concurrency to be 5 or 8)
- get prospect status
- delete from SP list the constituent previous data
- add the data to SP list

I understand you are new to Power Automate, so this may be a little too much for you to begin with, so you may want to start with something simpler by copying and using a template in template showcase and play around with it and get familiar with power automate first. I may create a template for doing this maybe next month, but don't hold your breathe =D

I am available as a consultant if you want to just get someone to do this for you instead, email alex.wong@ffxconsulting.co if interested.

Jessica Kalista

Thank you so much, Alex! I will poke around in the showcase and see what I can do. Much appreciated!