My database is used to manage our small Homeowners Association.
I want to automate the process of sending invoices in July and February. Invoices are based on the number of lots each member owns. Most members own one lot but several own two or more. My table structure looks like this:
Contacts with one contact designated as the "Primary"
- Fiscal Period
A script creates a set of "Billable Items" for each lot, fiscal period and dues or assessment item. e.g. The Description field in a "Billable Item" record might look like this, "Annual Dues for 2015-2016 for Lot# 2
This adds 31 records (one for each lot) to Billable_Items. I took this approach to make sure a line item is created for every Lot.
Now I want to create an invoice for every record in Billable Items that doesn't have a value in fk_ID_Invoice and I'm stuck.
From a layout based on Accounts, I want to see a list of all Billable Items with a matching ID_Account AND where ID_Invoice is blank. (e.g. not previously invoiced).
I've tried using constant values in Accounts to create a relationship with Billable Items but so far, that isn't working.
Accounts::ID_Account = Billable_Items::fk_ID_Account AND Accounts::Constant ("INV") ≠ Billable_Items::fk_ID_Invoice.
Every ID_Invoice states with "INV"
From a layout based on Accounts, how can I view a list of Billable Items that match on ID_Account AND with nothing in fk_ID_Invoice?