I’m a novice in BAQ and have been able to muddle through creating small queries. But I’m stumped at how to create a more comprehensive query or queries. I’m still waiting for IT to give me the ability to link Excel to Epicor 10. For now, all I can do is copy my BAQ query to Excel.
Any ways, I manage an Excel file and the worksheet contains data within Epicor and data outside of Epicor that I need to align to each order for management purposes. Ideally, I would like one BAQ query for all of the Epicor fields in my worksheet.
The tables I need to talk to each other are:
Contact (not critical yet)
Customer
InvcHead (not critical yet)
OrderDtl
OrderHed
Part
QuoteDtl
QuoteHed
ShipHed
ShipTo
TFOrdHed
I’m not concerned about the non-critical tables at this time.
I’ve attached a ‘Legend’ showing the Tables, Fields, Alt Fields, Worksheet Column Titles and Worksheet Column Numbers. The ‘999’ items are not in my worksheet at this time but is part of a project.
I would greatly appreciate expert guidance. Thank you in advance
Theres another module you can use called “External Business Activity Query”, have you tried that option?
also, if your Excel file data is static, I would recommend using UDTables to store that data so you can also use them on a regular BAQ.
more than happy to discuss your needs so we can figure it out a good way to make it work.
Hi Mauricio!
I’ve not heard of the External Business Activity Query. It must be different from the Business Activity Query I’m using?
Currently, my Excel file is not linked to any refreshable queries. I’m trying to get there, but IT is so slammed with high-priority projects, it’s hard to get help.
I have seen ‘UD’ as part of a field name, but otherwise clueless.
Thank you for responding and I look forward to learning more, if you are up for it:)
all good, no worries
so there are a few recommendations:
create an External BAQ to bring your data into Epicor and then use it to complement your Epicor data/BAQ. (I’ll try to get some docs for you).
you can also use a UD Table to store all your external data, but you may need to update it as often as needed; for that you can do an UBAQ (Updatable BAQ) to update those records or convert the UBAQ to an Updatable Dashboard, that way you can even update the data by literally doing copy/paste from your Excel file.
is your intention to have all Data in Epicor for reporting? if you are using this data for reporting purposes you can also use EDA, SS, or XLC for these purposes as all those tools will allow you to use external Epicor data.
External BAQs won’t connect more than one datasource. So I’m not sure that’s going to help you. You would have to import the data into epicor, or out of epicor to consolidate it somehow.
I have to have permission to ‘bring data’ into Epicor outside of the usual order entry process. I inquired about batch loading csv and to keep this short, that’s not happening.
I have a sneaky feeling access to a UD Table also requires high level permissions.
No, basically I want a query of all the fields I access in Epicor. I don’t wish to ‘add’ anything to Epicor. I just want to extract/link the data fields to Excel.
In a nutshell, I want to ‘pull’ data from fields I use from Epicor into an Excel worksheet. I have to maintain an Excel worksheet outside of Epicor. I manually enter that data. The data that is not in Epicor is not tied to another datasource.
I appreciate the input I am receiving. My responses may be delayed until tomorrow.
@amurdock made a snazzy excel sheet that can bring in BAQ info into excel. I haven’t dug into it myself, so I don’t know how complicated it is to set it up, but I believe he’s already done much of the heavyu lifting.
once a BAQ is created in Epicor, you can use the API services and pull them in MS Excel using the ODATA feed services, have done this many may times, is pretty easy
I’ll back up one level - Is there a good reason to export the Epicor data into Excel (besides 'that’s how we’ve always done it)? What is done with the data? Maybe we can help come up with a more efficient process to get to the end-goal (something like “we want to see the open customer orders, and where they are in the process, so important ones get expedited”).
You could create a BAQ to show the data in a table, and then make an Epicor dashboard, where the data is up to date. You can deploy the dashboard to specific security groups or users. BAQ’s can also be updatable, so folks can make bulk changes in the dashboard, instead of having to go to each screen and do it one at a time.
There’s free courses on how to create BAQ’s in the Epicor Learning Center. The Epicor user, application, and tech guides also contain a lot of info. And can’t forget Youtube!
Good morning, @askulte I appreciate your thoughtful response.
I manage large system orders, which are more like projects. These ‘orders’ are not your typical spare parts orders that customer service enters into Epicor, and they are done. These are more complex. There is data I need to track outside of Epicor before, during and after entering the order.
At the very least, I just want to create a BAQ that shows all the Epicor data fields I need. As I add tables and data fields the number of System orders tends to change, which means I may not have these tables in the correct configuration.
Ideally, I would like to be able to link to some of these fields, e.g., customer data so I can access them from my worksheet.
The dashboard sounds great, but I have no idea how to create one.
Bulk changes: I worked with IT to see if I can upload the data from Excel and without a lengthy explanation, this option was impractical for how I manage System orders. There’s nothing ‘bulk’ about it.
Last time I checked out the Epicor Learning Center and YouTube most of these seemed to be directed to people with a higher-level of acumen than I have. I had to keep looking up terms to get through some of these instructions and ended up more frustrated. I’ve had some luck with Copilot AI.
“Use API Services”, sounds like I’ll need IT to get involved. I’m trying to avoid it because they are slammed with a major project that’s put my other projects on the back burner.
Nothing at the top, but at the bottom it shows my name, our company name, plant, then: ‘Company initials here’-ES-01/EpicorERP. Like this: XXX-ES-01/EpicorERP. Not ‘.com’.
For now, linking to Epicor is secondary to my original request of how to create a complex query or queries to get the field data I need, per the attached file. Can I make one query with all the fields or do I have to create separate smaller ones?
