I have a report I am creating through an ODBC connection. The report includes several invoices, where each invoice has several products. There is also a table which contains all the historical price changes for each product (field: unit-price). Currently there are duplicate product records being pulled, one for each time there was a price change. Therefore, I need to filter my data so that only the most recent unit-price is shown (date field: effective-date). How can I do this via the "Select Expert?"
In short, show the product's unit-price for the most recent effective-date.
Thank you!
Best Answer
You'll need to create a sql-expression field to get the most-recent effective date, then use this field in the record-selection formula.
Record-selection formula: