Automated copy of one instance of field value to new table

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • In Progress
Every night I have an external API that copies new records into a "Purchase Order Lines" table. This table has a key field of a PO#-Line#, in the format "123456-001", where the 123456 is the purchase order number and the 001 is the line number. There are multiple records for each purchase order because there are multiple purchase order lines. So the next record would be "123456-002" and so on. The PO#-Line# combo is unique and the key field for that table. However, the PO# by itself is not unique. 

I need to have *just* the PO# copied to a new table as the key field. I have tried creating a QB Action to copy PO# to the new table (Purchase Orders) whenever a new record is added to Purchase Order Lines table, but it tries to copy each new record, and I get webhook error because my import data has duplicate values for unique fields. It won’t even copy one instance.

Since the Purchase Order Lines table is also a details table, I've also tried a maximum value summary field to the master table, which would then push back the value to the details table. However, the QB Action triggered on the addition of a new record in the Purchase Order Lines table takes place before the data is pushed to the master table then back to the details table. So that does not work either.

Does anyone have a better solution that I could try? As a workaround I've created a report to show all new records in the Purchase Order Lines that don't have a corresponding purchase order # in the Purchase Orders table and a URL button to copy the information over, but it's a manual process I have to do each morning.

Thanks in advance!
Photo of Michael Frishman

Michael Frishman

  • 294 Points 250 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
Just an idea.  If you have an API already creating the Order Line Items, any way you can just have that create the POs as well.  That would be the best, most fool proof solution.

I don't think actions will work for you, and if you used a webhook, you would need to use the CSV import function, so it can handle the duplicates.

Just throwing ideas... but I'm not sure on the best option for you.


Matthew Neil - Product Specialist
Photo of Michael Frishman

Michael Frishman

  • 294 Points 250 badge 2x thumb
Thanks for the response. I can't get the API added, at least not easily.

I tried using a webhook, which usually works when QB Actions fail on other commands, but I'm still getting a webhook error due to the duplicate values. Is there a command to add to the webhook that tells it to ignore duplicate entries?

Here is what I have so far:

<qdbapi>
   <usertoken>%xxxxxx%</usertoken>
   <records_csv>
      <![CDATA[
        %repeaton%
[CustomerID-PO#.csv]
%repeatoff%
      ]]>
   </records_csv>
   <clist>11</clist>
</qdbapi>

Am I missing anything?