Finding last occurrence of sales

[martbabs]

I have a database of invoices containing fields showing client name, job title and invoice date. Clients have multiple invoices over the past years. I want to show only the last invoice from each client. How do i do this please?

Thanks

[Inky Phil]

Hi Martbabs and welcome to the forum.

Create a self join relationship based on customer to customer .

Now create a portal that uses records from that self relationship and set the portal to show only 1 row.

Finally set the portal to sort the records by invoice date descending.

This will show you the last invoice for any given customer. If you exclude scroll bars in the portal this is the ONLY invoice you will be able to view. If you include scroll bars you will be able to scroll down to older invoices should they exist

HTH

Phil

[martbabs]

hiya

thanks for that - what i really want is one list containing all of the clients with their last invoice date - i want to see who we have worked for in the last two years - this will become our xmas card list - any ideas?

thanks

Edited November 29, 2005 by Guest

[SlimJim]

Follow the instructions given by Inky Phil to set up the self join and sort within the relationship rathyer than in the portal.

Set up a layout consisting of the client name and the related field :invoiceDate from the relationship (no portal) this will show the first record in the relationship which, with the sort specified will be the last invoice date.

[martbabs]

thanks again guys

but i need to end up with a list of clients and dates on one record - where the client only appears once with their last invoice date. Unless I've not followed correctly, this still lists all invoices but listed on seperate records

hope you can help

[Søren Dyhr]

I do hardly ever endorse the use of repeating fields, since they usually becomes addictive to the newbe, that then decides to abuse them all over the place ...I can't caution too much against such practice, but here's actually a place where they come in handy!!!

This is BTW a variation on this: http://www.kevinfrank.com/download/repeating-lookups.zip

--sd

Invoices.zip

Edited November 30, 2005 by Guest