Saving Searches with Multiple Record Types

Shannon Brower

I am trying to create a search in order to create a new tab on my dashboard that lets me know when a specific type of grant request is approved and waiting to be paid. It seems when I create a query builder, it allows me to specify the filters for the Request but doesn't allow me to add that Payment is "missing" along with it. The Advanced filters only allow for specifics about payments already made. I am working as a team on approving requests and once they are approved they disappear into the abyss of Grant Tracking. Is there another way to set up the new tab to show a request is approved but still needs to be paid? Hope this makes sense to someone.  

Paula Lentoni

Hi Shannon,

 

I think you need to build the search from the
payment section.

 

This is where you can use the advanced search
feature to find the status of the request.

 

 

 

Paula
Lentoni

Grants
Manager

She/Her/Hers

 

Tel.
617.927.5731

Fax 617.
927.5710           

plentoni@mottphilanthropic.com

 

Mott
Philanthropic

Prudential
Tower, 800 Boylston Street, Suite 1560, Boston, Massachusetts
02199-8129

www.mottphilanthropic.com

 

 

*************************************************************************************************************************************

This
communication may contain information which is privileged and/or
confidential under applicable law. Dissemination, copy, or
disclosure, other than by the intended recipient, is strictly
prohibited. If you have received this communication in error,
please immediately notify us via return e-mail to plentoni@mottphilanthropic.com
and
delete this communication without making any copies. Thank you for
your cooperation.

 

 

Shannon Brower

I initially thought that too, but the Payment search only asks specifics about payments already made.

Sheree Norton

Have you tried one of the Reports to get the information you need? I use Payments Due Schedule, By Month and then filter it by program or type e.g. scheduled, contingent. I was also able to come up with a dashboard after working with this canned report.

Shannon Brower

Hi Sheree,


That's a great way to filter the requests but how do you build a dashboard with a report from the Report Manager? It isn't a saved search. I appreciate your help!

Sheree Norton

I don't think I created it from the actual report. I created a search in payments with a Schedule Date before 6/30/2020 AND (Status is 'Scheduled' Or Status is 'Contingent') you can make the date whatever you want. I use our fiscal end date so I can track payments due before the end of the year.


You can create the search in a basic search. Once you have that, you can create the view you want, e.g. add program area from the request's coding sheet, or type of support etc. allowing you to filter it in the dashboard or for reports.


Then you can create your report/dashboard.  

 

Kim Hook

It sounds like you need an advanced search to me. It can look for things that are missing.

Here's an example I use for Requests which are missing certain review stages: 
(NOT EXISTS(SELECT ID FROM Reviews WHERE Request_ID = Requests.ID AND Reviews.Stage_ID In (4,5,6,7,8,9)))


The easiest way to get your particular search would be to ask technical support for help.

Justin Finighan

Hi Shannon,

Happy to help out if you are stuck.

You can create searches for Dashboards that will show you:

• Approved Requests that have no Payments attached to them.
  
  This is a Request search: Requests.Disposition = 'Approved' AND Requests.ID NOT IN (SELECT Request_ID FROM Payments)
• If the payments do exist, you can search for payments that are due to be paid (specifying a timeframe, for example, due in the next month)

If you describe exactly what you need, I think these will be easy searches to create.


Cheers

Justin

Shannon Brower

Justin,

I tried that, but our "Search" is not set up to choose AND Requests.ID NOT IN (SELECT Request_ID FROM Payments). We can only choose "OR" and do not have Request_ID FROM Payments. I created a workaround to show "Grants to be paid" for my tab. It's not ideal but it works. Thanks!

 

Justin Finighan

Hi Shannon,

You need to copy and paste the red text into an Advanced Search (in the Full Search window, click Advanced at the bottom). Make sure you have Requests selected as the Record Type to search for. 


In the Advanced Search, you need to use the keyboard shortcut Ctrl-v to paste the text. (there is no paste button, and no right-click-paste option).


Cheers

Justin