Skip to main content
Solved

Import scenario to skip filed values based on a specific condition

  • March 4, 2025
  • 9 replies
  • 236 views
H
Semi-Pro II
Forum|alt.badge.img

Hello all,

I have been trying to create an import scenario for SO where I have 4 columns from which the data is to be inserted. Out of these 4 columns it is not necessary that the data from these 4 columns are required to be inserted.

So as per the above screenshot, for row 1, the value under book1 should be inserted. Similarly, for last row the value under book1 & book2 columns should be inserted while the other should be skipped. For a single row an individual order would be made.

What condition can be inserted so that the cells where ‘-’ sign is inserted will not be inserted and be skipped in the import scenario?

Thanks.

Best answer by riglesias

Hi @Harry,

If you map Line Number line to =-2 it will delete lines from the grid (see webinar from link below with detailed example min 57:58)
If you need to add multiple lines under details for a single document, you need to have one line per item for the same document number. Like this:

 



Here’s the webinar where I clarify mapping Line number line to =-2 deletes lines from a grid:

 


Hope this helps.

If you still run into any issues, please export the scenario to XML and attach it here so I can restore it in my demo and review.

9 replies

riglesias
Acumatica Employee
Forum|alt.badge.img+2
  • riglesias
  • Acumatica Employee
  • March 4, 2025

Hi ​@Harry 

Have you tried using iif expression? IIf( expr, truePart, falsePart )
Something like this IIf( [Book2]=’-’, ‘’, [Book2])

Rodrigo ~ A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
Chris Hackett
H
Semi-Pro II
Forum|alt.badge.img
  • Harry
  • Author
  • Semi-Pro II
  • March 5, 2025

Hi ​@Harry 

Have you tried using iif expression? IIf( expr, truePart, falsePart )
Something like this IIf( [Book2]=’-’, ‘’, [Book2])

Here’s is the import scenario:

 

 

Where Dummy Item is a non stock that has been created in the system and this dummy item is placed where the cell is empty with value ‘-’
So can you guide me to where shall I be adding the iff expression you’ve shared?

Thanks.

H
Semi-Pro II
Forum|alt.badge.img
  • Harry
  • Author
  • Semi-Pro II
  • March 6, 2025

Hey ​@riglesias , can you suggest any other format of the input file?

riglesias
Acumatica Employee
Forum|alt.badge.img+2
  • riglesias
  • Acumatica Employee
  • March 6, 2025

Hi ​@Harry ,

In my expression, for the true part I typed ‘ ‘ so the fields that have the ‘-’ value would be replaced by a blank space. If you need to select the dummy item instead, change the ‘ ‘ true part with ‘Dummy Item’.  
In your mapping, under Inventory ID line, under Field/Action Name column try this: 
IIf( [Book2]=’-’, ‘Dummy Item’, [Book2])

Rodrigo ~ A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
Chris Hackett
H
Semi-Pro II
Forum|alt.badge.img
  • Harry
  • Author
  • Semi-Pro II
  • March 6, 2025

Hi ​@Harry ,

In my expression, for the true part I typed ‘ ‘ so the fields that have the ‘-’ value would be replaced by a blank space. If you need to select the dummy item instead, change the ‘ ‘ true part with ‘Dummy Item’.  
In your mapping, under Inventory ID line, under Field/Action Name column try this: 
IIf( [Book2]=’-’, ‘Dummy Item’, [Book2])

Hey ​@riglesias , shall I add this above the Line Number -2 condition of the Inventory ID?

riglesias
Acumatica Employee
Forum|alt.badge.img+2
  • riglesias
  • Acumatica Employee
  • Answer
  • March 6, 2025

Hi @Harry,

If you map Line Number line to =-2 it will delete lines from the grid (see webinar from link below with detailed example min 57:58)
If you need to add multiple lines under details for a single document, you need to have one line per item for the same document number. Like this:

 



Here’s the webinar where I clarify mapping Line number line to =-2 deletes lines from a grid:

 


Hope this helps.

If you still run into any issues, please export the scenario to XML and attach it here so I can restore it in my demo and review.

Rodrigo ~ A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
Chris Hackett
H
Semi-Pro II
Forum|alt.badge.img
  • Harry
  • Author
  • Semi-Pro II
  • March 7, 2025

Hi @Harry,

If you map Line Number line to =-2 it will delete lines from the grid (see webinar from link below with detailed example min 57:58)
If you need to add multiple lines under details for a single document, you need to have one line per item for the same document number. Like this:

 



Here’s the webinar where I clarify mapping Line number line to =-2 deletes lines from a grid:

 


Hope this helps.

If you still run into any issues, please export the scenario to XML and attach it here so I can restore it in my demo and review.

Hey ​@riglesias , so in this case can you guide me out how can I split/convert these items from columns (Book1,2,3,4 values) into rows?

H
Semi-Pro II
Forum|alt.badge.img
  • Harry
  • Author
  • Semi-Pro II
  • March 9, 2025

Hey ​@riglesias , I was able to format the excel sheet using forumlae. So below is the input data:


So now the import scenario should skip the blank rows of Book1 column:

I followed the youtube link that you had shared, but still it doesnt process. rather it skips the later inventory ids that are in the sheet too.

 

 

riglesias
Acumatica Employee
Forum|alt.badge.img+2
  • riglesias
  • Acumatica Employee
  • March 10, 2025

Hello ​@Harry 

 

You can try setting up a Source restriction. Something like this:

 

 

Rodrigo ~ A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
Chris Hackett
Terms & ConditionsCookie settingsAccessibility statement
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