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.
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.