SQL Query Help

lineamovil

Hello, I have a small query that I cannot complete. Hope you can help me.

Have a BO called INVOICE and another BO called CLIENT
In the BO INVOICE a have a RELATIONSHIP to CLIENT, named ClientINV

I have several INVOICES TO SEVERAL CLIENTS, so I need a query where I can see the last purchase of every client, I really cannot figure out how to do it. Any help will be great!

Thank you!

tford

Your subject line mentions SQL, but I'm going to talk about an AwareIM approach. Assuming you need to gather the last Invoice from all of your Client BOs in one place:

1) Goal: You will store the list in a reference attribute in either SystemSettings or LoggedInSystemUser

2) Clear out the reference attribute from #1 that you use to store the invoice instances.

3) Use a process that: finds clients in batches of 1

4) Within each client, find invoices for that client sorting with most recent first ... use TAKE BEST 1

5) Insert the invoice found in #4 to the reference attribute from #1

lineamovil

humm! I couldnt understarnd it. But I managed to make it work in Phpmyadmin, it showed the correct information. But when I paste this into the AwareIM SQL tab, in a Query, it just shows everything, just like if hadnt place the SQL sentence.

Any guess?

The sql string was this..

SELECT MAX( CARGONET_Remisiones.ID ) , CARGONET_Remisiones.EstatusTerminadoT, CARGONET_Remisiones.XFolio
FROM CARGONET_Remisiones
INNER JOIN CARGONET_Unidades ON CARGONET_Remisiones.Unidad_RID = CARGONET_Unidades.ID
GROUP BY CARGONET_Remisiones.Unidad_RID
ORDER BY  `CARGONET_Remisiones`.`XFolio` ASC