Hi,
I have the need to write a BAQ which shows the Labor Transactions in both the Book Currency and another Currency.
If I write it in SQL, I have
SELECT l.Company, ClockInDate, (L.LaborRate + L.BurdenRate) * LaborHrs LaborActUSD, L.JobNum, ( ((L.LaborRate + L.BurdenRate) * LaborHrs) *
(SELECT TOP 1 CurrentRate
FROM erp.CurrExRate
WHERE CurrExRate.EffectiveDate <= l.ClockInDate AND TargetCurrCode =
‘AUD’ AND RateGrpCode = ‘Main’ AND Company = l.Company
ORDER BY CurrExRate.EffectiveDate DESC)) AS LaborActAUD
FROM erp.LaborDtl L
Is there a way to do this in a BAQ or should it just be done in SQL and referenced as an external BAQ. I have quite a few reports that now need to find the currency rate that a transaction was ‘most likely’ converted on!
Check out this post, it shows you how to set up a sub-query.
Also, it’s really hard for anyone to help you when we don’t know what it is that you did. I’m sure it’s something simple, but if you don’t explain at least some of the things you tried, we have no way of knowing what you are missing.
Sorry I will add some more details. I’m in Western Australia so not at the office currently. I have created a number of BAQ’d and am quite familiar with them.
So far with this data I have tried adding two tables LaborDtl and CurExRate in the same query. This didn’t work as you cannot link directly on ClockInDate and Effective Date as there isn’t necessarily an EffectiveDate for every ClockInDate. I then added
the LaborDtl as a TopQuery and then CurExRate as a SubQuery. I used the Table Relations on LaborDtl.Company = CurExRate.Company. The table criteria was TargetCurCode = ‘AUD’.
In the SubQuery Criteria (on the LaborDtl) I had LaborDtl.ClockInDate >= CurExRate.EffectiveDate.
If I run this, I get the LaborDtl record repeated for every date there is a currency record prior to the labor date - which makes sense.
If on the CurExRate SubQuery I change it to Return Top 1, it only returns 1 row and not one per LaborDtl.
I have read a number of help records but most of them refer to two tables where there is an actual link (TranNum = TranNum or PartNum= PartNum). I don’t know how to do the link where I basically want the CurExRate record that is the max record prior to
the date of the ClockInDate.
You need to use and aggregate function in a calculated field and group the records. Max(LaborDtl.ClockInDate ) then group by in the query using the check boxes.
I can do that on the LaborDtl Query and I can get the max(EffectiveDate) I’m not sure then how to get the currency rate which relates to that EffectiveDate.
When you do the joins to your subquery you can type in < or > or <= or >=. It’s a little weird because you almost never do that so you don’t think about it, but you can.
It should. Are you in the joins? Or the subquery criteria? You should be in the joins.
I know that you aren’t in the office, but when you get back, screen shots and/or short videos would be helpful (I recommend greenshot and screentogif for doing those)
Hi,
I have uploaded the BAQ here. I have tried a few different ways but cannot seem to get the joins correct. I assume the best way is to join the Currency table twice? ProjectActuals.baq (63.2 KB)
I’ll have to open it up tomorrow, but by join twice, you mean add two rows in one join with one of them <= and the other >= right? You are talking about getting the labor detail date within the effective date range of the currency?
No – by join twice I mean add the CurrExRate table as a subquery twice. Once to get the Max(EffectiveDate) and then once to get the corresponding Currency Rate.
Ultimately I am trying to get every Labor Dtl Record with the most likely exchange rate that would have been used on that day to try and run a report in the currency of the Project as well as the Book Currency. The challenge is that we
load a currency rate for every week day but if someone books time on a Saturday or Sunday, I need to go and find the previous Friday’s currency rate…
ok, so I logged in to take a look because I was curious. We don’t use multi currency so I can’t really see any data to see if it’s working. Let me see if I can tell what you are trying to do.
ok, so let me see if I get this right. You need the row on the currency table with the date as close the the labor detail record previous to that date. You only need the single value from that table right? Which is the rate on that date. Is that correct?
I would tackle that using what I call a mini sub query (I don’t know what the correct name is)
Here’s a link to a step by step of that if you want to see where I learned it.
You’re on an older version of E10 so I’ll give you some screen shots of what I did
In your job operations subquery, get rid of both of the other subqueries.
So that should get you the currency rate for the date when the laborDtl record was created. I think you should be able to take it from there.
I don’t have any data to check any of this with, so hopefully that makes sense and I didn’t mess anything up.
One more note about the date and the ranges, I think there might be a bug where the date fields won’t compare correctly, and if you run into that, make the field equal to an expression and type the table and date field in that way. I don’t know why that way works, but I think I remember having to do that before.
FANTASTIC - thank you so much. So basically because I had three fields (rather than one) in the Currency Operations sub query it wasn’t working and wouldn’t allow the Sub Query Criteria.
I didn’t know you could reference a single field sub query through a calculated field without joining that sub query in. Yes in essence a mini sub query or a sub sub query!
Thanks for your help.
