Automatically Create Master Record for Child if one doesn't exist

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
This may be a bit of a long one as I want to explain the situation fully.. sorry in advance!

I'm building an app to report the weight of different types of post leaving our warehouse for invoicing reasons. As batches of post are completed, the ID of the user who packed it are entered, along with the quantity of items in the batch, the courier, the postage class, and whether it's a batch of parcels or packages. At the end of the day, every day, all of the items in one Bin (package vs letter, courier, class) are weighed, and the average weight calculated from the total quantity, this is what needs to be reported each day.

My thoughts on how to do this was to have two tables, a master called Bins and a details called Batches. The user would create a batch and populate it, at which point a parent would be automatically added, so there would be 1 record in Bins for each type each day. And then at the end of the day add the weight to the Bins table, where the total quantity for that Bin will be reported as a summary field.

My problem is how to somehow automatically create the parent record (perhaps using a concatenation of date&type&class&courier as the key) for the child if it does not already exist.

Many thanks for any advice! Or alternatively if there's another way of going about this!
Photo of Max Allen

Max Allen

  • 302 Points 250 badge 2x thumb

Posted 4 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
I think that you are on the right track.

Decide on the Key field format of the Bin.  For example it might be the result of a calculated field on the child table with this formula

List("-",ToText([date]), [type], [class], [courier])

You can make the Key field of the Bins table be a text string that will be the 4 fields concatenated together like the formula does, but for now we will keep it simple and it will just be a text field.

Make a field on the parent table which will always calculate to true (checked), so the formula for [Bin exists?] would be

true

 and look that up down to the Batches table.  So now the Batch record knows if it needed to trigger the creation of a parent Bin.

Then set up an Action to fire when the Batch is added or modified and the lookup of the [Bin Exists?] is false to create the Bin record and populate its key field with the value of the calculated key field.
Photo of Max Allen

Max Allen

  • 302 Points 250 badge 2x thumb
Thanks a lot, this looks like exactly what I need. When you say "set up an Action to fire when the Batch is added or modified", where exactly do I do this? Would it be on the dynamic form rules for the Add Batch form, or somewhere else entirely?
Photo of Max Allen

Max Allen

  • 302 Points 250 badge 2x thumb
Ah I've found it, using automations. I've managed to get it working, creating new pins only when required and with the right labels. However I've now run up against this error

 https://community.quickbase.com/quickbase/topics/is-there-any-way-to-create-a-summary-field-when-the...

when I try to add the total quantity in the Bin as a summary field, I'm going to use the workaround that you suggested there.

Thanks very much for your help :)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
You will need to use an Action or automation to create the text reference field to mirror the formula field. A form rule would only fire when hand editing a record.