Is it possible to create a temp table while running a BAQ query? i know it’s possible when using SQL directly, but i wanted see if i could created a temp table in a BAQ? i wanted to created a temp table and store some data in there and pull that information along with other details from the main query.
You can do a subquery, I’m not sure if that’s the same as a temp table, but basically you make a subquery and feed it into the top level, so it sounds like it should work the same way.
edit: So I did some reading, now I understand a little better what you are trying to do. I’m assuming you are pulling a lot of info and your query is slow? That’s why you want the temp table? As far as I know, the BAQ editor does not have the ability to make temp tables.
The Executive query can store cubes, which may be similar but probably not what you are looking for exactly.
1 Like
Yes, but can i manually input data into the subquery rather than information queried from the system?
What my whole idea was to track employees on how much vacation hrs they have left.
- created a baq that calculates total hrs of vacation they took for each employee and date
- Need for a temp table in a BAQ to load the total vacation hrs available to each employee (so the temp table should have employeeID and total hrs available for the year for each employee )
- Subtract from the total hrs available, the number hrs each took as the weeks go by throughout the year.
I’m not sure what you mean by this, can you elaborate?
Where is the data coming from? An external source?
but can i manually input data into the subquery rather than information queried from the system?
i wanted to load the data into temp table myself which is not in Epicor.
Where is the data coming from? An external source?
Yes, it’s external source (off system), since we dont have payroll management in Epicor, we use ADP. I could ask our HR dept to give me total vacation hrs available for each employee and then need to load that into the “temp” table. I could create main query to subtract from the temp table the hrs each employee took for vacation and come up with how many hrs are remaining for each employee.
So you can’t mix internal and external within a BAQ, you can do both, but not together. But you can bring in info into Epicor, and store it there.
For this case, If it was me, I would populate a UD table with information you need. That’s what they are there for. Then you can make the query that grabs the data from the UD table. To get it loaded in, you can either use DMT (if you have the license), or customize the UDEntry screen for the table that you are using, You can copy paste in to the grid view to bring in the data. A third option is to create a UDdashboard which can basically act the same way. If you really want to, you could also do a service connect workflow. There are lots of ways to skin that cat.
Does that make sense? Let me know if you need more details.
Thanks Brandon. Going the UD table was in the back of my mind, but wanted to make sure if the BAQ option was available. Thanks for your help.
2 Likes