Solved

Can Acumatica working with SFTP client in automation.

  • 24 November 2020
  • 21 replies
  • 2517 views

Userlevel 6
Badge +5

Hello,

     Our vendor is requiring us send files which are exported from Acumatica to them by SFTP in daily basis.
       Yes, this can be done manually, I can export the file to my local computer and then I use a SFTP client to send the file. 
      However, I am wondering can this be done in an automation way?
      Thanks in advance.

icon

Best answer by vkumar 25 November 2020, 08:45

View original

21 replies

Userlevel 4
Badge +1

Yes

On the File Synchronization (SM202530) Page, you can set up a clock to create an Automation Schedule (SM205020). 

Note that synchronization needs to be enabled on the file maintenance to display on the File Synchronization page. 

Userlevel 6
Badge +5

Yes

On the File Synchronization (SM202530) Page, you can set up a clock to create an Automation Schedule (SM205020). 

Note that synchronization needs to be enabled on the file maintenance to display on the File Synchronization page. 

@schneur96  Thank you. It is a bright idea.
However, in this way, it is a semi-automation not a full automation. The key question here is how can I get the “file” in an automation way.
The way you suggested is in the precondition that we already have a file in system, and then define the sync schedule based on that file.
Can we also to generate that kind of file like from a GI or from a report run in an automatically way?
thank you in advance.

Userlevel 7
Badge +11

Hi @ray20 

Here are the steps,

  • You can create a GI to pull required results
  • Create a data provider for an export scenario to be built using GI. You can use an excel file with required columns
  • Create an export scenario to export the result of the Gi
  • On data provider, enable Synchronization to move exported file required destination folder based on date or file version
  • Set an automation schedule to run the export scenario on the required interval

Here are the screenshots:

Created a GI to pull Customer Code and Customer description

Created data provider to export details to an excel 

Created an export scenario to export the data from GI result

 

Export by scenario exports GI result content to the file specified on provider

 

Click here to open file maintenance on data provider and enable synchronization

 

 

Screate a schedule to automate running the export scenario on required time interval

Hope this helps,

Regards,

Userlevel 6
Badge +5

@vkumar 
wonderful, wonderful, fantastic, amazing, super. I don’t know how to choose the words to express how I feel about the workflow you pointed out. This is why I love Acumatica so much, it is powerful, strong, efficient tool. And with so many nice people to help us with. Thank you.
Thank you very much, it is the best solution I can imagine.
Wish you a happy and safe thanksgiving day.

Userlevel 7
Badge +11

@ray20 wish you a happy thanksgiving day :)  stay safe

 

Userlevel 6
Badge +5

@vkumar 
I have repeated your steps, all is OK. however, in your steps, you didn’t mention the File Synchronization screen, ScreenId=SM202530.
The only scheduler I set is on “process export scenarios” screen, will the export of file automatically trigger the synchronizaion file? 
or I still have to add a scheduler maybe 1 to 2 minutes later on “file synchronizaion” screen after the scheduler on “file exporting”.
Please advise, thank you.

Userlevel 6
Badge +5

@vkumar 
hello, it looks like I still have to set scheduler on “file synchronization”
thank you. vendor successfully received our report.

Userlevel 7
Badge +11

Hi @ray20 

Glad to know its working. Yes, you need to set the scheduler on ‘file synchronization’ as well. Did not get a chance to respond to your previous question sooner.

Regards,

Userlevel 4
Badge +1

Hi Vijay,

Brilliant work! 

I did not manage to get a data provider using SFTP working correctly. Is it possible to have a sample/screenshots of the data provider you created ? just want to check how you specified it. 

Please send e at eudes.coelho@tascoli.com.

 

Many thanks

Userlevel 4
Badge

Here are the couple steps to automate the export of the file to the FTP server. 

  1. Once the file is created via the export scenario you can go into the File Maintenance and setup the synchronization tab.  You can test the sync by clicking on the Synchronization > Export File action to test if it works.
  2. Once the sync is working manually it can be automated via the File Synchronization processing screen by setting up an automation schedule.

 

 

Hello,

Thanks for the above.

New query - How can you export one or more SO Sales Order and export to ftp with different file name.

ie Order1.csv, Order2.csv, Order3.csv

And to remove them for next lot of SO orders

ie Order4.csv, Order5.csv, Order6.csv

Appreciate your assistance.

Mac

 

Userlevel 4
Badge +1

Hi Mac,

I have never tried to do what you need using Export Scenarios but I can tell you that my approach in most of the cases is a 2 step procedure:

  1. I use Export Scenarios to extract the required data ;
  2. I use ETL tools to read , transform and distribute the data according to multiple business rules (by order/invoice, all together by customer, etc.) , multiple media formats (csv, Excel, pdf, etc.) and multiple destinations (upload to SFTP or other sites, copies via email, and so on)

I believe this approach gives me the best and fastest results as extracting the data from Acumatica database using Export Scenarios saves me a great deal of time and by using ETL tools I can add distributing rules, schedules, business rules and media choice that I would not be able to add to Export Scenarios. There are other indirect benefits as well such as:  transferring all that secondary workload from Acumatica to an external application or even environment; higher flexibility to adapt the distribution of data to new requirements; it is a solution that fits very well to instances running in cloud.  
 

I hope this helps. 

Userlevel 4
Badge

Mac,

The standard Acumatica export scenario and FTP sync is not able to name a file specifically based on the order number.  We looked at the AcuSync product for a couple clients in the Acumatica marketplace.  It has a lot of features and could easily support your custom file names.

AcuSync - Marketplace - Acumatica Cloud ERP

Thanks,

Kurt

Userlevel 1

Hey guys, thanks for all the answers on this.  I have a scenario where we do have to ftp a file out and I’ve done all the steps outlined.  Everything is working when I do the steps manually.  The file is exported and the sync puts the file out on the ftp server.  great.  My issue is this will need to be run nightly and when I setup the automations to test this, it didn’t work.  The automation history said it was all completed successfully, but I don’t see any evidence that the export scenario was prepared, exported, or the file was sent out.  What  I’m noticing or seeing now, is that even if I just go into the Process Export Scenario screen, pick the scenario and choose either just Prepare, or Prepare and Export, nothing seems to happen.  No error, but nothing changes.  The status of the export scenario doesn’t change to Prepare if I just do Prepare, etc.  I’m thinking that maybe that is why the automations also didn’t seem to do anything.  Any thoughts or ideas on why the Process Export Scenario screen doesn’t seem to do anything or what else I can try?   Thanks.

 

Userlevel 4
Badge +1

Hi

 

When you are on the Export Scenario screen (SM207036) dose prepare and Export work? 

Userlevel 4
Badge

Here are the high-level steps to get the process working to automatically send out data created via a generic inquiry.

  1. Create the GI with the data
  2. Create the export scenario to export the GI to a data provider (Excel or CSV)
  3. Setup the synchronization on the file maintenance tab of the file created by the export scenario
  4. Schedule the export scenario to run using the Process Export Scenarios screen (SM207035)
  5. Schedule the synchronization of the file using the File Synchronization screen (SM202530)

Do you have both #4 and #5 setup?  The file synchronization will only work when a new version of the file has been created by the export scenario.

I just setup something similar for a client that needs to export daily transactional data to a parent company.

Thanks,

Kurt

Userlevel 1

Thanks for replying Kurt.  I have double and tripled check things.  I did discover that if the data did not change, mine is from a GI of some stock item data, since the last time a prepare and export are done, that it will not generate a new version of the file and based on what you said, then the file sync will not occur.  I thought that was going to be my issue, but that would be way to easy to explain!!  After playing around with it some more, it’s just that the Process Export Scenario screen is simply not processing the export scenario no matter what I try and at the moment, I’m doing this outside of the automation, just doing it directly on the screen.  I am changing an item, before trying so that the data is different from the previous try, and whether I do either prepare or prepare and export, nothing happens  The screen seems to be behaving a little weirdly too although I haven’t had any experience with the screen before.  When I check the box and click process (after saving), I am seeing the checkbox  become unchecked.  From what I remember from other screens, that seemed strange.  Even did the prepare on the Export by Scenario screen and just tried the Export on the Process screen and again, it just doesn’t do anything. The status doesn’t change and there is no sign that anything happened.

Userlevel 3
Badge

Any ideas on how to export only prepared records?  Overwriting anything previously exported?

Userlevel 4
Badge +1

Can you share a bit more details of current process and the gole?

Userlevel 4
Badge +1

If you are exporting the data to a file, You can always name the file with the same naming convention, and it will override. 

Userlevel 3
Badge

Thanks for the quick response!!  I figured it out.  Kinda like going to the doctor and then feeling much better. :-)

Reply


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