Take information from one table to create another table based on a third table's information

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

My company has 3 tables related to invoicing:

Invoices

Invoice Items

Reports

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:

<!DOCTYPE HTML>
<html>
<head>
    <title>Create an Invoice</title>
</head>
<body>
    <form action="[myDomain]/db/[target table]?a=API_AddRecord&apptoken=[app token]" method="POST">
        <table>
            <tr>
                <th>Invoice Title:</th>
                <th><input type="text" name="_fid_[x]"></th>
            </tr>
            <tr>
                <th>Invoice Start Date:</th>
                <th><input type="date" name="_fid_[y]"></th>
            </tr>
            <tr>
                <th>Invoice End Date:</th>
                <th><input type="date" name="_fid_[z]"></th>
            </tr>
        <table>
        <div>
            <input type="submit" value="Submit">
        </div>
    </form>
<body>
</html>

Photo of Sam

Sam

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Sam

Sam

  • 0 Points
I have already done a few things that make this process possible, though it requires a lot of work on my end that I believe should be able to be automated.
I create a new Invoice and take note of its Record ID#.  Next, I go to my reports, and run a summary report that filters on or after a start date of my choosing, and before on on an end date of my choosing.  I then go to "More" and "Copy these records to another table..." and copy them to my "Invoice Items" table.  Then I go to my Invoice Items table, "More" and "Search and Replace these records...", choose "Related Invoice," and change all blank fields and change them to the Record ID# of my invoice.
I also created an embedded report that looks exactly like my ideal embedded report, so that part is completely taken care of.
The issue I now have is that creating an invoice is a lot of steps, and there is much room for error.  Is there anything I can do to automate this process?  Can I use the dates I put into the Invoice as my start and end dates of the filter of my summary report? And when copying those files into my Invoice Items, can I automatically create another field that links it back to my original Invoice?

>>EDIT: I created a URL field that automatically runs the report for the invoice date range.  It has to be manually clicked after I create the invoice, which is not the best, but again, better than what I started with.
I belive that this is possible to automate using only native QuickBase.  If you like you can contact me via the information in my profile to schedule a time for me to look at your app and ask a few questions.  After that call I would probably be able to estimate the setup hours required and you can decide if you want to proceed.  But it would be a consulting project and not just a few forum "Tips" as what you are looking to do is not trivial.