#AcumaticaTnT Generic Inquiry to Export Scenario

Tim Rodman · 2022-08-26T03:58:45Z

I'm working with a client who has a large database in Acumatica. Their GLTran table has over 2.8 million records (yes, over 2,800,000 records). As always, we'd like to be able to analyze this data with 3rd party tools. But we can't export to Excel because an Excel Sheet can only hold around 1 million records. OData is an option, but it's REALLY SLOW on a large dataset like this. So, I've been wondering if we could use an Export Scenario. Thanks to @shennelly at the Acumatica User Group in Ohio (AUG Ohio) tonight for telling me that it was possible. I gave it a try and it worked. This is what I did... 1. Created a file in Notepad with the column headings that I wanted, separated by commas: Note: I discovered that Acumatica wanted a second line or it didn't recognize the file. So, I just copied the first line and pasted it since that was the easiest thing to do. 2. Saved the file as a CSV file called My Vendor Export.csv: 3. Went to the Data Providers (SM206015) screen, populated the Name and Provider Type fields, then pressed Save: 4. Dragged and dropped the My Vendor Export.csv file onto the screen which attached it to the FILES area. 5. Pressed the RELOAD PARAMETERS button, then Save, which populated the Value column on the FileName row with the link to my uploaded file: 6. Clicked the SCHEMA tab, checked the Active box on the left-hand side, then clicked the FILL SCHEMA FIELDS button on the right-hand side, then Save: 7. Went to the Export Scenarios (SM207025) screen, gave it a Name, selected the Vendors (AP3030PL) Generic Inquiry in the Screen Name field, and clicked Save: 8. Populated the MAPPING tab as follows: 9. Went to the Export by Scenario (SM207036) screen, selected my Export Scenario, then pressed PREPARE (which populated the grid below): 10. Then pressed EXPORT which moved the data into my CSV file: 11. Clicked ... -> Get Latest Version which allowed me to download the latest version of the My Vendor Export.csv file to my computer: 12. Opened the My Vendor Export.csv file in Notepad and noted that I now have a populated CSV file! I didn't go this far, but Stephen was encouraging me to try synching the file an SFTP folder which you could do like this: Back to the client with 2.8 million records in their GLTran table, I tried this same approach in their environment. Unfortunately, the Export Scenario ran for 35 seconds, then returned this error message: The Export Scenario ran for 35 seconds and returned this error message:Denial Code 513. System was unable to process the request due to data size limits. Please review your operation and consider requesting a smaller dataset. Contact your provider for further assistance.

Page 2 / 2 Prev

Acumatica Generic Inquiries & Pivot Tables

Last Post by Joni Girardi 3 years ago

20 Posts

6 Users

7 Reactions

7,502 Views

RSS

Posts: 13

Joni Girardi

June 12, 2023 11:39 am

(@joni-girardi)

Eminent Member

Joined: 9 years ago

You can use DataSelf ETL+ to easily get that data into MS SQL. We have lots of clients using ETL+ to extract much larger tables than 3M rows (current OData max is a table with 60M+). The first extraction can take some time, after that, ETL+ quickly updates changed rows and insert new ones. You may use any MS SQL version, or use our hosted SQL services. Cheers!

Page 2 / 2 Prev

12 Forums
2,532 Topics
11 K Posts
50 Online
2,418 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

#AcumaticaTnT Generic Inquiry to Export Scenario

Online Members