Forum Discussion

DavidBrogdon1's avatar
DavidBrogdon1
Qrew Trainee
6 years ago

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

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!

4 Replies

  • ChrisChris's avatar
    ChrisChris
    Qrew Assistant Captain

    Have you experimented with using Quick Base Actions?
    • KevinSlider's avatar
      KevinSlider
      Qrew Trainee
      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.  
    • DavidBrogdon1's avatar
      DavidBrogdon1
      Qrew Trainee
      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
    • KevinSlider's avatar
      KevinSlider
      Qrew Trainee
      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.