Is automatic Run Time Programmatic Date Selection possible

Jenni Atkinson
Jenni Atkinson
Facilitator 1 First Anniversary
in SKY Developer General Discussions

Hello erudite colleagues,

I am trying to automate some queries for a reporting task I have been given and I need the queries to be dynamic as in I want to supply dates INTO the query at run time programmatically, so I can run a bunch of them and harvest the resulting data. I don't want to use the ASK syntax as I want the dates to be computed at run time based on the date of the query being run. Is this possible using SKY API and Power Automate wizardry perhaps or will I be better off extracting out the data into my Azure Data Warehouse and doing some good old fashioned SQL Server queries to get the info I am after ?

Any wisdom gratefully received.

Regards, Jenni in Canberra Asuatralia

Answers

  • Erik Leaver
    Erik Leaver Blackbaud Employee
    Tenth Anniversary Kudos 5 First Reply Name Dropper

    Hi Jenni,

    Assuming this is for RENXT/FENXT this is possible. You can create the queries in RENXT/FENXT and then use Power Automate to run the queries. Or you can use Power Automate to build an ad-hoc query with your set parameters.

    Here are some sample templates from our Showcase:

    Get Query Results (takes results of query and saves to OneDrive or Google Drive)

    Get Multiple Query results with Query Connector & SharePoint List (uses a List of Query IDs to run multiple queries at once and saves output to Sharepoint)

    Ad-hoc Query API for NVA and RNE (good example of how to run an ad-hoc query. Example saves data into Azure data warehouse table)

    Let us know how the project goes!

    Erik

  • Alex Wong
    Alex Wong Community All-Star
    Tenth Anniversary Facilitator 5 Kudos 5 bbcon 2025 Attendee Badge

    @Jenni Atkinson As Erik has pointed out, you can run Ad-hoc query (which allows you to dynamically "create" a query definition in JSON to run and get results.

    However, depending on what kind of reports you are trying to create from the queried data, you may need to use a combination of query (or multiple queries) or direct SKY API list endpoint to handle various one-to-many properties. (i.e. one gift has multiple gift splits for campaign/fund/appeal/package information). You will get duplicates rows for every one-to-many relationship, so depending on the results you want to get for the report, you may need to do some manipulation within power automate.

  • Jenni Atkinson
    Jenni Atkinson
    Facilitator 1 First Anniversary

    Thanks Alex - wise advice !

  • Jenni Atkinson
    Jenni Atkinson
    Facilitator 1 First Anniversary

    Thanks Erik !

Categories