FIRST, LAST record in BAQ for E10

Yahoo Archive
1
Does anyone else have any experience with the "First/Last" criteria in E10?

Thanks for the help.
B-


2

Hello,


We are running 10.1 and are trying to create a BAQ that has 7 tables in it and one of the tables had the 'first' criteria set on it. We can't figure out how to get this to work in 10.1? When we try using the subquery options, it looks like it is applying it to the whole BAQ and not to the specific one table. Anyone have an idea how to do this?


Thanks


3
I the Subquery you can say Top(1)


Jose C Gomez
Software Engineer


T: 904.469.1524 mobile

Quis custodiet ipsos custodes?

On Wed, Aug 10, 2016 at 2:10 PM, detroitrdwings91@... [vantage] <vantage@yahoogroups.com> wrote:

 
<div>
  
  
  <p></p><p><span>Hello,</span></p><p><span><br></span></p><p>We are running 10.1 and are trying to create a BAQ that has 7 tables in it and one of the tables had the &#39;first&#39; criteria set on it. We can&#39;t figure out how to get this to work in 10.1? When we try using the subquery options, it looks like it is applying it to the whole BAQ and not to the specific one table. Anyone have an idea how to do this?</p><p><br></p><p>Thanks</p><p><br></p><p></p>

</div>
 


<div style="color:#fff;min-height:0;"></div>

4
Jose,

I tried that but my BAQ will only return one row of data. If I remove the subquery, then it shows all of the records. How does it know which table to find the FIRST or LAST record on? 

thanks
5
You have to do it at the subquery level not the top query.


Jose C Gomez
Software Engineer


T: 904.469.1524 mobile

Quis custodiet ipsos custodes?

On Wed, Aug 10, 2016 at 3:16 PM, detroitrdwings91@... [vantage] <vantage@yahoogroups.com> wrote:

 
<div>
  
  
  <p>Jose,</p><div><br></div><div>I tried that but my BAQ will only return one row of data. If I remove the subquery, then it shows all of the records. How does it know which table to find the FIRST or LAST record on? </div><div><br></div><div>thanks</div><p></p>

</div><span class="ygrps-yiv-668397781">
 


<div style="color:#fff;min-height:0;"></div>


6
Jose, 

When I setup my baq using your above instructions, I only get one record. If I have  two tables in my BAQ, Part and Jobpart, I want to see the last or first JobPart record per part from the part table. When I set the BAQ up using your above instructions, The BAQ returns just one row, NOT one row per part. Is it possible to get a FIRST or LAST per record in my toplevel query using a subquery? 

SubQuery 1 has Part table
SubQuery 2 has JobPart
SubQuery 2 is set for Top One Result
SubQuery 2 options are:
Type: InnerSubQuery
Result Set Rows: Top
Rows Number: 1
"With Ties" checkbox checked and unchecked <--Tried both ways and still didn't get more than 1 record.
These are linked by PartNum and Company via an Inner Join
I sort SubQuery2 by SysRevID by descending order, also tried ascending order <--Tried both ways and still didn't get more than 1 record.
These settings result in only returning just the one record.

Thanks