Database Considerations and Decisions

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

New Application Cycle Launch Checklist

There are common steps you'll need to take for each new application/program cycle that goes live. This section covers the key things you'll need to do/keep in mind.

1. Update existing forms for new program cycles

A big part of Fluxx administration is updating grant request and request review forms in preparation for new application cycles. Rather than copying forms for each new year (which, as you'll see historically, gets messy fast), update the existing form.

Quick note: If you remove a field from the form, this does NOT delete the information from the record. For example, if you remove the "Project Title" field from the CHSP form, and then you perform an Excel export on that record, the project title will still be populated.

Best practice if a field is no longer relevant to a given program is to create an employee-only section that has that field. Include relevant contextual information, such as the year(s) a given field was in use. This will make it easier for the average Fluxx user to access relevant historical details without having to do a more in-depth database search. Grants aren't editable by grantees after they're granted/declined/withdrawn/closed, so this does not present a problem for previous grant cycles. 

Similarly, validations should be turned off after an application reaches the granted state, so even if you add new required fields it should not impact old grants.

2. Ensure core request grant fields are present

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).

When a new program is launched, 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)
  • HOWEVER, it was found that some forms had liquid code automatically setting this field. On those forms, this field is not required. 
    
• Add these fields after the Primary Topic field (values for these fields were last updated Dec 2023 based on historical consolidation efforts led by Lindsay):
  • Primary Topic - Use Primary Topic (perc_primary_topic).
  • Population Served
  • Ethnicity - Label = "Ethnicity focus"
  • Gender - Label = "Gender focus"
  • Urban/Rural - Label = "Urban/Rural focus"
  • Counties impacted - Use Request Area Served County XXXfor now, still ironing this out. Use this language for the question: "Please indicate the counties in which you anticipate conducting project activities over the duration of the award. This could include where your research will be based, outreach activities, or training opportunities that will occur as a part of this project."

Other changes to include (all applications unless otherwise indicated):

• 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).
• Grant ends at, Grant closes at, Original project end date - These three fields have been used together to indicate when the project ends (or originally ended). Grant ends at is a core calculated field, which requires updating the duration (months) field to change. WPP has decided not to use this field, and would like it removed going forward. Use ONLY Grant closes at and Original project end date (Key Information section)
• Organization Primary Contact Employee ID - This field is used on application forms to collect employee IDs, referenced by HR to determine eligibility (I think this might be PERC specific). When a new app goes live, check if this should be added.
• Academic Staff Indicator - PERC ONLY - Academic staff need to upload a document verifying PI status with campus to be eligible. Use this language in the question: "Do you have an academic staff appointment? If yes, please upload documentation of permanent PI status." Also, for CHSP 2024, we're using the Other Documents type. Might be subject to change.

3. Ensure core request review fields are present

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, program officers should be able to do this without having to contact an admin.

4. Override the project summary, if needed

Fluxx has hard-coded the project summary field to be required on all grant records. However, WPP doesn't really use this field. If for whatever reason you absolutely must make a new grant form, you'll need to add a bit of code to the template to auto-populate some dummy text. 

1. Click the gear icon next to the grant request record to edit
2. Expand the Advanced Settings section
3. Enter the following code into the Draft After Create Block: model.project_summary = "Project Summary Not Needed for <year> <program name> Application"
   Note: If there's currently text in the Current After Create Block, and it's not also setting the project_summary field, you should copy that code into the draft field as well. I haven't seen anything other than the summary being set, but check just in case.
4. Save

If records have already been created and the project summary is blank, you have a couple options:

• Add the field to the internal section of the template, and then manually update all affected records (best for a low number of records)
• Create a dashboard card that finds all affected records, then use Bulk Update to apply some dummy text to all (best for a lot of affected records, though be careful with your filtering logic!)

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.

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

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. Send them a ticket if you're really stuck, they're very helpful!: https://fluxxdev.atlassian.net/servicedesk/customer/portals
• Theme switching (i.e., changing a grant record from one template to another. Only works with grants): https://fluxxdev.atlassian.net/servicedesk/customer/portal/1/article/2991161352

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.

Migrations

The migrations option is really nifty if you need to make a bunch of updates to a lot of different records. Documentation here, but honestly I had more success copying an existing config. NCE Updates or Grant Demographics Consolidation both worked (check the settings carefully!!).

A couple considerations:

• I don't think it's possible to clear values with this tool. If a field is left blank for a record in the file, no change will be made to that field. If you enter Null, it'll just enter Null (and create a new value "Null" for select fields)
• This tool does not work for multi-select values, as far as I can tell. You can add a single option, but if you try to comma-delimit multiple options, like "A, B, C" the system will create a new value "A, B, C" and assign that. Maybe a different delimiter would work though–might be worth experimenting with.

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.