Skip to main content
Solved

Import Scenarios - Order in which lines imported

  • 22 December 2021
  • 12 replies
  • 338 views

Hi everyone,

 

I feel like this probably has an easy answer but I cannot find it anywhere. I have looked through the screens and the help files. 

Our customer is importing GL transactions through the GL file. This last one they sent failed. We realized it is because, on the import file, the transactions are not ordered by the transaction ID anymore. When the import scenario goes to import, it fails because it is only seeing part of the transaction (since the other part is farther down in the file). 

Is there any way to set in the import scenario that when the file is prepared, it rearranges the order of lines by the transaction ID number assigned to it?

12 replies

V
Rank
Userlevel 7
Badge +11

Hi @rebeckamckinney53 

If all transactions are imported into ‘one’ single GL batch, then, it should allow you save the lines in ‘Hold’ mode. 

Send us your import scenario and source file. 

 

You may enable ‘Hold batches on entry’ option on GL preferences, and then try import the GL batch. Check if that works. One has to release the batch from hold later. 

Regards,

 

VijayKumarU
R
Rank
Userlevel 3
Badge

@vkumar 

That isn’t the problem. I pasted the image of the file below. When I prepare the import, this is the order the files come in as. As you can see, Transaction ID 1 is not balanced. The other half of the transaction is farther down in the file. 

When I upload it to import scenarios and prepare, this is the order is loads them in.

Those first 4 lines fail. 

Is there any way to default the order within Acumatica so that they load in the order of Transaction ID? I was able to change the order in the file itself beforehand and upload it, so this is really the only issue. The client wants to be able to import it with as little modification beforehand as possible. 

V
Rank
Userlevel 7
Badge +11

Hi @rebeckamckinney53 

You can sort the lines on per ‘Batch’ basis. I guess the Transaction ID on above screenshot represents one single batch? I dont see the credit entries for the batch on the list. 

You can find a sample GL transaction import scenario on our demo instance. I am attaching the import scenario and the excel file from our Demo tenant. 

 

Regards,

2 Attachments
VijayKumarU
R
Rank
Userlevel 3
Badge

@vkumar 

The other lines are farther down in the file. If I sort the file before importing then it will work. 

How do I sort the lines on a per ‘Batch’ basis? I don’t see where to do that. 

V
Rank
Userlevel 7
Badge +11

Hi @rebeckamckinney53 

One need to sort content on source file in this case. With regards to your requirement, Is this a one time import in your case (initial balance?) or regular import based on any standard file received from another system? (file updated using synchronization?).

Regards,

VijayKumarU
R
Rank
Userlevel 3
Badge

@vkumar 

It will be a regular import. 

Chris Hackett
Rank
Userlevel 7
Badge

Hi @rebeckamckinney53 were you able to figure this out? Thanks!

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
meganfriesen37
Rank
Userlevel 7
Badge +10

I’m running into something similar.  I’ve got manual numbering turned on for my GL batches and I have the batches being set-up On Hold.  I’ve sorted my Excel supporting file in Batch order and all my batches are balanced in Excel.  We have branches not requiring balancing and we have transactions that cross multiple branches.  We are bringing in detailed historical records prior to go-live
 

I’ve tried this with both parallel processing turned on and turned off and I’m running into the following issues that lines are getting entered and saved, but not always into the correct batch number that I want them to go to, so I am ending up with loads of unbalanced transactions. 

Megan Friesen, Contego Software Solutions, Canada
R
Rank
Userlevel 3
Badge

@meganfriesen37 It’s been a while for this one, so I don’t exactly remember the solution. I’m pretty sure we either had them sort before uploading the file or went back to the source and they were able to sort before exporting.
I went back in to look over the windows and see if anything new had been added. Unfortunately, I didn’t find anything. Under data providers, I noticed the command field so I checked it out. Funny enough, it specifically says that Order By cannot be used as a command in this window. 

meganfriesen37
Rank
Userlevel 7
Badge +10

Thanks @rebeckamckinney53 - My data is sorted by the batch number so I’m stumped on this one.  If I figure it out I’ll come back and give an update.

It’s super weird because I used the exact same data set and import in 2021 R2 as a test run and now that we’ve updated to 22.1 it’s doing something different.

Megan Friesen, Contego Software Solutions, Canada
T
Rank
Userlevel 3
Badge

Had the same problem and our SQL expert had me try creating two key fields in the data that created a unique value and then the Acumatica Data Provider moved those to the top on the list when loading the schema and it sorted by those values when the import scenario was loaded.  We were doing GL Transactions and created a Row Counter and Keyed that with the Batchkey from the data  (which was a unique value in the data when combined with the Row Counter field).  

 

This worked for us. 

meganfriesen37
Rank
Userlevel 7
Badge +10

Since this popped back up in my inbox after the answer earlier today, the answer to my issue from last year was that the column order on my screen had been updated and I needed my import scenario to exactly match the column order on my GL Transaction screen.

Megan Friesen, Contego Software Solutions, Canada

Reply


Terms & ConditionsCookie settings