Latest date from table 1

[mwmark]

I am creating an inventory reorder report and have a similar question.
I want to display only the last date purchased of the selected sku_id.
I grouped by Sku_id and suppress the details, then added the purchase date (from a seperate table) to the group footer with other displayed info. This gives me the first purchase date not the last.
All groups sort accending.

What am I doing wrong???
Crystal 10

 

[synapsevampire]

Similiar question to what?

Please post your Crystal version and the dtaabase being used.

Use Report->sort (it'll be slightly different depending on your Crystal version) and seletc the date and ascending.

Or use the Report->Selection Formula->Group and place:

{table.date} = maximum({table.date},{table.sku})

Then you can just use the details for display.

-k

 

[mwmark]

similar to thread - Showing latest date from group
Crystal Version 10 (see bottom of orig Post)
Database Mysql 4.1

The Report sort worked!

But... It is omitting items from the sku_id table if there is no data in the date purchased table (ie.new items)

Thanks

 

[lbass]

Use a left join FROM the sku table to the purchase table. Then change your group selection formula to:

isnull({table.date}) or
{table.date} = maximum({table.date},{table.sku})

-LB

 

[mwmark]

lb, this didnt work
should the Left outer Join be enforced or reversed or???
I also noticed that some of the dates are not showing from the purchase table (where data exists.
The results of what is displayed checks out

 

[lbass]

Do you have any selection criteria on your purchases table? This will undo the left join in effect.

Please show a sample of your data before group selection and then one after. Then also show what you expect to see.

-LB

 

[mwmark]

No selection critera on purchase table but several on the sku_id table


the selection formula doesnt seem to have any effect
here is what I have :
isnull ({ic_inventory_receipt_item.INVOICE_DATE}) or{ic_inventory_receipt_item.INVOICE_DATE}= maximum({ic_inventory_receipt_item.INVOICE_DATE},{IC_SKU.IC_SKU_ID})

Sample
skudescription lastpurchdate qtypurch skucost otherdetails

You can go to a web page I created to see what I mean

http://www.mwtire.net/tek.htm

 

[lbass]

I can't follow your linking (you should click on auto arrange to clear up the view). Note that once you use a left outer join, all tables to the right of the left outer join need to be left joined.

-LB