BAQ with SubQuery (finding Currency Rate)

[traceyash]

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!

Thanks for your assistance.

[Banderson (Brandon Anderson)]

It should work fine as a BAQ. Where are you getting stuck?

[traceyash]

I can’t work out how to do the sub select in a baq. Whatever I do the Where statement is on the outside rather than within the select statement.

[Banderson (Brandon Anderson)]

Check out this post, it shows you how to set up a sub-query.

BAQ results on two lines rather than on 1 line

Below is a pictorial step by step. I didn’t put in every single click, but it should get you close enough to see what’s going on. Here is the menu location. It exists in a couple of other places too. [image] Once you open it, create a new query [image] <img src="/uploads/default/original/2X/c/c0280b67c34d3d538a5e35f3da171738043590ac.png" width=“690” [image]height=“422”> [image] [image] [image] Now the scary part, we are going to turn this into a sub query, [image] [image] […

@traceyash

Whatever I do

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.

[knash (Ken Nash)]

What does the BAQ look like? Can you upload it? What does the query phrase show? You can do this all within the BAQ as Brandon mentioned.

[traceyash]

Hi,
Thanks.

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.

Hope this makes more sense???

Thanks,

Tracey.

[A.Baeisa (Al)]

could you put some example data under each table and how would you like the result to be ?

[Banderson (Brandon Anderson)]

@traceyash

If on the CurExRate SubQuery I change it to Return Top 1, it only returns 1 row and not one per LaborDtl.

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.

[traceyash]

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.

[Banderson (Brandon Anderson)]

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.

[traceyash]

Yes but I assume I need to add the CurExRate table again as another SubQuery and do the table join on
LaborDtl = 2ndCurExRate.Company and

Calculate_MaxEffectiveDate = EffectiveDate

It doesn’t let me do the join as it doesn’t recognize the calculated field.

[Banderson (Brandon Anderson)]

@traceyash

It doesn’t let me do the join as it doesn’t recognize the calculated field.

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)

I think it’s something simple.

[traceyash]

Yeah I was in the joins.
When I get back to the office tomorrow, I’ll send through some screenshots. Thanks for your help - enjoy your day!

[traceyash]

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)

[Banderson (Brandon Anderson)]

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?

[traceyash]

Hi,

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…

Thanks.

[Banderson (Brandon Anderson)]

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.

[Banderson (Brandon Anderson)]

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.

Previous Operation in BAQ

yes you can with a few limitations. Being that there may not be a previous op, you may return a null value. You have to use a inner sub query and that will limit you to returning only one column. If you need multiple columns with this method you may just have to make more than one query. This is the only method i know of at this time, I’m sure there may be other methods. create a inner sub query [image] bring in the joboper table (you’ll need to assign an alias), create links under subqu…

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.

In your currencyOperations subquery, remove all of the field except for the currency

In your Subquery criteria, add these two criteria as shown. This will link them to the upper (or middle in this case) level

In the subquery options change the options to top 1

And when we do that, we need to order the query so that we get the right one, we do that here.

Now we go into the job operations subquery and add the field like shown.

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.

Let me know how far you get with that.

[traceyash]

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.

[A.Baeisa (Al)]

if this solved your issue, then could you please and kindly mark @Banderson post as solution.