Automating Generic Inquiry with Import Scenario
I've developed a query which returns data which is then used as the source of an Import Scenario I built that creates Journals.
At the moment, the user needs to export the query to excel, edit the excel and delete the 8 header rows added by GI, upload the new file version to the Import Scenario, Prepare & Import and then it's done (they then just release the journals, which can stay as a manual process).
Is it possible to provide a more seamless integration then the above?
Checkout the new Business Events (SM302050) screen in Acumatica 2018. It sits on top of a Generic Inquiry and allows you to feed the data set to an Import Scenario.
As you can see in the screenshot above, you reference the Generic Inquiry in the Screen Name field.
Then you have the option to Trigger by Record Change or Trigger by Schedule.
If you choose Trigger by Record Change, you have a bunch of options: Field Changed, Field Value Increased, Field Value Decreased, New Field Value, Old Field Value, Record Inserted, Record Deleted, Record Changed. So it's very flexible.
Then, you can either take the Generic Inquiry and feed it to an Import Scenario. Or, you can feed it to an email notification:
Unfortunately MYOB hasn't released that version yet to Australia so we don't have Business Events yet.
But actually, there's still an element of user intervention required here. What would be nice would be to integrate the query download and import scenario so the user could see the Generic Inquiry on a page and then an "Upload to Journal" button. The button does all the remaining work:
- Download the file to excel
- Strip the first 8 rows out of the file
- Import the data via pre-defined Import Scenario
How hard would this be?
Hmmm, sounds interesting, but I think you would need a customization for this.
Since the data is on the screen, you might not need the customization to download it to Excel. It might be possible to have a custom Data Provider created that would basically pull from the Generic Inquiry. If you could pull that off, then you could probably just have the user run the Import Scenario from the menu. Hitting the PREPARE button would pull from the custom Generic Inquiry data provider and allow them to review the data on the screen. Then clicking the IMPORT button would run the import.
Also, FYI, in Acumatica 2018 R2 you won't have to have to manually remove those header rows from the exported Excel file every time. Check this out: