Discussions

Hi!

We bill a particular kind of deliverable to our clients on a monthly basis according to the number of hours worked on each deliverable that month. The result I envision for this problem is a table where each record consists of a unique field that is a concatenation of the key associated with the deliverable and the month in which work was completed on that deliverable (e.g. "1234 - April 2019" for deliverable #1234 if work on it was completed this month), the sum number of hours worked on that deliverable in that month (based on our existing Time Cards table), and a number of other fields relating to the deliverable in question.

This seems like the best path forward for our use-case based on our billing process, but I don't think I'm necessarily married to it so let me know if I'm way off. We'd like to not have to run a different summary query for every month we want to look at or add a new field for every month of work.

I want to run an import or automation on our existing Time Cards table to bring the new billing table up to speed for all existing time cards. Then moving forward, I'd like run an automation (either on a schedule or triggered by each new relevant time card) that either creates a new billing record for the deliverable-month if it doesn't exist, or simply updates an existing record with a new total of hours worked.

I've been banging my head against the wall for a couple days trying figure out how to do this but it seems like I keep running up against some limitation with everything I try. Has anyone figured out a similar path or have an idea of how I could do this? I'm fairly savvy with specifying imports and actions and automations, and I could write up scripts if I have to, but naturally I'd rather do what I can in vanilla QuickBase if I can.

Thanks in advance for helping! I hope I was anywhere close to coherent. Please let me know if there's anything I can clarify.

Hi Christopher, I'd like to help. What's your main/first problem here, forming the concatenation field in the Billing table (child table) and then making sure the (child) records are assigned to the right parent in the Time Cards table? Then you want to move forward to automate this? 

I'm having trouble pinning down your first obstacle. Can you clarify and maybe share some sample records from your Time Cards table?

Hi Tyler!

Thanks for responding. I didn't really have any one obstacle. My question was more of a design/workflow problem that I couldn't quite figure out. I was hoping someone would read it and have an immediate idea in mind.

The workable solution I found was to create a new field in the Time Cards table that associated the deliverable being worked on with the month in which the work was completed. That way I could create a summary report where each entry was essentially ["Deliverable #1234 - April 2019", 16 hours]. Then I can export that summary table as a csv, and import it into a new table where that first field acts as the key field, the number of hours is filled in,and the other relevant information can be pulled in from the deliverables table with a relationship.

I set up a Microsoft Flow flow (is that what they're called?) to automate that export/import process using a series of HTTP requests so it runs each night, keeping that new table more-or-less up to date.

If anyone thinks up a better way to accomplish what I'm doing then I'm all ears! But this is what I worked up in the last 24 hours.