PS Query & SQL

Can you provide more concrete details?  Can you provide an example of the expression you are thinking of?

John,

I'm interested to see an example of what you have in mind, but I have worked on queries with monstrous (and repeated) expressions that do refer to prompts with default values. It's ugly, but easier than updating a large number of expressions and trying to keep everything synced up.

I'm not aware that PS Query supports the creation of variables like you're asking. However, Michael Williams at UConn gave a presentation at Alliance 2025 called "Prompt and Circumstance" in which he described tying variables in queries to the Field Conversion Definition tables. That's a place where you can save variables with field names and values in a static place, then make updates as you see fit. It takes some setup, but he demonstrated it as a way to update the references in an unlimited number of queries all at the same time (e.g. save all your odd year values and your even year values in one place, update the references when it's time, and now all your "odd" queries go from 2025 to 2027). That might scratch the itch you're describing!

John,

I'm interested to see an example of what you have in mind, but I have worked on queries with monstrous (and repeated) expressions that do refer to prompts with default values. It's ugly, but easier than updating a large number of expressions and trying to keep everything synced up.

I'm not aware that PS Query supports the creation of variables like you're asking. However, Michael Williams at UConn gave a presentation at Alliance 2025 called "Prompt and Circumstance" in which he described tying variables in queries to the Field Conversion Definition tables. That's a place where you can save variables with field names and values in a static place, then make updates as you see fit. It takes some setup, but he demonstrated it as a way to update the references in an unlimited number of queries all at the same time (e.g. save all your odd year values and your even year values in one place, update the references when it's time, and now all your "odd" queries go from 2025 to 2027). That might scratch the itch you're describing!

Hi Timothy,

Can you point me to the session you are referring? I looked but can't find it. Unless the presentation didn't get recorded and/or the power point wasn't posted.

Thanks,

Lara

John,

I'm interested to see an example of what you have in mind, but I have worked on queries with monstrous (and repeated) expressions that do refer to prompts with default values. It's ugly, but easier than updating a large number of expressions and trying to keep everything synced up.

I'm not aware that PS Query supports the creation of variables like you're asking. However, Michael Williams at UConn gave a presentation at Alliance 2025 called "Prompt and Circumstance" in which he described tying variables in queries to the Field Conversion Definition tables. That's a place where you can save variables with field names and values in a static place, then make updates as you see fit. It takes some setup, but he demonstrated it as a way to update the references in an unlimited number of queries all at the same time (e.g. save all your odd year values and your even year values in one place, update the references when it's time, and now all your "odd" queries go from 2025 to 2027). That might scratch the itch you're describing!

Hm... it looks like there's an issue accessing the 2025 materials. I'd expect to see them here https://www.heug.org/resources/heug-files/a25-files, but it's taking me to a page to buy an on-demand pass for 2026 instead. I've sent an email to support.

Hi Timothy,

Can you point me to the session you are referring? I looked but can't find it. Unless the presentation didn't get recorded and/or the power point wasn't posted.

Thanks,

Lara

John,

I'm interested to see an example of what you have in mind, but I have worked on queries with monstrous (and repeated) expressions that do refer to prompts with default values. It's ugly, but easier than updating a large number of expressions and trying to keep everything synced up.

I'm not aware that PS Query supports the creation of variables like you're asking. However, Michael Williams at UConn gave a presentation at Alliance 2025 called "Prompt and Circumstance" in which he described tying variables in queries to the Field Conversion Definition tables. That's a place where you can save variables with field names and values in a static place, then make updates as you see fit. It takes some setup, but he demonstrated it as a way to update the references in an unlimited number of queries all at the same time (e.g. save all your odd year values and your even year values in one place, update the references when it's time, and now all your "odd" queries go from 2025 to 2027). That might scratch the itch you're describing!

Hey All - I was able to get to the Prompt and Circumstance presentation.  Here is what I did.

I used the following URL to get to the Alliance Mobile site.  https://events.rdmobile.com/Home/Details/117 

1. After clicking the link, click the Login link in the upper right corner.  
2. Add your email address.Click the Login button. 
3. You will receive a email with a link in it. 
4. Click the link, and you will be logged into the mobile Alliance site. 
5. Click the Continue button. 
6. Click the All Events link. 
7. Choose the conference 
8. Use the navigation pane on the left side of the page to find presentations, presenters, etc. 
9. I have found searching for the Presenter the easiest way to find a presentation. 

Hm... it looks like there's an issue accessing the 2025 materials. I'd expect to see them here https://www.heug.org/resources/heug-files/a25-files, but it's taking me to a page to buy an on-demand pass for 2026 instead. I've sent an email to support.

Hi Timothy,

Can you point me to the session you are referring? I looked but can't find it. Unless the presentation didn't get recorded and/or the power point wasn't posted.

Thanks,

Lara

John,

I'm interested to see an example of what you have in mind, but I have worked on queries with monstrous (and repeated) expressions that do refer to prompts with default values. It's ugly, but easier than updating a large number of expressions and trying to keep everything synced up.

I'm not aware that PS Query supports the creation of variables like you're asking. However, Michael Williams at UConn gave a presentation at Alliance 2025 called "Prompt and Circumstance" in which he described tying variables in queries to the Field Conversion Definition tables. That's a place where you can save variables with field names and values in a static place, then make updates as you see fit. It takes some setup, but he demonstrated it as a way to update the references in an unlimited number of queries all at the same time (e.g. save all your odd year values and your even year values in one place, update the references when it's time, and now all your "odd" queries go from 2025 to 2027). That might scratch the itch you're describing!

You might be able to leverage process parameters, as they are query-able. Sort of depends on the requirements, but works well when you have lists, such as academic programs/plans or item types.

Hey Alex would you mind expanding on that? Are you talking about a specific page in PS? Quick google didn't make it clear to me - thanks!

You might be able to leverage process parameters, as they are query-able. Sort of depends on the requirements, but works well when you have lists, such as academic programs/plans or item types.

You might be able to leverage process parameters, as they are query-able. Sort of depends on the requirements, but works well when you have lists, such as academic programs/plans or item types.

Hi guys, the summer session of my presentation was open to everyone I'll attach that and the example SQL.