function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Victor Nguyen 13Victor Nguyen 13 

Tracking Late Payment

Hi everyone!

We need to track students late payment. When they are accepted to our school, the Representatives will toggle "Acceptance Letter Issued" stage. When the students pay their fees, the Reps will toggle "Registered" as the students are ready to be enrolled.

We have a deadline for payment as well. If Reps don't toggle a different stage after "Acceptance Letter Issued" stage after the deadline, we want a custom field to indicate that the student is late on paying their fees.

What would be the best practice to accomplish this project? Please give me some advice.

Thank you!
Harvey JethroHarvey Jethro
Check out this documentation: https://help.salesforce.com/articleView?id=customize_functions_i_z.htm&type=5
Naga AlapatiNaga Alapati

I think you can create a custom formula field with checkbox as return type (something like below) to indicate whether the student is late on paying their fees or not. 
  
IF(AND(TODAY() > Date_Field__c, Stage__c <> 'Registered'), 1, 0)
If the custom field with the above formula value is checked then that means the student is late on his payment
 
Andrew GAndrew G
Lets make an assumption:
1. When the status changes to "Acceptance Letter Issued" stage, a process builder (or manual validation), updates a field "Acceptance Letter Date" as TODAY() - the assumption being there is a date field for the date the letter was sent.

Then, (Naga is close), i would do a formula Checkbox field with formula like:
  
IF(AND(TODAY() > Acceptance_Letter_Date__c + 30 , ISPICKVAL(Stage__c, 'Acceptance Letter Issued')), 1, 0)
Modify the number of days as required in your instance.  I guessed at 30 days.
You can then report on the calculate field.  Perhaps have a it run automatically with an email feature weekly.
If there is a manual process for chasing those getting close to being over due, you could also do a report where the filter is Acceptance Date < Today - 20 days with the Stage being  "Acceptance Letter Issued".  And set that up for weekly email alert.
And if you wanted, you could calculate the number of days overdue   
TODAY() - Acceptance_Letter_Date__c - 30
so today minus the acceptance letter date would give the number of days since the letter was sent,  removing the 30 days (again assumption) would give the number of days overdue.


HTH

Regards
Andrew
 
Victor Nguyen 13Victor Nguyen 13
Thanks everyone,

It's actually more complicated than that, I didn't put the full information at the beginning.

Each semester has a different deadline, and it only apply to the student who apply to that semester.

I had a process builder update a field called Accpetance Letter Issued date when the stage is toggled just like Andrew said. I have a object called Terms contains the name of the semester (for example: Fall 2020) and the the deadline.  We have 2 cases:

1. If the Reps the toggle the Letter Issued stage before the deadline, students must pay before the deadline.
2. If the Reps toggle the Letter Issued stage after the deadline, student will have the Friday of the week after the day the Letter is issued (technically they will have a full week to pay). In this case, the deadline will change to the week after they receive the letter.

The way we charge the late payment fee is the same for both cases. Every week pass, we will increase the number of late payment by 1. For example, late penalty is $10, if I don't pay the tuition fee 2 weeks after the deadline, I will be charged $20.

I'm planning to schedule to run the apex class every Friday. The class will select all student is under "Acceptance Letter Issued" stage with the semester that they apply, the day that the letter was issued. Also, it will select the Term table. Compare the date and increment the Number of Late Payment Charge field if needed.

I'm not sure if there is any better way to accomplish this project. If there is, please let me know.

Thank you!!!
Andrew GAndrew G
Hi
Create a field Post_Deadline_Date with a formula 
CASE( 
  MOD( Acceptance_Letter_Date - DATE(1900, 1, 7), 7),
  0, Acceptance_Letter_Date + 12, 
  1, Acceptance_Letter_Date + 11, 
  2, Acceptance_Letter_Date + 10, 
  3, Acceptance_Letter_Date +  9, 
  4, Acceptance_Letter_Date +  8, 
  5, Acceptance_Letter_Date +  7, 
  6, Acceptance_Letter_Date + 13, 
  null
)
Vary as required, but the idea is to generate a date that is the next Friday, full week after the Acceptance Letter Date.

Then for your Checkbox field 
IF(
  AND(
    Today() > Deadline_Date,
    Acceptance_Letter_Date < Deadline_Date,
    ISPICKVAL(Stage, "Acceptance Letter Issued")
  ),
  1,
  IF( 
    Acceptance_Letter_Date > Deadline_Date,
    Today() > Post_Deadline_Date
    ),
    1,
    0
  )
)
Part one is if the deadline date has passed, and the acceptance letter was issued before deadline and the status is "acceptance letter issued" still, then it is late.
Part two is if the Acceptance letter was issued after the deadline and Today is pasted the Post Deadline Date, then it is late.

HTH

Regards
Andrew
 
Victor Nguyen 13Victor Nguyen 13

Hi Andrew,

Thanks for the suggestion.

If I use those formulas, I would need to have the deadline date assigned to each students when we create the opportunity as we have different deadline for different semester they apply to.

Also, we need increment the number of week after the deadline. For example, if the late penalty is $10 and I don't pay my tuition 2 weeks after the deadline, I will be charged $20. I think we will create a field called Number of Late Payment Charged and increment it depends on each case.

 
Andrew GAndrew G
Hi
Given there is an object called Term which has the Deadline for the Semeter, I assumed you could/would have a process builder or similar to pop the Dead Line Date into the Student Application record as I have assumed the aplication would have a look up to the Term record.

As for the penalty, you could do a formula making use of the FLOOR formula function.
so perhaps (untested) 
IF(
  AND(
    Acceptance_Letter_Date < Deadline_Date,
   Late_Checkbox_field
  ),
  FLOOR(Today()-Deadline_Date/7)
  IF( 
    Acceptance_Letter_Date > Deadline_Date,
    Late_Checkbox_field
    ),
    FLOOR(Acceptance_Letter_Date -Deadline_Date/7),
    0
  )
)

HTH
Andrew