Create a record in different table every time a specific field value is unique?

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • In Progress
I have an Orders table and a Ordered Items table. I would like for every time a new item record is added, if the due date doesn't match any other records' due dates, then a new record needs to be created in a Shipments table.

In essence, if all items are due on the same date, they should all be on the same shipment. If there are multiple dates, shipments should only be created for the unique dates.  

Can this be done in QB?

Thanks for any help!
Photo of David Brogdon

David Brogdon

  • 192 Points 100 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

Have you experimented with using Quick Base Actions?
Photo of Slider

Slider

  • 1,322 Points 1k badge 2x thumb
Chris, we should recommend Automations over Actions. ;)

To solve this though: are the unique shipment Date records parents of the order records?

The automation can be triggered by an added order record, and if there is no parent shipment record, then you can add a new shipment date record.  
Photo of David Brogdon

David Brogdon

  • 192 Points 100 badge 2x thumb
Yes, I have figured out how to add the record automatically in the shipments table, But I need to be able to add the record ONLY when the "ordered item" record's "due date" field is a date that another "ordered item" record doesn't currently have. 

For example,

Item #1 due date = "10/10/2020"
Item #2 due date = "10/10/2020"
Item #3 due date = "11/12/2020"
Item #4 due date = "12/15/2020"

There should only be 3 shipment records auto-generated to match the due dates that are unique, because Lines 1 & 2 are the same due date and can be shipped together as one shipment. 

Any way to check for unique dates before auto-generating a record in the shipments table with the corresponding due date?

Thanks
(Edited)
Photo of Slider

Slider

  • 1,322 Points 1k badge 2x thumb
If there is a relationship, you can summarize the # of related records, and lookup that summary in the child table. When running the automation, you could isolate records where the value is 0, and after it creates the first date, the summary would then have a value of 1, skipping the remaining items for that date.

Without a relationship, I was thinking perhaps the target date table could restrict the field to unique values in the date field, and reject adding additional date records during further automations. 

just trying to think outside the box. shoot some more ideas at me.