Database Considerations and Decisions

This section is for recording database decisions for administrators. Please review carefully to get context before making related system changes.

Grant Application Consistency

Background: There are certain types of information that we always want to collect, regardless of application type. Often, the goal is to provide reliable reporting references so WPP staff can easily query across time, program category, and application to answer basic questions like "How much money has been awarded for cancer-related topics in the past 5 years?"

Therefore, the following fields MUST be filled out on ALL applications. If someone requests these fields to be removed, do not fulfill the request until you've verified with WPP or come up with an alternative plan to collect the pertinent information. Similarly, do NOT update the fields on dropdown lists without investigating potential impacts. For example, even if you're simply combining two fields into one, you'll want to think about whether it's appropriate to update existing applications with either of the old topics based on reporting needs. Contact with the WPP researcher/reporting contact to verify changes (Lindsay Barone, as of 10/4/23).

When a new program is launched, you should also check to make sure these fields are all present on the application form.

Required fields (all applications):

• Project Summary (hard-coded Fluxx requirement. Applications cannot be advanced unless this field is filled in)
• Counties impacted - this is distinct from the organization counties. XXXI need to research this more and come back
• Primary Topic - we're currently using perc_primary_topic, I think. Check the most recent 2023 CIG 1&2 forms and the 2023 NIP form–it should be consistent with those XXXCome back and put the exact field name in here.
• All grants characteristics tracking (values for these fields were last updated 10/17 per Lindsay in prep for 2023 NIP full app):
  • Population Served
  • Ethnicity
  • Gender
  • Urban/Rural

Other changes to include (all applications):

• Reviewer Feedback - Full App - Previously, there was one field for reviewer comments to be shared with grantees. Going forward, that field (previously review_summary, now Reviewer Feedback - LOI and Historical) should be labeled Reviewer Feedback - LOI and the full app field should be added to all templates (Internal > Reviewer Comments section).

Review Records

Features that should be present on ALL Review records

• Send back to review button - In the Submitted state, make sure there's a button that allows any WPP internal staff to send a review from submitted back to pending
  • Sometimes reviewers will ask to adjust their reviews based on discussions about the various grants, which happen after they submit. Since it's a standard workflow, we don't want program officers should be able to do this without reaching out to an admin.

Custom Field Limits/Generic Fields

Although you can create as many fields as you like, creating too many will degrade system performance, not to mention making exporting data take forever. 

To prevent performance issues, do not create more than 150 custom fields per record type. To get the most out of existing fields, we have a policy of creating generic fields which can be reused for different purposes on different forms. The downside is that when exporting, we can't see what field name/prompt was so it's not apparent what information was intended to be collected on each form. 

If you ever need to find out what information was intended to be collected, you can take the following steps:

1. Export the field value for all records
   1. If the field is included in the default export card, you can just create a dashboard card with no filters and export Excel
   2. If the field isn't included as a default export, create an ad hoc report that returns the record ID, model theme name, and the field value
2. Filter results to find which model themes used said field and the creation date
3. Look up the affected themes (either via card filtering or the Admin Panel) and check the configured field name/surrounding text

There's more information littered across the WPP wiki about this issue, including, somewhere, a tracker (possibly on the N drive?). If you're ready to fall down the rabbit hole, here are a couple places to start:

• WPP Task List
• Generic and Custom Fields

Document Type Prefixes

You'll notice a lot of Grant document types have a prefix like F07A. The purpose of the prefix is to enforce a consistent sort order in the Document Group on application forms. Doc types without a prefix might be retired or intended for internal or ad hoc use (ex. MOUs). 

Doc sort order is controlled in the Admin Panel > Global Settings > General Settings in the Sort Documents By field (ctrl+f to find). We decided not to sort by file name due to the likelihood of error and to avoid burdening grantees. Also, we didn't use a simple 1, 2, 3 naming convention because not all document types are used for each application, which might spark some confusion for grantees.

The naming convention looks fancy but is pretty straightforward. Each code has 4 digits:

• Position 1: F for full application document or P for preliminary application document. The only form that currently falls into both categories is Confidentiality Form Upload, but since it belongs last in both lists I've kept it as P to make sure it goes on the end.
• Positions 2&3: The intended sort order, with a prefix of "0" for single-digit sort orders. For example, 01 for the first document.
• Position 4: O for OAC, P for PERC, A for all. This isn't doing much in terms of sorting, but it's helpful information that further obscures the sorting intention. Ideally, people's eyes will glaze over and they'll ignore the prefix altogether.
• Suffix: " - " make sure there a single space on both sides of the hyphen. Then, follow immediately with the doc type name.

Examples: F12A, P01O, etc.

Permissions

If a user says they can see a record but can't edit, it's likely a permissions issue.

Permissions work like this: Users can be restricted access to certain workflow states EITHER by updating the state permissions or, most commonly for grantees, filtering certain states out of their dashboards.

Finance Review state details are shown on the right

However, even if a user can see records in a specific state, they can't edit UNLESS their profile has access to one of the Workflow buttons in that state.

For example, in Bundle Stage below, all profiles can see records this state, but only users with the Grants Management Staff profile can edit records in this state. This is because there's only one button, Send to Program Review, which only Grants Management Staff have access to.

Tools

This is more of a call-out for specific tools that are extremely helpful but took me a while to find. Hopefully this saves you time and headaches!

Fluxx Documentation and Support

https://fluxxdev.atlassian.net/servicedesk/customer/portals

XXX

Card Actions

First, a lot of important tasks, such as applying filters or exporting data, can be taken directly from a dashboard card. The training videos touch on some of them, but they're worth calling out. 

Some additional things to note:

• Filter - one thing that wasn't clear when I started was that Basic and Advanced filters are both applied. So, if you start applying basic filters and then realize you want to also filter on a non-standard field, you don't need to re-build basic filters on the Advanced tab.
• Excel Export - note that if you want to see dynamic fields in this report, they must be enabled for Export as described here. Alternatively, you can always configure and run an ad hoc report and include any field.

Note: Not all WPP staff will have access to all of these tools.

Bulk Update

One of the Card Actions options, it's worth its own call out due to its versatility–it does more than the name implies.

Bulk emails

You can choose an email template to generate messages for all records in the card--even disabled templates! These messages follow queueing rules for the email template, i.e. if Autosend is turned on, they are sent automatically and don't show up in the queue.

Bulk update attribute or status

You can update records' attributes (i.e. fields) and statuses en masse. For example, during the progress report workflow changeover, the previously separate statuses of Finance Review, NSQ Review, and Program Staff review were combined into one new status. New fields were added to indicate each reviewer type's comments or approval, so for old forms I used bulk update to apply a generic message "Approved - see notes for details" to indicate who had already finished review.

Note that this tool only lets you apply the same value for all filtered records. If you need to update multiple records to have different values, this tool isn't much more efficient than just looking up each record and updating manually.

FYI

• If you see core fields that start with "gs", ex GS Project Name (gs_project_name), these are fields that are linked to the old Grantseeker product and are scheduled for deletion. Changes to GS fields don't affect other fields, although GS Project Name is linked to Project Title (core) and Project Summary (dynamic). It was noted Dec 2023 that a migration updating Request records caused GS Project Name to be changed, but because it's not used by WPP that isn't a problem.