My Vendors (AP303000) screen in Acumatica for that vendor.
Basically, all you need to do is the following:
- Start with an Excel file that has a list of vendor IDs.
- Go to the Vendors (AP303000) screen in Acumatica, pick a random vendor, and click Help -> Get Link in the upper right-hand corner.
- Copy the External Link: value to your clipboard.
- Paste the External Link: into an Excel formula like this:
=”http://localhost/AcumaticaERP/Main.aspx?ScreenId=AP303000&AcctCD=V000007″ - Change the hard-coded vendor ID to an Excel cell reference like this:
=”http://localhost/AcumaticaERP/Main.aspx?ScreenId=AP303000&AcctCD=”&B21 - Wrap the link in the Excel HYPERLINK function like this:
=HYPERLINK(“http://localhost/AcumaticaERP/Main.aspx?ScreenId=AP303000&AcctCD=”&B21,B21) - Hide the original column in Excel that had the vendor ID and just display the new vendor ID with the hyperlink.
- Clicking the hyperlink for a vendor should take you to the Vendors (AP303000) screen in Acumatica for that specific vendor.
- You will need to login to Acumatica and your login will need permission to the Vendors (AP303000) screen.
Here is a quick video which walks through the steps:
The hyperlink issue is a known bug, AC-47803. Excel messes with the User Agent, which breaks our browser detection. We’ll try to fix it ASAP”
Tim,
We spoke on email about this, but I thought summarizing it here might catch the eye of someone in the know…
Following the steps above, I created columns in my table (fed from my OData connection to Acumatica, OF COURSE!) that are the Hyperlinked version of the “source data” which was just – in my case – the Customer ID or Invoice RefNbr. The link itself is fine – as I can copy and paste it into my browser(s) and it will take me to the Acumatica login page (assuming blank browser session to begin with) and then once logged in, will redirect me to the specific Invoice or Customer page from the table. Essentially – “works as expected” as a hyperlink.
However, WITHIN excel, and using the HYPERLINK() function, when I click on the link (and yes, hovering over it DOES show met he correct URL I tested with), lo and behold – a Windows Security pop-up comes up asking me for credentials to .acumatica.com. No valid credentials were successful in opening the chrome tab (or IE when I reset my default in testing).
When I clicked “Cancel” on the Authentication pop-up, it would actually open a tab in my default browser – but it was the “Browser Not Supported” error screen from Acumatica. (https://.acumatica.com/Frames/BrowserWarning.aspx)
Oddly enough – when I publish this to OneDrive and open it on my iPad via the Excel App, it works fine.
My research (aka – targeted Googling) leads me to believe that something within the OS itself (or Excel 2013 – but I don’t have many ways to test earlier versions).
I believe it is related to the “Microsoft Office Protocol Discovery” feature in Office 2013 (Ref: https://support.microsoft.com/en-us/kb/838028 – thought this says it’s for Office 2003, so I cannot verify) or possibly a yet-to-be-figured-out “feature” of Windows 7.
If anyone reading this has any insight, thoughts, or questions, please feel free to toss them my way! I created a pretty bad-ass report from Acumatica, and the users really want to “drill into” the related screens if we can make this work!!!!
Thanks!
Brad K.
Hi Brad,
Thanks for all the detail that you provided. I did a test on TryAcumatica.com without the HTTPS (HTTP instead) and I get the same error. In the video above I was using my local instance of Acumatica which isn’t the case in the real world. There is something different about a “real” URL I guess.
If you paste the following formula in Excel and click on the result, you’ll notice that you get the same error (note the HTTP instead of HTTPS):
=HYPERLINK(“http://tryacumatica.com/Main.aspx?ScreenId=AP303000&AcctCD=AAVENDOR++”,”a link”)
So, I’m wondering if Acumatica sees Excel as being the “browser” which is why it’s not supported.
Tim,
Have you attached wireshark or some type of packet sniffing tool to your network interface to grab the HTTP GET request and see if there’s anything in the HTTP header that’s asking for something funky?
My Acumatica instances I have handy to test with are all HTTPS, so I don’t know that I’ll see any of that as it would be encrypted.
Brad
Brad,
Thanks for the screenshot from wireshark. It sure looks like Excel displays its browser type as “Microsoft Office Excel 2013” which must not be a supported browser in Acumatica.