Opportunity ID

Pippi Reader

Hello

I am currently in the process of linking RE to Power BI. I have created queries in RE NXT with the data I need and this is pulling through to Power BI using this method from another forum topic:

We've been working on setting up some of these but have come across an issue with one of my queries - Opportunities. I want to be able to have the opportunity ID included in my query but Query doesn't allow you to get this Opportunity ID. Other than using the Sky API connector where you can get this information (I'd prefer to just use the above rather than involving two different ways to connect the two systems) is there a work around for this?

I wondered if anyone had set up a Power Automate flow that pulls the Opportunity ID that is automatically created when someone creates an Opportunity and then uses Power Automate to paste that value into a field in Opportunity that isn't being used which can be pulled in a query. @Alex Wong do you know of a workaround for this?

Hallie Guiseppe

@Pippi Reader
Hi Pippi,

We use opportunities heavily at Alvernia to feed projections into our campaign dashboards which are Power BI reports. I use the connector because it gives you access to the all the information on an opportunity.

Your proposed solution (appending the opportunity ID as an attribute) is the best option I can think of if you are unable to use the connector in Power BI.

Pippi Reader

@Hallie Guiseppe thank you! I have the connector but because I am using the other way to pull all the other data, I was hoping to not have data being pulled in two different ways but perhaps I am over thinking it. This is good to know. Thank you

Hallie Guiseppe

@Pippi Reader
Actually that is one of the best things about Power BI. You can bring data in from multiple places and work with it all in the same dashboard. Good luck!

Alex Wong

@Pippi Reader
Unfortunately, Query API does not provide a field for Opportunity system record id, or any ID for that matter to specifically identify an opportunity record. So there is no way to query opportunity in query AND use opportunity list and “join” them in power bi.

You will have to use ONE or the other until Blackbaud allow for ID to be outputted from Query API. If ID is important (so you can “link” out from Power BI to the opportunity record on webview), then you will need to use the opportunity list api endpoint, BUT you do not “NEED” to use the unofficial custom connector with gateway. You can still use the template I shared that uses Power Automate, but instead of running a query and getting its result to pass back to Power BI, the Power Automate can run the opportunity list api endpoint and send the result back to Power BI.

Pippi Reader

@Alex Wong thank you!