Formula for putting As at Date functinality in a BAQ.
Please formulate a complete question. Refer to Tips for asking question on the forum - Experts’ Corner - Epicor User Help Forum (epiusers.help) for great suggestions.
The date i have highlighted. You are able to get the correct stokc status even when you select a past date
The date i have highlighted. You are able to get the correct stokc status even when you select a past date
Are you just trying to learn how to add Date Parameters to a BAQ,
or are you trying to figure out how to get something similar to stock
status of a certain date with a BAQ?
I know how to add normal date parameters.
What i want to achieve is getting information as at certain date in a BAQ
You really need to use more words. I understand if this is not your native language. Please try to add more information to your question. We don’t know what you want. Tell us what you are trying to accomplish, what you have tried so far. If you have any code or screen shots, post them here for review.
Short posts are not a problem if you include the right parts. Write your posts in 3 parts:
- State your business objective. Include the approach you plan to use to achieve this business goal.
- State your primary challenge(s). If you have an error with code, or compile, post it. If you have tried a filter, what did you try?
- Post a flat question that someone can actually answer. No one can answer your post if you don’t post a question.
Good luck! Message me if you need help formulating your post to achieve optimal responses.
Getting information as of a certain date is a complex thing to do with a baq. The challenge is that in order to retrieve such data, you must reverse transactions that have happened between “NOW” and your entered date.
In most places within Epicor that have an “as of” date, we are not using a BAQ to retrieve the data. instead, we have programs that extract the data, and reverse out values…
For example, for Inventory as of, the system must get the CURRENT inventory on hand, and then reverse out all the transactions from the PartTran table for the time period. The programming does this. in theory, this same thing could be done with a BAQ by using multiple sub-queries/union queries to combine the data, and give you the values.
2 Likes
and that is no small task!
Does this answer your question
I have created a BAQ which returns the last receipt date and last issue date for each stock item.
I have a from date and to date parameter.
When I run the report using a date range let’s say upto 31st December 2022, the output is not right.
There are those stock items whose last receipt or last issue date is > than 31st Dec 2022
In such a case I want the report to return the last receipt date or last issue date for that stock item as a date within the selected range.
2 Likes
Please give a hint how this is achievable through a BAQ
Did you put a criteria where ‘Last issue date’ >= Date_From parameter AND ‘Last issue date’ <= Date_To parameter ?
2 Likes
Great! Now we are getting somewhere! Like Brian asked, do you have criteria set in your BAQ? Let us see where that criteria is applied. If you can post the BAQ for review I bet we can solve this one pretty quickly. I know that dates can be tricky, particularly in the transactions tables.
I figured out this works well on an SQL Query rather than on a BAQ query