AnsweredAssumed Answered

Create batch of Invoices for HOA

Question asked by sccardais on Aug 17, 2018
Latest reply on Aug 21, 2018 by TKnTexas55


I would like to create a script that automates the process of creating bi-annual invoices for members of our Home Owners Association.

  • Every six months, we have to create invoices for HOA Members. One invoice is for Annual Dues. The other is for Roadway Maintenance. We track these in a JOBS table.
    • e.g. the July 2018 Annual Dues is one JOB. The February 2019 Road Assessment is another JOB
    • e.g. Member 1 owns 2 lots so their invoice would contain two line items for the same JOB but for different LOTS. Member 2 owns 1 lot so their invoice would have only one line for the same JOB.
  • Creating the invoices manually is both tedious and error prone. I want to make sure that every lot is invoiced to the proper Member and that none are accidentally missed due to a manual process that might be interrupted or subject to human error.
  • For each Job, Members are invoiced the same amount for each lot they own.
  • This is the Table structure I have so far.
    • MEMBERS
      related to Invoices on member_id Fields in MEMBERS show a count of the number of lots owned and the lot numbers.
    • LOTS
      Related to Members on member_id
    • INVOICES
      related to Members on member_id and a JOIN table that is related to JOBS on job_id
    • JOBS
      JOBS are like "products" in an invoicing solution. The JOBS table includes a unique JOB_id, Job description and Job amount$
    • invoices_JOIN_job
      same as "Lines" in invoicing solution. related to Invoices on fk_inv_id and to JOBS on fk_jobs_id.
      • NOTE: I'm using line items on each invoice for clarity and because we potentially need to allocate Payments at the line item level.
  • Where to Start?
    • In the end, for each JOB, I need one JOIN record for each Lot showing the Job ID, Job Description, Amount (from JOBS), Member ID, and fk_Inv ID.
      • I think a Loop script starting from LOTs can create one JOIN record for each LOT with the correct fk_Member id, but not sure how to assign the fk_inv_id properly since some Members own more than one lot.
        • e.g. If there are two records in the JOIN table with the same Member_id and Job_id, both should be assigned the same Inv id.
      • I could use some help on this looping script, especially how to assign the Inv_id properly.
    • Once the Invoices are created, some (most) will be emailed but some will be printed and mailed USPS. I would like to save a PDF of each invoice. This will allow me to email or print / US Mail for those who insist on a printed invoice.
  • Thanks in advance for any and all help.

Outcomes