I’m not saying emailing CSV’s is better than using the API, just that the task I was given is to get these emails setup. I did ask if we’re open to using the method you mentioned above, but have not received a response yet.
With that being said, I’ve attempted to setup an excel document that pulls the data in from an odata feed and am not able to find a way to do this that keeps my API key from being visible. Even making my API key visible I’m running into issues. I have searched and am not able to find a straightforward walkthrough of setting up the API in excel.
I have a url to my BAQ where I’ve begrudgingly already had to include the API key exposed. Even with the key, my Epicor credentials are still required (I am also prompted to enter the credentials in the browser). In the browser with my credentials and the api key in the URL, I am able to retrieve the data. In Excel with my credential and the api key in the URL, I am getting this error:
Any clue on a way I can do this? These sales reps are not directly employed by my company and do not have their own Epicor logins. I would really prefer them not being able to see the API key as well. In order to keep from making 26 separate BAQs for each rep, we decided to make a master BAQ with all data and would like to pass the repcode as a parameter. If the API key is visible, reps will be able to see information for every rep if they’re tech savvy enough.
That is wise. Having an intermediate service between Kinetic and the Reps would help hide the API-Key from them.
Is your company a Microsoft 365 user? If not, do you use any file sharing services (DropBox, Box, Google Drive) to pass information to the external reps?
We are on Microsoft 365. But to my knowledge we just communicate with reps via email. Is there a solution you know of using 365 to do what you suggest?
Personally, I would create a Team for each rep and invite them to it. Add a channel called “Sales.” Each channel in Teams gets a Document Library by default. You can get the URL for the Library to use for the MS Graph calls above from the Copy link item.
If they insist on emailed reports then yes using BAQ report with APR to route to email is the way to go. @Mark_Wonsil suggestions are better overall but may not be what your reps want, we have some who insist on emailed reports even though they have a Teams portal to see the latest reports.
Randy brings up a good point. It they insist on email, you can also use the MS Graph to send email, create a draft of the email for review before sending, send all drafts, etc.
In this case, is there a way to pass a parameter to the BAQ? I see that I can use the filter, but I have some questions. I am a little confused on what it means by breaking, I must break before I can use the filter and other rules I’d like to use. I am also wondering if there is a way to iterate through a list so I do not have to have a break, filter, and email for all 26 reps. I can copy and paste if need be.
I didn’t want to keep necro’ing this thread, but since we are still beating this dead horse:
I would use power automate, make a file somewhere with rep’s email and report parameters then iterate through that list to pull the odata, load it into an excel sheet, and then email the rep the excel sheet.
An Epicor Consultant would tell you to spend a bunch of money on automation studio to do the same thing.
Normally a report creates a single output. Even reports like printing Invoices. While the data set may be for 5 invoices (pretend they are 1 page each), you get one report containing 5 pages (all 5 invoices in one report).
Breaking allows for multiple reports to be created when usually just one is. What field you break on is what is field is used to group the data before creating individual reports.
In my example of “5 invoices”, if you mailed that report to each customer they would get the other 4 invoices too. If you made it break on the customer, then 5 reports would be created. If two of the invoices were for the same customer, then those two would be in a single report (still as two distinct invoices in one PDF), and there would be 3 other reports for the other customers.
Thank you Calvin, I was able to get mine working. A good first iteration of it at least and I’m getting the hang of it. Really appreciate the response.
Another thing you can do with APR is make a condition, look for the @ in the email and if it doesn’t exist, send the report to someone else with a new template.
That template should say ‘when trying to send sales reports, an email was not found.’ Have that sent to a person or group so they will set it up properly. Maybe a sales person was not assigned or something like that. This is our common practice with APR.
Also in the past we have sent shipment reports for the last 5 days instead of just one day, that way if something glitches they can just wait a day. Sales people don’t care, they just want their $$$
