I am going to take a one-table database that has been used only for data entry (and treated like a spreadsheet) and add an invoicing feature, so the data entered can populate invoices and make the workflow easier.
The users want to invoice once a month. Invoices are generated by location. Not every location will have an invoice every month. Invoices will take the data entered as line items and show them in a portal on each invoice.
I'm struggling with how to auto-create invoices that group by location so I don't end up with a separate invoice for each line item. I'm come up with a hypothetical script that I think will work, but it seems clunky and I'm hoping someone can suggest a more elegant solution.
There will be three tables: data entry (line items), invoices, and locations.
Here is my current method (untested):
Add a script trigger to the line items table in the Location field (which is also the join field to the Location table)
Import (update matching records in Invoice table, add remaining records as new) - will import the invoice ID and location. This should create new invoices and prevent duplicate invoices for the same location/same month.
To make this work, I think I need to have an invoice ID calculation in the line items table that creates an invoice number from locationID.month.year. So the July invoice for location 19 would be 19.7.2017. This will ensure that the line items that belong together get matching invoice IDs.
What I was hoping to do but couldn't figure out how was to write a script that would scoop up all the records from the month to be billed (previous month) and create the appropriate invoices. Any suggestions on how to do this?