My company has 3 tables related to invoicing:
On our reports, we get get daily information that has different metrics we are keeping track of. They are related to certain assignments and sub-assignments within the app.
Our invoice table has 3 fields, "Title," "Start Date," and "End Date," and has a relationship with the Invoice Items (an invoice may have many invoice items).
Our invoice items has most of the same fields as our reports, with a few more to measure additional metrics.
What we would like to do:
When an invoice is created, we want to look for all reports that have a "date" (one of it's fields) between the start and end date. We then want to summarize some of the other metrics based on sub-assignments. We then want to take this newly created information and create new items in the invoice items table, and make these items associate with the invoice. Finally, we need a custom embedded table that shows the the subtotal for each assignment (based on the sum of metrics in sub-assignments), and the overall total for that time period. This table must be editable, as our company may need to manually change certain numbers in our metrics for various reasons.
I've attached a spreadsheet that shows what we would like this to be able to do. The first sheet is an original report, the second sheet shows what we would like the invoice items to look like, and the final sheet shows what we would ideally like our embedded report to look like.
What I have:
Unfortunately, I do not have a whole lot. I have created a Quickbase user page with some custom HTML, but all it does is create a new Invoice. After that, I am lost as to what to do. Here is my code:
<title>Create an Invoice</title>
<form action="[myDomain]/db/[target table]?a=API_AddRecord&apptoken=[app token]" method="POST">
<th><input type="text" name="_fid_[x]"></th>
<th>Invoice Start Date:</th>
<th><input type="date" name="_fid_[y]"></th>
<th>Invoice End Date:</th>
<th><input type="date" name="_fid_[z]"></th>
<input type="submit" value="Submit">