• Final

    • Create Scheduled Query SFTP Process

    Owned by Helen Williams
    Last updated: Nov 27, 2024 by Elizabeth Storrs
    4 min read

    Until the Slate to SIS interface is updated to accommodate two fields for gender (gender marker and gender identity), the delivered ‘sex’ field in Slate must continue to be updated with SIS-friendly prompts as schools transition to the new fields.

    Delivered fields in Slate cannot be updated by a rule, so the only way to do this is to set up a query that exports a list to Slate’s SFTP environment, where Slate can pick it up and run it through a source format to update records.

    1. Create a query to identify records that will need updating.

    The query should identify records where the gender marker or gender identity have been updated and where there is either no value for ‘sex’ or the value in the ‘sex’ field doesn’t match the gender marker or gender identity. This will pull records that have new data in one of the two fields, and where ‘sex’ needs to be updated because of that.

    Make sure to add an export for Person ID for record matching in the source format. Set an export for sex that coalesces the value of identity first, then marker if identity is null.

    When testing, I discovered that the nested exports in my subquery export for Sex were outputting the ‘short value' for the prompt, instead of the ‘extended value’ or what is set as ‘value’ in the prompt configurations (what we expect, the display name). To ensure this doesn’t cause issues with prompt value mappings in the source format, I set the nested exports to specficially use ‘extended value.’ image-20241115-170622.png

     

    Sample TUSMGP query

     

    SQL Filter for identity does not match correct sex value: (@identity = 'F' AND @sex <> 'F') OR (@identity = 'M' AND @sex <> 'M' ) OR (@identity = 'TF' AND @sex <> 'Trns') OR (@identity = 'TM' AND @sex <> 'Trns') OR (@identity = 'NB' AND @sex <> 'NB') OR (@identity = 'O' AND @sex <> 'O') SQL for marker does not match correct sex value: (@marker = 'F' AND @sex <> 'F') OR (@marker = 'M' AND @sex <> 'M' ) OR (@marker = 'X' AND @sex <> 'Ix') OR (@marker = 'D' AND @sex IS NOT NULL)

    1. Set up the query for exporting:

      1. Set the Execution Option to be "retrieve only the new records since the query was last run"

      2. Set the "Schedule Export"

        1. Path template: ../incoming/PersonRules/update_sex_%FT%T.txt (this is the folder and name of the file that will be sent to the SFTP/source format. %FT%T will generate a date/timestamp at the end of the file name, which is key to ensure files are not overwritten)

        2. Delimiter: Comma(,)

        3. Leave as inactive for now, until finished setting up and testing the rest of the process.

    2. Create a new Source Format to consume data generated by the query. Use settings in images below, making sure to:

      1. Set Unsafe to Unsafe to ensure the person record will be updated even if there is an active application.

      2. Disable update queue to prevent records updated by this process from going through the rules (unnecessary).

      3. For the import path/mask, use the same path from 2b, but remove the ‘../incoming/' and change ‘%FT%T’ to '*’ (this is a wildcard, so regardless of the date/timestamp the source format will pick up this file).

      4. Leave the source format active, but remap inactive. Set the remap date today.

    image-20241114-222419.png
    image-20241114-222516.png

    1. Return to the query and send a test file to the SFTP using this button on the query homepage (this will not be available until after the “Schedule Export” settings within the query editor are set up).

    2. Go to the Database and "Force Process Pickup" then "Force Process Import" to force Slate to look for the file in the SFTP server. (This may take a few minutes--it can also happen on its own, this is just to make it happen more quickly).

    3. Return to the Source Format and go to Edit Mappings and do the Field Mappings and the Prompt Value Mappings. For Prompts, use the list below to append all possible values and then match.

      Female Male Transfeminine/Transwoman Transmasculine/Transman Non-binary Prefer to self-describe Other Decline to State F M X Decline to State

    4. Refresh test environment and then change the remap setting to active on the source format. Verify that records have been updated appropriately.

    5. After testing, to turn the whole process on:

      1. Return to the Query > Edit > Scheduled Export and select all days and the 2:00 - 4:00 window. This will update records before the 4:00 Slate to SIS interface runs.

      2. Return to the source format and set the remap to active.

      3. Enjoy!