BAQ using First/Last/Each

I think that was because you were using the subquery top which only returns 1 row (or whatever you have set) for for whole subquery. Not because of the field you were using. Using the group by will act like what you describe used to be first each last in E-9.

1 Like

Dear Jose,

The difficulty is that a CustCnt entry can be for a customer, OR CAN BE for a ship-to of a customer. The way I read your post, you want the earliest created CustCnt record for each customer, regardless of ship-to. If that’s what you want, then the ConNum field will not be unique for your purposes and you’ll need to go with something unique like SysRow. It’s OK if your chosen field is unique in the entire table, as long as it’s unique to a customer which of course is true. It’s also OK if the chosen field is immaterial to your needs, you’re only using it as a means of strictly-less-than testing on the second copy of your table, and once you find the CustCnt entry for a given customer that has only a null record satisfying the test, then you’ve found the record you need.

For more information on the fields of CustCnt and how they work, pull-in the system query zIWCustCnt, and in the Query Builder / Phrase Build tab, click the Erp.CustCnt table, to the right appears field name/type/description which you can right-click copy to Excel.

Also, I realize I’ve given some information on how to solve this issue without really using subqueries. I tried to find a thread on here to reference and don’t see one. But I’ll be glad to point you in that direction if you’d like.

HTH
…Monty.

Thanks Monty!
With everyones help, i was able to get the one row of the contact per customer. i used subqueries and think i have a handle on it. Will keep practicing but all your help is much appreciated!

Jose