Is this thing on? Hello! Long time no blog. I just wrapped up a whirlwind of a year where I was Slating more than I have ever Slated before (positive). Maybe I’ll write some reflection on that but I doubt that’s not why any of you are here. You’re here to see what random quirky solutions I came up that colors outside the lines of a standard Slate form. Well folks, I am back to give the people what they want.

If you are hosting an event in Slate that requires a registration fee, you may have tried to implement some sort of promotional code system where a registrant inputs a code and the fee then becomes waived. This can be achieved with Slate standard functionality through a combination of calculation formulas and form filters, but what if you wanted to limit how many times a certain code is used?

There isn’t really anything in the form builder that can do this, but this is possible through a translation table, a dictionary export and a tad bit of custom scripting.

Determining the Requirements

After some brainstorming, I concluded that these items are needed in order for this to work:

• A place to store all of the promotional codes and their criteria
• A way for the form to access that list
• A running count of used codes

Storing the promotional codes

Using a translation table seemed like an obvious choice. Through this I can define the code itself and use the export value to store the count limit for how many times a code can be used. This will come into play later when we build our calculation formula. It will also be easy for any future user to maintain when they need to add codes or change the maximum limit.

Accessing the promo code table

With the translation code set up, we now have a reference for our form to use. We just need a way to link it to the event form itself.

There is no actual join or direct mechanism in the form builder that can do this, so we’ll create a merge field that is a dictionary export that points to the translation table.

Remember, our ‘max’ export is just the export value of the translation table.

In order to check the number of times a code has been used, we have an additional export that is another independent subquery that is based in form responses.

We then have a filter that looks for the count where the form field value matches the promo code value in the translation table.

From here, we can store the translation table as a regular HTML table in an instruction block on the form.

This table will be hidden but with some DOM manipulation through custom scripting, we can leverage this table to determine if a code is valid or not. The main loop for this is show below.

for (const row of rows) {
    // Get the data for each row in the table
    const cells = row.querySelectorAll("td");
    const codeCell = cells[0]?.textContent.trim().toLowerCase();
    const maxCell = parseInt(cells[1]?.textContent.trim()); 
    const usedCell = parseInt(cells[2]?.textContent.trim()); 
        // Check if there's a match between the code inputted code and one of the codes on the translation table
        if (codeCell === code && usedCell < maxCell) {
            // Set the payment value and messages.
            reg_fee.value = 0;
            valid_msg.style.display = "";
            decline_msg.style.display = "none";
            
            // Set the flag to true and exit the loop immediately
            foundValidMatch = true;
            break; // Stop the loop as soon as a match is found
        }
    }

Validation

For the registrant to input their promo code, we just set up a regular free text form field. This form field then informs the payment widget that actually places the payment activity on the record.

There needs to be some feedback to let the registrant know if their code was valid or not, otherwise, they can end up submitting the form without knowing what fee they are going to be charged. To address this, I set up a message for each scenario that will appropriately display depending if the promo code was valid or not:

<div id="promo_accept" style="display:none ; color:#27ae60;">
  <strong>The promo code has been applied and you will not be charged the registration fee</strong>.
</div>

<div id="promo_declined" style="display:none; color:#c0392b;">
  <strong>The promo code you entered is either invalid or no longer active. You will be required to pay the registration fee.</strong>
</div>

The displaying of this message is addressed in the prior code block. You’ll notice that there is logic in there that resets the style display depending if there was a valid match or not.

Final Demo

With all the pieces in place, we now have a function that does the following:

1. Checks to see if the inputted code matches anything in our translation table
2. Checks to see if the max number of used codes has not yet been reached
3. Display a message to alert the registrant if the the code is valid or not

In the end, we have something like this:

Conclusion

I thought this was an interesting request and had a lot of fun working through it. This ended up being part of a more elaborate build where I am also taking into account discount rates so that different promo codes apply different fees. I didn’t go into detail about that in this post because that is just more custom script (in short, I use another export value to store the discount rate and then use that in a formula to calculate the fee). My goal here is to showcase the utility of independent subqueries and translation tables and the different ways they can be leveraged in Slate. You may not have events that use promo codes, but my hope is that you can take this foundation and apply it to something else. I don’t know what that is, but I look forward to finding out.

It’s been a little over a year since I started this blog and it has been one of the most professionally gratifying things I’ve done. I keep it pretty low key when it comes to my job but I finally decided to write about some solutions I came up with. I didn’t have any specific goal…I guess I wanted people to have more resources but if I were honest about it, I was probably seeking validation.

I eventually got that validation last year in Chicago, in front of the FGI booth. Nick Conte found me and had some very nice things to say about my posts. He liked it so much that he encouraged me to become a community ambassador, which I hesitantly took him up on. It should’ve been a no brainer, but I am inherently lazy. Eventually, I figured that if I was serious about my career then I need to put myself out there and do things that would normally make me uncomfortable. If I already started posting content online, I might as well keep it pushing.

From there, I met Paige and Kim along with a group of ambassadors who were some of the sharpest and most thoughtful slate users and captains. I wasn’t in there long and I actually didn’t say much, but just being on those calls with them really widened my perspective on how much Slate captains really care for their institutions and making sure everyone is given the best possible opportunity to succeed. I am so grateful I got to be a part of it but from what I can tell, they are just getting started.

I ended up leaving the ambassador program because I joined the preferred partner, Kennedy and Company, as a Solutions Engineer — which is a fancy way of saying consultant. There’s a lot to be said about this transition but I’ll just say that I’m Slate-ing more than I’ve ever Slate-ed before (I mean that positively). I haven’t been writing much because I’ve been so focused on making sure I have my best foot forward for the clients we work with. The nature of the work has changed a bit too where I was previously just building things by myself on a whim. The process as a consultant is much more methodical and collaborative which has been a very welcome change of pace for me. It’s only been a few months but I’m excited to be learning and working with such a talented team.

So as you can see, a lot can happen in a year. None of which would’ve happened if I hadn’t decided to put myself out there. It also wouldn’t have happened if I didn’t have the wonderful people around me constantly encouraging me to keep going. So if you’re looking for a push, let this be your sign to network and find that solution you were told wasn’t possible while you’re at summit. If you don’t know where to start, come find me along with the rest of the Kennedy and company team at booth # 10 in the vendor showcase. At the very least, you’ll make a friend to meet up with at the next conference.

(…it would be me. I would be the friend.)

For those unfamiliar, Chart.js is an open source JavaScript library that allows data visualizations to be easily rendered on your custom webpage. Using the Chart.js library just gives you a bit more of a dynamic visual for your Slate data. You can make pretty robust charts for proper data analysis but for my purposes, I just wanted some simple visuals for YTD comparisons so that the recruitment team can keep an eye on their territories.

Note: I spend most of this post talking about the Chart.js code since my previous post about this topic covers a lot of the portal/ query setup. You can refer back to that post for reference: ‘DevLog: Mobile Friendly Reports’

Understanding the template code

Before we put this into a Slate portal, let’s take a look at the template code for a simple bar chart.

const labels = Utils.months({count: 2});
const data = {
  labels: labels,
  datasets: [{
    label: 'My First Dataset',
    data: [65, 59],
    backgroundColor: [
      'rgba(255, 99, 132, 0.2)',
      'rgba(255, 159, 64, 0.2)',
    ],
    borderColor: [
      'rgb(255, 99, 132)',
      'rgb(255, 159, 64)',
    ],
    borderWidth: 1
  }]
};

If you’re not super comfortable with JavaScript, this can look like a lot but here are a couple of bullet points to summarize what’s actually going on:

• ​The chart’s configuration is handled through a JS object.
• The actual numbers that are displayed on the chart are defined by the ‘data’ object property where it is given an array of values.
• The background and border colors are also defined by arrays and the color defined at a given index will be the color displayed for the data in that same index. For example, 65 (the first bar in our graph) will have a background color of ‘rgba(255, 99, 132, 0.2)’
• This example uses the Utils.months method to label the axes — this is just a more efficient way of using month names as your labels, especially if you’re looking at a whole year, but the labels can be anything you want.

With this foundation, we can implement this with a Slate portal and connect it with a Slate query.

Use Case

The admissions staff were well-versed enough in Slate to be able to query and build reports, but they didn’t really have a quick way to pull and assess data for any of their given territories. I started playing around with the idea of a portal that passes through a parameter of a selected territory. Once I got that framework down, I decided to take it a step further and make it mobile-friendly with dynamic visuals.

One of the charts that is used in this portal is a comparison of the YTD number of applications submitted between the current and previous admissions cycles.

First, I grabbed these numbers by having independent subquery exports that filter off the application year (round) and submission date.

From here, we can insert these exports as merge fields into the code for our chart. This is very similar to how we made simple KPI cards in my previous post. The only difference is that instead of inserting the merge fields into the HTML, we are putting it into the JavaScript so that it can render as a dynamic chart. We also want to use the stacked bar chart type from the library.

 new Chart(document.getElementById('myChart').getContext('2d'), {
   type: 'bar',
   data: {
   labels: ['Fall 2024','Fall 2023'],
   datasets: [
               {
           label: 'Actual',
           data: [{{2024_app_count}}, {{2023_app_count}}],
           backgroundColor: '#1f97e9',
           borderColor: 'rgba(54, 162, 235, 1)',
           borderWidth: 1
       },


                  {
           label: 'Goal',
           data: [{{2024_app_goal}},{{2023_app_goal}}],
           backgroundColor: 'rgb(34,139,34)',
           borderColor: 'rgba(54, 162, 235, 1)',
           borderWidth: 1
       }




   ]
},
   options:
 {


       indexAxis: 'y',
       scales: {
           x: {
               stacked: false
           },
           y: {
               stacked: true,
             beginAtZero: true,
             stepSize: 100
           }
       }
   }
});

The above code is inserted into a script element in a portal view where the chart is injected into a given <div> element. I copied this into a couple of other portal views that were looking at different metrics. The whole thing looks like this:

Conclusion

I’ve mainly written about mobile-friendly reporting through Slate because the institution has PowerBI licenses to do more heavy reporting, so my use cases have always addressed the need for quick and concise reports. However, Chart.js is a nice alternative if you want to create a more interactive and digestible report for larger presentations to various stakeholders. I’ve just used simple bar and pie charts but there are plenty of other options in the library to do something more elaborate.

I do want to mention that having your portal queries scan through the person table multiple times can lead to slow-downs and even timeouts (which is why I am only comparing two years worth of applicants). It may not be ideal for daily reports, but it can be great for yearly summaries where you can just manually input the values instead of having the report calculate totals every time it is open.

I was playing around with the idea of gamifying training and thought that the New York Times connections game would be a good fit for drilling the database tables. I’m not entirely sure how effective it will be but thought it would be fun to try nonetheless. I actually posted my prototype on the facebook group and the feedback was overwhelmingly positive! Seems like a lot of you are interested and how I built it so here is a complete breakdown of how I built my own version of NYT connections.

As a disclaimer, I should mention that although this is hosted on a Slate form, the game is mostly built with HTML, CSS, and JavaScript. As a disclaimer to the disclaimer, I learned coding foundations from various online materials so the way I write my code may be seen as basic (lots of if statements) but at least it’s easier to read?

Overview

If you are not familiar with the original game, the premise is fairly simple:

• Players get 16 words in a 4 x 4 grid divided into four hidden categories
• Figure out connections between words and group them into sets of four; grouped words become a category
• Players must figure out all categories by grouping the words

In the NYT game, you only get a limited number of attempts, but since this is supposed to be a training tool, I opted out of implementing that to encourage players to finish the puzzle.

In practice, it looks something like this:

Setting up the grid

I ended up setting up the foundation of the game by using a CSS grid that contains 16 HTML button elements. Each of these buttons has the word stored as well as attributes that store which group the word is in.

<div class="button-container">
   <button data-group="B" id="btn1" onclick="activeField(this.id)">Jobs</button>
   <button data-group="A" id="btn2" onclick="activeField(this.id)">Concat</button>
   <button data-group="C" id="btn3" onclick="activeField(this.id)">SIS</button>
   <button data-group="B" id="btn4" onclick="activeField(this.id)">Relationships</button>
   <button data-group="D" id="btn5" onclick="activeField(this.id)">Priority</button>
   <button data-group="C" id="btn6" onclick="activeField(this.id)">Banner</button>
   <button data-group="D" id="btn7" onclick="activeField(this.id)">Updated Date</button>
   <button data-group="A" id="btn8" onclick="activeField(this.id)">Formula</button>
   <button data-group="A" id="btn9" onclick="activeField(this.id)">Existence</button>
   <button data-group="B" id="btn10" onclick="activeField(this.id)">Addresses</button>
   <button data-group="D" id="btn11" onclick="activeField(this.id)">Created Date</button>
   <button data-group="A" id="btn12" onclick="activeField(this.id)">Coalesce</button>
   <button data-group="B" id="btn13" onclick="activeField(this.id)">Sports</button>
   <button data-group="D" id="btn14" onclick="activeField(this.id)">Street</button>
   <button data-group="C" id="btn15" onclick="activeField(this.id)">CommonApp</button>
   <button data-group="C" id="btn16" onclick="activeField(this.id)">Colleague</button>
</div>

Each button is assigned a ‘data-group’ that corresponds to the 4 different categories that the player has to find. We also have a unique id for each button and a linked function that determines the ‘active state’.

Active State

A button’s ‘active state’ is defined by the button being highlighted as well as the selected word being considered as part of the grouping the player wishes to submit or check. This is handled through the following function:

// SET ARRAY TO STORE SELECTED BUTTONS
let selected = [];
function activeField(id){
    // GET THE SELECTED BUTTON
    const btn = document.getElementById(id);
    // GET THE BUTTON'S DATA GROUP (CATEGORY)
    const btn_group = btn.getAttribute('data-group');
    // HIGHLIGHT OR DE-HIGHLIGHT THE BUTTON DEPENDING ON THE CURRENT STATE
    if (btn.style.backgroundColor === 'rgb(0, 158, 135)') {
        btn.style.backgroundColor = '';
        // IF DE-SELECTED, REMOVE THE RESPECTIVE DATA GROUP
        const indexToRemove = selected.indexOf(btn_group);
        selected.splice(indexToRemove,1)
        } else {
        // IF SELECTED, ADD THE DATA GROUP
            btn.style.backgroundColor = 'rgb(0, 158, 135)' ;
            selected.push(btn_group);
        };
        
        };

This function is handling a couple of things. First, it’s checking to see if the button is already clicked by checking the background color. If there is no background color, then add it in so that the selected button is highlighted. Additionally, store the grouping of the selected button in our ‘selected’ array. We use this array in order to check to see if the player has selected the correct grouping of words. We also have the function to reverse this if a player de-selects a word, meaning that the respective data grouping is removed and the background color is removed.

Here’s a demo of the console so you can visualize what is happening.

Checking the Answer

So now that we are properly storing the data of the selected words, we can write our function to check to see if the grouping is actually correct. To put simply, we want to see if our array has 4 elements in it and all of those elements belong to the same grouping. Ex: [“B”,“B”,“B”,“B”]. This will be the function that will be attached to our ‘CHECK’ button.

To achieve this in JavaScript, I wrote a nested for loop that compares each element in the array to the next. If it all matches, then the code will remove the selected words and display the discovered category above the grid.

let isWrong = false;
for(i=0; i < selected.length; i++) {
    for (j=0; j < selected.length; j++) {
      // IF ONE ELEMENT IN THE ARRAY DOESN'T MATCH, SET THE ISWRONG VARIABLE TO TRUE
        if (selected[i] != selected[j]){
            isWrong = true;
            break;
        } 
    }

You’ll notice that we have a ‘isWrong’ variable that is set to true if there’s a found discrepancy within the array. If it is false, then we can trigger the actions that are connected with a correct answer. When a player has found a grouping, the following should occur:

1. The correct grouping leaves the grid

if(!isWrong && selected.length == 4){
    // WHEN THE ENTIRE ARRAY OF 4 MATCHES, HIDE THOSE WORDS FROM THE GRID
    allBtns.forEach(button => {
        if (selected.includes(button.dataset.group)) {
            button.style.display = 'none'
        }
    });
}

2. The discovered category should be displayed

Each category display is set up as static HTML elements that are initially hidden in the beginning of the game.

<table border="1" cellpadding="5" cellspacing="1" style="width:100%;">
  <tbody>
    <tr id="group_a" style="display:none">
      <td style="text-align: center;">
      <h2>
        Subquery Outputs
      </h2>
      Coalesce, Concat, Existence, Formula</td>
    </tr>
    <tr id="group_b" style="display:none">
      <td style="text-align: center;">
      <h2>
        One-to-Many Query Bases
      </h2>
      Addresses, Sports, Jobs, Relationships</td>
    </tr>
    <tr id="group_c" style="display:none">
      <td style="text-align: center;">
      <h2>
        Unique For Merging Examples (__ ID)
      </h2>
      SIS, Banner, CommonApp, Colleague</td>
    </tr>
    <tr id="group_d" style="display:none">
      <td style="text-align: center;">
      <h2>
        Address Table Rank Determination
      </h2>
      Priority, Created Date, Updated Date, Street</td>
    </tr>
  </tbody>
</table>

Our function un-hides these categories as they are discovered throughout the game. This is handled through a switch statement that looks at the common data-group.

// USE JUST THE FIRST ELEMENT OF THE ARRAY 
const correctGroup = selected[0];
switch(correctGroup) {
    case "A":
        document.getElementById('group_a').style.display = '';
        gameCheck += 1;
        gameScore += 25;
        break;
    case "B": 
        document.getElementById('group_b').style.display = '';
        gameCheck += 1;
        gameScore += 25;
        break;
    case "C": 
        document.getElementById('group_c').style.display = '';
        gameCheck += 1;
        gameScore += 25;
        break;
    case "D": 
        document.getElementById('group_d').style.display = '';
        gameCheck +=1;
        gameScore += 25;
        break;
}

Scoring

This is supposed to be a training tool but it is a game after all, so I felt it’d be an added level of fun to have some sort of scoring system to further incentivize players to try harder. Ultimately, I decided on the following:

• The player starts out with 100 points
• Every incorrect guess subtracts 10 (score will not drop below zero)
• Every correct grouping adds 25
• The total possible score is 200

To implement scoring, I initialize a global variable ‘gameScore’ and set it to 100. From there, I update the score depending on the scenario. In our switch statement above, you can see that 25 points get added for every correct category that is found. If there’s an incorrect match, then points are subtracted in the following line of code:

let gameScore = 100

// IF ANSWER IS WRONG AND ONLY 4 BUTTONS ARE SELECTED, ALERT THE PLAYER AND SUBTRACT 10 POINTS
if (isWrong && selected.length == 4){
    alert('Incorrect, try again!');
// MAKE SURE THE SCORE DOES NOT DIP BELOW 0
    if(gameScore > 0) {
        gameScore -= 10
    };
}

After I had done the test run with the users in the Facebook group, I saw that there were a LOT of players who were easily hitting the max score of 200. In order to have a bit more variation with the scores, I added in a time element where the score counts down until all categories are found.

let timerInterval;

function startTimer() {
    if (timerInterval) return; // Prevent multiple intervals
    timerInterval = setInterval(() => {
      if (gameScore > 0) {
      gameScore--;
      };
     // UPDATE THE SCORE FORM FIELD 
      const formScore = document.getElementById('form_8a9aade3-6008-475c-9bf9-6a6dafd3ee94')
      formScore.value = gameScore;
    }, 2000); // Update every 2 seconds
  }

// Function to stop the timer (triggered when the game is complete)
      
function stopTimer() {
        clearInterval(timerInterval);
        timerInterval = null; // Allow restarting
      }

Finishing the game (Completion State)

You’ll also notice that within each case statement, there is another variable called gameCheck that gets added with every discovered category. I use this to essentially track the progress of the player. When the gameCheck variable reaches 4 (+1 for every category), then that means the game is done and I can use that to put the game in a completion state. In this case, our completion state simply means that the score becomes locked in and a congratulatory message is displayed.

function gameComplete(){
    // GET THE FORM FIELD ELEMENT
    const formScore = document.getElementById('form_8a9aade3-6008-475c-9bf9-6a6dafd3ee94')
    // INSERT THE ACTIVE GAME SCORE INTO THE FORM FIELD
    formScore.value = gameScore;
    if (gameCheck >= 4) {
    // DISPLAY GAME END MESSAGE
        gameEnd.style.display = '';
        checkBtn.style.display = 'none';
      stopTimer();
    }

}

Leaderboard

To help feed everyone’s competitive side, I knew I wanted to implement a leaderboard that shows the player how they did in comparison to all the other submissions up to that point. To achieve this, I ended up creating a simple portal that pulled in all the form responses, along with the scores and put it into a simple table. I then embedded the portal onto the confirmation page of the form so when the player submits the form, they can immediately see the results.

Demo

And here is a full demo of the game!

Closing

I once saw a funny tweet that said something along the lines of “video games are so funny, you could be killing a bunch of zombies but in reality you’re just deleting lines from a table”. To some degree, that rings true here. All this code is really doing is storing and removing elements from an array and making sure they all match. It was a really fun project to work on and I’m also delighted that it was able to work within Slate’s framework.

I don’t actively study code or development. I’ve always just looked up how to do something based on the specific problem I’m trying to solve. This was really one of the first times where I had an idea pop in my head and I was immediately able to execute it without having to look up much. It’s not the cleanest or most efficient code but it works and everyone I’ve shared it with has enjoyed it. It’s a simple knockoff of an existing popular game but I am so so proud of it.

1) You can have the registration form calculate a fee upon submission but this fee reduction requires a manual review and must be approved first.

We do not want to have the students pay a fee that they may not even be eligible for. On the flip side, we don’t want to delay students who aren’t requesting a reduction and give them the ability to pay immediately.

To account for this, there is form logic attached to the payment widget where it only appears with the standard fee only if the student does NOT request a fee reduction. Otherwise, the widget is withheld so there will be no charge until the reduction is reviewed.

2) The payment-due activity needs to be placed on the Slate record, but the personnel reviewing the requests are not Slate users.

Typically, we would designate an operations staff member to handle this but the process is so fluid that it made more sense to figure out a way to automate this piece. We could’ve also trained up the staff members who are reviewing the reductions, but the window to turn this around was relatively short. Additionally, I don’t feel all that comfortable granting that level of access to non super users, especially when we’re dealing with fees.

So how do I reconcile all of this into one simple, streamlined process? In a portal, of course.

Building the Portal

First, I have a query that grabs all the registrants that have requested a fee reduction.

Then, I created a pop-up view that calculates and displays the expected amount. If necessary, the reviewer can still adjust the final reduced fee.

If you’ve kept up with my previous posts, you know that I love embedding forms into pop-ups. I like doing this because I can have the portal pop-up display whatever I want it to and have a portal query auto-fill the form while also keeping the form itself relatively minimal.

Hidden on this form is a payment widget that takes the value of the final total amount. The fee reduction does not account for guests so I had to ensure those costs are always included. If the payment activity were person scoped, the activity will get added to the record upon submission. However, we have this set up as an application scoped payment activity so these charges have to be added after the fact.

To automate this, I created an import/export where a query sends out a file with the new approved amounts into the SFTP. A source format then picks it up and adds it into the record as a new due payment activity. There is a final deliver mailing set up that notifies the student when the import is complete.

Closing

I’ve actually gotten some version of this request at previous institutions I’ve worked at. I’ve always hesitated in offering up a solution in Slate because it’s always been my assumption that the more sustainable solution lives within student accounts/billing. I’ve come to learn that student accounts are interconnected with so many different things that it takes time to revamp or even tweak a fee. Additionally, there’s always the question if this kind of solution is even worth diving into. Is there enough a financial benefit to go through the trouble of tinkering with an already established system? That’s not really for me to decide. I was asked to build out a process in a short period of time and Slate provided the tools to do so.

I will say that we saw enough of an increase in funds that went towards making a more robust orientation for the students and their parents so your institution may want to do this too.

Our institution has a process for students to formally request or change their faculty mentor. This was previously handled through a Microsoft Dynamic form which generally worked fine at a base level. It can accept form submissions and then route the request to the appropriate faculty member for approval. The problem was that there was no real mechanism for tracking the status of the request or sending out reminder notifications if it sat untouched over a length of time. Both of these gaps can be filled with Slate functionality.

Here is the outline of what I thought the process could look like in Slate:

1. Student submits request via Slate form
2. Request gets sent to faculty member for approval
3. Student success is notified of approval and officially updates the roster in campus SIS

Before getting into the actual workflow, I had to actually decide on how to store the faculty mentors and their roster of mentees. What I landed on was a custom dataset to store the faculty contact information that is then linked to an entity that stores their roster.

Faculty Mentor Dataset

For this project, the dataset is pretty minimal. All I really need is their SIS ID, name, and e-mail address. I hope to expand this in the future but for now, this is enough information.

Mentee Roster Entity

We then have a custom entity that is scoped to the faculty dataset. To link the appropriate student record, I have a related dataset row field that leverages our existing custom continuing student dataset. Additionally, I have a status flag to indicate if a student is active on the roster or not.

Request Form

The request form is simple and straight forward. We just ask for the student information (which is auto-filled via SSO) as well as the faculty member they’d like to request to be their mentor. The added benefit of having a dataset is that we can leverage the auto-suggest function. When the mentor is properly selected, we can ensure that the request is forwarded to the appropriate e-mail.

Faculty Mentor E-mail and Approval Process

Since I wanted to utilize some dataset information that is stored outside of the form response, I built the notification e-mail in Deliver. I have the form response as the base and turned off deduping in order to ensure that the faculty member gets an e-mail for every request.

The content of the e-mail lets them know the student that submitted the request as well as a total count of their current roster. This is used as a quick reference for the faculty member to help inform their approval decision.

The ‘Review Request’ hyperlink is a URL to a separate Slate approval form. The URL also passes through parameters to auto-fill the student and faculty information. Additionally, I also pass through the form response GUID of the initial request so we can tie it to the approval decision.

For various reasons, the mentor roster actually also has to be coded appropriately in our SIS. This is currently a manual process so we have to notify the appropriate staff member to make that update. To do so, there is a form communication that is set up that sends out an e-mail if the faculty mentor approves. If the request is not approved, then the student will be immediately notified.

Tracking Progress

On the form registrant page, I also have a subquery export that links the form response of the approval form and displays the final decision. If the faculty member hasn’t responded, the status will show ‘Pending’.

Final Processing

We do not want to send the student a confirmation of the approval until the SIS is updated so we actually have an admin field that is updated on the back-end of the form which then triggers the final confirmation e-mail to the student.

It is always a priority for me to minimize the amount of places something needs to be manually updated. To achieve this, there’s a daily scheduled SQL stored procedure to export the updates in the SIS and import it into Slate.

Closing

You may be reading this and wondering why I didn’t build this in a Slate workflow. As I mentioned earlier, this process is so low-impact that I didn’t want to go through the trouble of planning and building out bins. At this point it is much easier for the academic advisor who is managing this process to go into the form registrant page instead of clicking through the workflow after updating the SIS.

This is somewhat of a simple workflow but I was happy that I was able to establish the foundation of a faculty dataset. This potentially opens up options for faculty to interact with the student record more. I would eventually like to have Slate be the central hub where we facilitate other form approvals (add/drop, petitions, etc.) as well as alerts for when the student requires some additional assistance. A lot of discussions are needed with various stakeholders before that can happen but this is certainly a good start.

What I am offering with this post is a framework you can keep in mind when having discussions with your leadership about workload and the potential need for more resources. You may not have control over how staff resources are allocated or even get to determine what actually goes into Slate, but what you should have is the ability to advocate for yourself and for your team.

I should clarify that the idea here isn’t to try to convince your leadership to give you less work. This is more about having effective discussions to ensure that you set proper expectations and are doing productive work that you can feel good about.

Types of Work

If you are active within the Slate community, you have probably seen jokes about how Slate is hard to define as a product (it’s an admissions CRM…but so much more!). In turn, this also makes it hard to define what the Slate captain role does. My mistake early on was considering all of my work to be just ‘Slate work’. I felt responsible for any process, no matter the size, that went through Slate. I didn’t realize I was doing myself a disservice until I came across a book called ‘The Phoenix Project’.

The book tells a fictional story of a struggling car parts company that is mandated to improve their IT operations with no additional resources, while dealing with conflicting corporate priorities. In short, they need to not only improve their online store, but ensure it’s better than their competition while dealing with layoffs and limited time. Now, I will say that I wouldn’t recommend this book to everyone. The story aims to provide an illustration of the principles laid out in the DevOps handbook so the dialogue can be a bit…unrealistic, but effective nonetheless. Also, working as a Slate captain in higher education doesn’t necessarily align with working as a DevOps specialist in a software adjacent company. However, I still found that reading this book to be incredibly cathartic.

There’s a lot of concepts that are taught throughout the story but I wanted to highlight the lessons that were most applicable to me– the first one being the four types of work:

Business Projects — Business initiatives, most of the development work.

• Slate implementation as a whole and any potential integrations with the rest of the campus enterprise systems.

Internal IT projects

• This refers to projects that reduce technical debt and provide more automation. In the book, they specifically cite server maintenance and other database solutions. Luckily, Technolutions mostly manages that side of the house but tasks like building new Slate workflows, creating sets of rules, setting up automatic mailings to alert users of issues can also be considered.

Updates and Changes

• This can apply to any change but every academic cycle usually comes with a consistent set of changes whether it is making updates to applications, accounting for new policies or handling changes from external parties such as the FAFSA.

Unplanned work or recovery work

• You may also know this as ‘putting out fires’. This comes in many forms in Slate–maybe a query all of a sudden no longer runs as efficiently as it once did, you get an urgent request to pull data, or an incorrect mailing gets sent out and no one knows why.

In hindsight, these seem like very obvious categories but seeing it written out on paper was a REVELATION for me. I finally had a framework where I can properly describe what was going on in my day-to-day workload. As the institution’s use of Slate grew, so did my responsibilities–which spanned across all four of these types of work. My title was just ‘Slate captain’ but in reality, I was a pseudo project manager, security administrator, integration developer, quality control specialist, helpdesk support, and even a salesman (I would be mandated to help transition a process into Slate but then when I actually got in the room with stakeholders, I still had to convince them that this is the best move for their process). I’ve found that framing your work this way, instead of throwing out general language about having a lot on your plate is a much more effective way of signaling to managers/stakeholders that you are being stretched thin. Why should they care? Well, because they are inadvertently creating a bottleneck, with you being the constraint.

Identifying the constraint

In the book, the company’s IT team was fairly large and had multiple teams. The main issue is that they really only had ONE person who knew the system well enough to solve any unplanned work or ‘fires’ that would come up, so when there would be multiple fires across different teams, every team had to wait for this one key member to put out one fire before moving on to the next, constantly creating bottlenecks. Sound familiar?

You may have already experienced this at your campus. I’d bet good money that you have gone to your campus IT with a new idea to improve a process but were either turned away or given a lower priority due to their limited bandwidth. This is by no means a knock on campus IT teams. I believe that they are also being stretched thin. I point this out because there’s an inherent understanding that implementing with other systems takes a lot of work and for whatever reason, that counter argument doesn’t work quite the same with Slate. That’s probably more of a testament to how dynamic a platform it is, but it shouldn’t mean that the approach should change.

Project proposals are usually (in my experience) presented to me as simple tasks but don’t take into account scope creep, potential features or changes that need to happen in the future. For example, a department may request for one of their forms to be recreated as a Slate form. The creation of the form itself is fairly simple and fast, but how often does the form change? How will the form responses be reviewed? Does a workflow need to be built? Are there communications involved? The list goes on from there. I think it should also be emphasized that you’re not just adding a new form or workflow into Slate–you are potentially replacing a decades old process that one has ever needed to change or analyze, until now. Alternatively, if you are creating a brand new process, it should be treated as a long term solution, or at least the foundation to one, rather than a band-aid fix. Whatever the case, there needs to be an appropriate amount of care and time devoted to it.

If the stakeholders are receptive to those questions, hopefully that leads to discussions about on-boarding other staff members into Slate or highlights the need to hire new staff. Otherwise, Slate captains are stuck managing multiple processes and have to deal with all of the types of work that go along with it. Aside from this being a lot of work for one person, this just doesn’t set the campus up for future success. Ironically, the more work that ends up being consolidated into Slate, the easier it is for Slate captains to become the same bottleneck they’ve been working so hard to avoid or eliminate.

Making Your Work Visible

There’s a quote from the ‘Godfellas’ episode in Futurama that has always been stuck in my mind since my roomate played me the episode in college.

“When you do things right, people won’t be sure you’ve done anything at all”

The context of the episode doesn’t really apply, but that quote has become increasingly resonant to me as my career has gone on.

Working in Slate, or campus systems in general, is interesting because if you do your job well enough, you create this expectation that things just inherently work. You don’t have the benefit of seeing tangible results from your efforts. Admission counselors can celebrate receiving more applications than their anticipated goal, but in this role, simply not getting a panicked email about something being broken is probably the highest form of praise you can get. This is why it is absolutely crucial for you to spend some time to actually log your work. Write documentation, track updates, keep your leadership up-to-date with periodic milestones, etc.

I have a custom built ticket and project management system within Slate where I can take that data and present an analysis on time being spent on a given task or project. Before that, I tracked my work on Obsidian on a kanban board and later became a user of Notion. There is no shortage of tools that you can utilize, but I don’t think it really matters how you log your work, just as long as you do. Don’t get me wrong, I find doing this to be quite the drag…even a little boring. However, it will pay dividends when a new request comes in and you can provide a proper assessment of how much bandwidth is available. If this new project needs to take top priority, then you can set the expectation that completion of the existing pending projects will need longer timelines. Only you really know the amount of time and work it takes to get certain tasks done, so it’s vital that you can communicate that with some supporting evidence.

Closing

So will any of this actually amount to anything? It’s hard to know for sure. Nothing is guaranteed and it will depend on what your institution’s priorities are so you can take everything I say with a grain of salt.

I did spend a lot of time at my previous job rambling through this kind of thing with my boss at the time. He had always reassured me that changes were on the way but I ended up taking another job before those changes had come to fruition. After I had left, the team I was on was able to expand from two (including myself)…to FIVE. So yes, there is hope that if you are persistently advocating for your team, you will be given the proper resources.

Like with everything in higher education, everything moves in cycles. There will be some periods where budgets will be tight and everyone needs to do more while offices get expanded in more prosperous periods. All I know for sure is that no matter the amount of work, it is a much better feeling knowing that you are working with intention instead of aimlessly putting out fires. I wrote this in the hopes that this can help you get on that track.

Until next time.

The alternative method involves a combination of a custom HTML form and a custom SQL query. I should note that this practice is generally not recommended by Technolutions. As with any custom solution, there are plenty of variables that can go wrong which make it tougher to troubleshoot. Because of this, I tend to agree that you should always prioritize leveraging forms when wanting to post data to the record. At the very least, I recommend re-thinking your process and seeing if there is an alternative solution before going this route.

With all that being said, here is how to do the thing I’m not recommending.

Note: I will be building off of some components we established in my previous post so I will be referring back to that a couple of times.

Use Case

Let’s go back to our custom course entity. We have a field to store any advising notes that may be used for future reference. Instead of overwriting a new note every time an update occurs, we want to set this up so the initial note is displayed. This will allow the user to have the ability to edit the note.

Fields Table

First, I think it’s important to look at the table itself and illustrate what it is we’re actually going to do. Let’s take a look at one of our notes that already exist in the table:

There are more columns in the table than the ones shown in the screenshot, but what’s most important to note here is that all custom fields live in the same table and each row has a [record] id that ties it to the actual Slate record, regardless of the scope.

In our case, the [record] corresponds with the GUID of our course entity row and the [id] is unique to every row in the FIELDS table. It’s ok if you don’t completely follow right now. This might make more sense when you see how we end up using these fields in practice.

Pop-Up and Course List Query

We are going to use the same two queries that we used when using the form approach. One will pull the information of our clicked-on course. Again, this takes the course ID and places the associated data and puts it in our pop-up after the linked method is called. The other will display all of the courses linked to the respective record. We will also use the same pop-up link that we used before.

For more info, you can refer back to the previous post.

HTML Pop-Up Form

 <body>
   
   <!-- Add form element -->
    <form action="?cmd=updateCrs" method="post" style="width: 500px;"><input name="course_id" type="hidden" value="{{crs_id_update}}" /><input name="cmd" type="hidden" value="updateCrs" />


       
    <div class="header">
      Update Course Information
    </div>

<!-- Display data from our pop-up query -->
    <div class="content" style="height: 300px;">
      <table class="plain">
        <colgroup>
          <col style="width: 100px;" />
          <col />
        </colgroup>
        <tbody>
          <tr>
            <th style="text-align: left;">School</th>
            <td>{{crs_school_update}}</td>
          </tr>
          <tr>
            <th style="text-align: left;">Course Name</th>
            <td>{{crs_name_update}}</td>
          </tr>
          <tr>
            <th style="text-align: left;">Course Note</th>
<!-- Add <textarea> element so that user can edit the actual note -->
            <td><textarea cols="50" id="course_note" name="course_note" rows="4">{{crs_note_update}}</textarea></td>
          </tr>


        </tbody>
      </table>
    </div>


    <div class="action">
      <button class="default" onclick="FW.Lazy.Commit(this);" type="button">Update</button><button onclick="FW.Dialog.Unload();" type="button">Cancel</button>
    </div>
    </form>
 </body>

This HTML code was lifted from the knowledge base article on Portal Pop-ups and uses some functions and styles that are baked into Slate’s custom framework.

The keys here are the form and input elements. These allow for the information entered in the pop-up to be referenced in our POST method.

Breaking it down a little further, the ‘name’ attributes within these elements should end up corresponding with the custom parameters you are going to use in the query.

<input name="course_id" type="hidden" value="{{crs_id_update}}" />

For example, this will take the course ID field ({{crs_id_update}}) from our form pop-up query and run it through the custom SQL query as the @course_id parameter.

We do the same thing with the actual note.

<tr>
  <th style="text-align: left;">Course Note</th>
  <td><textarea cols="50" id="course_note" name="course_note" rows="4">{{crs_note_update}}</textarea></td>
</tr>

Here, we are pulling the existing note from our pop-up query and placing it into a text box so it can be edited. We also define the id attribute with “course_note” so that it will also be passed through our POST query.

Custom SQL Query

When the form is submitted, we want to have the FIELDS table be updated with that edited note.

You could write an UPDATE statement in SQL to do exactly that. However, we don’t just want to update notes in our portal. We may need to ADD notes as well, if a note doesn’t already exist.

To account for that, we’re actually going to write a DELETE and INSERT statement in our query. So in practice, we’re doing a full replacement of the field instead of updating the value.

First, we need to set our parameters that we refer to in our HTML code.

We have these two parameters to take in the values of our course ID (to ensure the correct course is being updated) as well as the actual course note.

We can then write out our SQL statements.

DELETE FROM FIELD WHERE [RECORD] = @course_id AND [FIELD] = 'pce_course_note'

INSERT INTO FIELD
([FIELD],[RECORD],[VALUE])
(SELECT 'pce_course_note', @course_id, @course_note)

These statements happen in the sequence they are written when the form is submitted. First, the query will find the field row that has the GUID of the course that is being reviewed and delete it. Then, it will add in a new field value with the new note.

The last step is linking this query with a portal method that is configured to POST. The ‘Action’ corresponds with the CMD we use in our HTML form.

Portal Methods

To summarize, these are the portal parts we’re dealing with:

1. Course List — To display all relevant rows within our custom course entity.
2. Pop-Up: Update Course — This hosts the view for that HTML form we built above.
3. POST Course Update — Handles the custom SQL query that will update the fields table.

Demo

Here’s what it looks like after we put all those pieces together:

As you can see, the initial note is auto-filled in the text box. Then, we add some additional text that ends up being saved. Magic!

Closing

Being able to go with this custom approach adds a lot more flexibility to what you can do within your portals. However, I want to reiterate that you may be introducing more ways your portal can break. It could be something as simple as a browser update that changes the behavior of your code. Something like adding/editing notes may not be worth the trouble but you may want to do something more robust where this is the only viable option within Slate. It’s important to assess the risks against what you are trying to achieve before deciding on how to move forward.

Slate has a great widget that can be used within custom tabs on the Slate record that makes it easy for users to either edit or add a data row to the entity. Unfortunately, this widget isn’t available within portals, which is where most of my non-operations colleagues work out of. The good news is that you can replicate the widget within the portal by using a form pop-up that passes through appropriate parameters.

Let’s look at an example I talked about in a previous post regarding course evaluation.

I have a custom entity that stores evaluated courses and their grades. Let’s say we want to allow users to update the grade and credit value of any given course.

We first have a portal that displays the record’s associated courses in a simple table. Within that table we’ll have a form pop-up that passes through our required parameters to ensure that the clicked-on row gets updated.

Building the Form

The key when building your form is to choose the base that your entity is scoped to. My course evaluation entity is person-scoped so my form will also be person-scoped.

Form Fields

In order for the form to update the appropriate row, you’ll have to pass through the GUID of the person record and the GUID of the entity row. The rest of the form will depend on what you want to update within your entity. In our case, we want to update the course grade and credits.

After your form is built, you’ll need the embedded code to put into one of portal views that will be discussed in a later section.

Portal Methods

For this to work, we’ll need two separate portal methods. One will handle displaying all of the courses and the other will handle the form pop-up.

Course List

For our first query, we’ll start with our entity for our base and export out the different fields we want to display. Functionally, you’ll just need to make sure that you’re pulling the GUID of the course. This will be passed through as a parameter in our pop-up link. The rest of the query exports will be up to you.

That last column will be where we’ll display the link to show our pop-up form. We’ll get back to that after we finishing setting up our portal methods.

Form Pop-Up

For the pop-up, we’ll need a second query that will pull in the relevant fields for the course that is actually clicked on.

To set this up, we’ll add in a custom parameter that will take in the course GUID from our course list query and use it in our filter.

These exports will be passed through in our embedded form code so that the form pop-up will auto-fill with the clicked on course information.

This looks like a lot but all we’re doing here is taking the form field IDs and linking it with the exports from our pop-up query.

Pop-Up Link

Now that our portal methods are set up, let’s go back to our course list view and add in our link for the form pop-up.

The ‘onclick’ attribute is set to a baked in Slate function so you just have to worry about the ‘data-href’ part. Your CMD will be the action of your pop-up method and the second part is the @crs_id parameter we created in our Form Pop-Up query.

After that, your portal should now be ready to take in entity updates.

Closing

If your staff and colleagues are already familiar with navigating Slate records, then I’d recommend sticking with using the built-in widget. However, as your processes in Slate get more elaborate, this is a good method to have in your back pocket that raises the ceiling on what you can offer other stakeholders.

In my experience, the main reason why I’ve needed to build this is so that the staff’s workflow isn’t disrupted. It’s much easier to provide an update if you’re working off a list instead of clicking into the Slate record, finding the appropriate tab, scrolling to the correct section, clicking on the entity row, and THEN updating. As I’ve joked about in previous posts, I consider my job to be minimizing the amount of clicking for users. This has been one of the key tools in achieving that.

Use Case

I built out an athletics portal to process compliance. I wrote about the structure in one of my first posts but in short, student athletes need to be cleared by academic services, health services, and our athletic compliance officer in order to be eligible to compete. Each of these departments needed their own view to meet the requirements of their clearance process. They also generally work exclusive of each other so the views also need to be secure and only displayed to appropriate staff members. On top of that, coaches also need to see the status of their roster, but only for the teams they actually coach for. That’s all to say, access within a portal can get tricky so I thought it’d be helpful to talk about how I manage that.

User Roles

The easiest way to categorize and restrict access is to leverage Slate’s user roles. I ended up creating two:

Athletics-coaches

Athletics-staff (non-coaches)

Each of these grants access to the athlete portal but also determines which tabs are viewable. These are also used for certain access in Slate outside of the athletics compliance portal. I could’ve also made roles for health and academic services but they are relatively small teams who only access Slate through this portal, so I handle their access in a user query. My general rule of thumb is to only create roles for groups of users that have a set standard of permissions they need.

Portal User Query

I start with a user base and use the default @identity filter. This allows slate to know the user that is logged in.

Here, I have a bit more flexibility in what I can restrict. I create sub query existence exports that are based off of the username.

I then take this flag and restrict tabs in the view through liquid markup.

This is also helpful when you need to go more granular with your access. For example, there are some assistant staff members that need view-only access. For them, we can just create a separate existence export for that. I use liquid markup to take out any form or pop-up links that can update the record.

Coach roster

Finally, I needed to ensure that coaches only see the roster of the teams that they are actually in charge of.

I could’ve just created a user role for each team but that permission list would’ve gotten very cumbersome. What I ended up doing was leveraging our sport prompt list. I took the usernames of each coach and plugged them into the export values of their respective team.

I then have a prompt query that will show any sports where there is a matched export value with the username.

In the portal, I have a JavaScript function that passes through the logged in user as the @coach_user parameter through the prompt query to determine which sports to show.

Here’s an example of the view of one of our coaches who is in charge of both the cross country and track & field teams:

And for comparison, here is a screenshot of what it looks like when a user has access to everything:

Closing

I’ve built portals for other departments where having user roles was enough. There were also cases where the process was so nuanced that I set restrictions using only the user portal query. I chose to write about the athletics compliance portal because it’s a good combination of both approaches. As with anything I write about, you’ll have to assess for yourself what works best within your structure and what you’re most comfortable with.