Can somebody help me create a report for loan/payment balances and a report to track past due payments?

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered

So this is a multi-level question:

My company gives out no interest loans and sets up a promissory note to receive monthly payments until the loan is paid off. Right now I have “People” as a parent table to “Promissory” (each Person can have many Promissory Transactions).  Each time a loan is set up, the loan amount is entered into a field called “loan amount”.  Each time a client makes a payment, that amount is entered into a field called “payment amount”.  (I'm open to changing this layout if you have a better idea)

1. I want a field to track the balance associated with each person. 

2. Then I want a report that tells me when any person who still owes us hasn’t made a payment in 2 months (1 month past due). 

3.  Ideally I’d like it if that information (person-balance-last payment-last payment date) could automatically go into a form letter document that I could copy into an email or print off to physically mail.  Maybe the email could even auto send...

  I know this is relatively complex and I’m sure I could put several hours into it and figure it out, but I thought that maybe somebody could give me some pointers or point me towards a related article.

  Thanks in advance.

Photo of Animals In Need

Animals In Need

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 410 Points 250 badge 2x thumb
Do you have multiple payments per Promissory Transaction (i.e. the Promissory Note is a single loan)?  Or is each Promissory Transaction a single monthly payment?

How long is the loan term?  (12 months, 2 years, etc.)  And are all the payments monthly?  Are they due on a different day for each loan?  (i..e someone can have a payment due on 5th of every month, another person can have a payment due on the 12th, etc.)
Photo of Animals In Need

Animals In Need

  • 0 Points
It can get sort of complicated... Sometimes there is more than one loan per person.  Sometimes a loan and a payment can happen in the same month.

Loan terms vary based on financial need.  Payment amounts vary per promissory note.  Payments are due by the end of the month but sometimes they come in a little early or a little late.  Sometimes the person will be able to make more than one payment in a month.
Photo of David_In_Tucson

David_In_Tucson

  • 206 Points 100 badge 2x thumb
Hello Animals,

Describing what is needed seemed more complicated than showing.

Please take a look at this mock-up:  https://intuitcorp.quickbase.com/db/bj2hdng9j

It shows a customer loan table and a loan payment table.

It may not do everything you are trying to do, but it does show payment history, balance remaining and you can create multiple loans and multiple payments regardless of timing or amounts.



Have fun!

- David in Tucson
Photo of David_In_Tucson

David_In_Tucson

  • 206 Points 100 badge 2x thumb
Verified link to example Quick Base app 12/18/18/djl