Question

Upsert (update OR insert a row) with import scenarios

  • 27 October 2022
  • 5 replies
  • 492 views
Gabriel Michaud
Rank
Userlevel 7
Badge +10

Has anyone ever worked on an import scenario that requires you to do a custom search on multiple fields and insert a row if no match was found? Our goal is to work with the Sales Prices screen, and to update data based on a matching price type / price code / inventory ID / date.

This is what I have so far:

There are two problems with the scenario above:

  • It will not insert a new row in the Sales Price grid if no type/code/ID/date combination exist
  • It only works for one item, it will not work if I process multiple items from the same import

I know we could also work with the Sales Prices Worksheet, but in this specific case we’re trying to make it work directly with the Sales Prices sceen.

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica

5 replies

R
Rank
Userlevel 5
Badge +2

Build a GI to apply the criteria to retrieve the rows that need to be inserted and then the IS just does the insert. 

Gabriel Michaud
Rank
Userlevel 7
Badge +10

Royce,

I’m not sure I understand the idea - I want to insert as well as update data from the same import scenario. It would work with entities that have a natural key, but Sales Prices use an identity. The search commands will let me locate the row to update, but it won’t let me insert conditionally. It’s one of the other.

 

As an alternative, I would need two import scenarios, but this is far from ideal.

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
E
Rank
Userlevel 4
Badge +1

@Gabriel Michaud This has worked for us for a similar requirment to update or insert a customer price class row (Haven’t tried with other price types):
 

 

Gabriel Michaud
Rank
Userlevel 7
Badge +10

Hi Ellie,

I see this inserts =Today() in the grid - did you ever try it with two updates on the same day for the same combination? Based on my earlier tests this would fail.

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
E
Rank
Userlevel 4
Badge +1

@Gabriel Michaud It does not fail in our system. In fact even if a single fail contains identical records with different prices on multiple rows, it will not fail ( the last record will prevail).

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved