First Function in Epicor

,

Can you use First function in a BAQ? I need Epicor to take the first line and use it for something. Or is this only in SSRS?

There lots of ways to do it, depending on what you are after. Do you want to BAQ to only return one row? Then go to subquery options and choose TOP 1.

2 Likes

I am working on a report to compare AvaTax info to what is in Epicor. Problem is, it s a pain. Anyway, to the question at hand, is that when I bring in the Total Sales, I have to do it by getting the EXT prices from InvcDtl or InvcTax. However, it duplicates the total sales. So, I need to restrict the total sales to just the first line as it is correct then.

I not really sure how you are working this problem, but I’m wondering if you are getting duplicate rows because of something wrong in your query. I would analyze that first, because unless you have a really good understanding of why you need to throw out lines, that’s usually where the problem is.

No. In InvcTax you will notice that for each line it ahs a different tax amount. However, the total amount is a single column added in and it has the same number for every row for that invoice number no matter what. At least, this has been my experience. We have orders hundreds of lines long at times, really, so you can imagine the problem.

in a subquery, bring in your table, group by invoice number and total sales. Then rejoin to what you need.

1 Like