AnsweredAssumed Answered

Loop to Create set of Records

Question asked by sccardais on Aug 25, 2015
Latest reply on Sep 11, 2015 by Mike_Mitchell

I am building an application to help manager our Homeowner's Association.

 

My question is about scraping the process of creating a batch or new "Line Items" that can then be assigned to invoices.

 

I would like to automate the process of creating invoices for our members. Twice a year, I have to send Invoices to all members of our association, based on the number of Lots they own in the neighborhood.

 

Most members own one LOT but several own more than one.

 

TABLE Structure

 

  • ACCOUNTS (Who we bill)
  • CONTACTS (People including one marked as "Primary" whose address and contact info is in ACCOUNTS
  • LOTS (Lot ID, Lot Address, ID_ACCOUNT of current owner.)
  • INVOICES
  • BILLABLE_ITEMS
  • PAYMENTS (future. record of all checks received. )
  • JOIN_PAYMENTS_ALLOCATION (future. Join table to allow split of payments across invoices)

 

We bill our members once in July and once in February. The July invoice is for Annual Dues. The February invoice is for Road Maintenance. Billing is based on the number of lots owned. A line item on a typical invoice would be "Annual Member Dues, 2015-2016, Lot #1".

 

Members that own multiple lots would receive an invoice with a line items for each lot they owned and a total.

 

For each LOT, I want to create a new record in the BILLABLE_ITEMS table populating the fields shown below.

 

  • ID_Billable_Items (auto enter serial number)
  • fk_ID_Lot
  • fk_ID_Account
  • Fiscal_Period (text) (e.g. 2015-2016)
  • Description (text) (e.g. Annual Member Dues or Roadway Assessment)
  • Invoice_Description (calc concatenating fk_ID_Lot & Fiscal_Period & Description

 

I have no experience with looping scripts. How would I construct a script to loop through each record in LOTS to create a new record BILLABLE_ITEMS?

 

Once the new batch of Billable Items is created, I want an "easy way" to create an invoice - assigning each new Billable Item to an Invoice with the proper Account, formatting as PDF, emailing to the email in ACCOUNT and (I think) saving a copy of the pdf in a container field in INVOICES.

 

Any help would be appreciated - especially on the looping script as that's my current road block.

Outcomes