Formula to create a unique payment ID based on value extracted from multiple tables/columns

[kimwg]

Hello --

I am interested in developing a formula to assign ID's to a series of payments based on type of payment, date, and information on payee's name. This is for tracking payments in a program that is pairing experienced mentors with mentees.

There are various payment categories (Mentor Initial Payment, Mentor Final Payment, Mentee Stipend Payment), the year in which the mentorship in question started, the name of the mentor, and the name of the mentee(s) that I would like to integrate. 

I'm hoping to develop something where if this was a Mentor Initial Payment to John Smith for mentoring Joe Boggs and Jane Doe in 2024, based on values in the Payment Table (payment type and date), linked records Mentor table (First and Last Name of Mentor), and linked records in the mentee table (First and Last Name of Mentee or Mentees), a unique payment would auto calculate producing something like MIP_2024_JS_JBJD.

The first part is obvious to me (IF function), I've looked up how to extract year from date, I can use concatenate to insert the underscores. I'm struggling a with the steps of extracting the first letter from the first and last name of Mentor or Mentee to concatenate those together for the final output. Any suggestions? TIA.

[TheTimeSavingCo]

You could use "LEFT()" to grab the first letter of the First and Last name?  In the Mentor table, create a formula field to extract those first letters, then lookup that formula field in the Payment table to use it for the formatted ID

LEFT(
  {First Name},
  1
) & 
LEFT(
  {Last Name},
  1
)