How to find the last input by product in a table which contains different products
How to find the last input by product in a table which contains different products
(OP)
The table looks like this

The columns from left to right are
product, price, date of input, vendor
The list is sorted by product and their date of input
I would like to find in this sample
only the products with their last input (the youngest input by product)
In this sample it would be
record no 3 (Hefe)
record no 7 (Heringssalat)
record no 9 (Honig)
and only that must to be shown in a new view.
Thanks for the correct select/scan command.
Klaus
Peace worldwide - it starts here...

The columns from left to right are
product, price, date of input, vendor
The list is sorted by product and their date of input
I would like to find in this sample
only the products with their last input (the youngest input by product)
In this sample it would be
record no 3 (Hefe)
record no 7 (Heringssalat)
record no 9 (Honig)
and only that must to be shown in a new view.
Thanks for the correct select/scan command.
Klaus
Peace worldwide - it starts here...
RE: How to find the last input by product in a table which contains different products
CODE
then a select like this would work
CODE
RE: How to find the last input by product in a table which contains different products
CODE
To avoid some problems and have a better performance, it is advisable to not store the data you have in this way. There should be a products table that has an id per productname, then a vendors table with id and vendor name. When you group by an id, no matter if it is a 4 byte integer or a longer guid, that's faster done than grouping by a long char field like the name is. besides, it's in general always advisable to store your data in a normalized database design.
our product_prices table then has a productid instead of productname and a vendorid instead of vendorname. For (humanreadable) display you always can join in the names and show them instead of ids, but ids are better performing and also don't have problems with comparisons in different collations and codepages chracter fields could have. It simple takes less times to group by data in less bytes, to make the explanation short.
Then this becomes your query:
CODE
Or, if you're only interested in the latest price and remove all records of other vendors with an older price, then don't group by vendors, only group by products:
CODE
Another idea would be to store the latest price you know of a product per vendor in a table current_vendor_prices. And store historical prices separately in an archive table product_price_history. Both tables have the same structure product_id, vendor_id, price and price_date. And whenever you have a newer price for a product and vendor you take the record you have in current_vendor_prices and add it to product_price_history, then update the price and date in the current_vendor_prices table and use that table instead of a query.
So just using a current_vendor_prices will show you the result as per the first query without actually needing to query, you just eed to join in the product and vendornames, but not need to group by anything, as you only have latest prices records anyway. And if you then only want the latest prices independent from the vendor, you can still use a group by query again:
CODE
And again, if you're always only interested in this list independent from vendors, you can only store those records in Current_Product_Prices and whenever you have a new price input, no matter from which vendor, you store the current record in the history table and update, price, price date and vendor_id in the current price record.
It's a matter of preferences and taste how exactly you structure your data, but at a minimum keep separate tables for the separate lists, like vendors and products, and in tables combining data use IDs as references to these basic data lists. Without deep-diving into database design theory you can easily think of these separate lists that make sense and which become providers of names of vendors, products, etc. for human readable display on the one side and simpler, faster, not error-prone processing by integer IDs or guids on the other side.
Chriss
RE: How to find the last input by product in a table which contains different products
Simply read the table in a loop line by line and if the name change, then print the previous line.
RE: How to find the last input by product in a table which contains different products
if you like the idea of a current price list, no matter if it includes only one record per product or per combination of product and vendor, then one more interesting topic will be an update trigger you can do in a DBC. That will run before the actual update is done and so in that moment, you can store the current record in the history table and then simply return .t. to allow the update.
Here's a little demonstration of that using a copy of the Northwind database:
CODE
This will a) copy the northwind database from the VFP samples subdirectory into the current directory (therefore first create a new directory and change into it), then adds a table product_history based on products with just an additional field "until" that will store the datetime() until the record was valid when a products record is updated or deleted.
The interesting code mainly is the update trigger, you can also modify a database and go into the proecedures manually and then code in there, no need to understand the append procedures command.
Any change you make to any product record now will trigger the routine in the dbc and that will copy the current record into history with the current datetime put into the "until" field. That denotes until which datetime that record was this way. For comparison you can verify that the products record in the original northwind database has the value that was stored into products_history.
In this case you could use it to get a history of prices, units unitsinstock, but also any other fields of the products table. And the trigger code is quite short. Nice, isn't it?
It's important to notice one detail: When the trigger starts you don't get hold of the old value, so at best you have to turn on buffering, which my demo program does. A trigger always starts with the workarea that caused it, that means it has the products.dbf open in it and it is on the record that is affected by the insert/update or delete - whichever trigger type is used. With or without buffering, you access the already changed record in the current workarea, but the dbf still has the old record, because your code now mainly decides whether that change will be allowed or not by your return value. To get at the dbf fields values the select from products does that, as the dbf still has the old values, even though a scatter would be simpler and suffice but it wouldn't read the old values and we need the old values for the history.
Yet another reason to always work with buffering, but that's not the point. You see that it's actually only a few lines of code in triggers which maintains the history of data and allows you to concentrate on the latest current data only in your main tables. Any "historical" values can be queried from the history table. That's the main point, avoid putting data you usually want to filter out, just store what you actually use mainly and put anything else separately. That's how I would handle any kind of data instead of mangling old and new data in one table and then always needing filtering. The "historic" data is rarely of interest, maybe to query price inflation but usually, you want to only know the latest price you have.
There are more advanced ways, to only store the actually changed fields, for example, but this way, having a table copy with just the added "until" field is enough for simple purposes.
Chriss
RE: How to find the last input by product in a table which contains different products
Once these triggers are set up you only maintain the latest prices in your main table and get the rest done automatically.
It just works, no matter if you use UPDATEs, REPLACEs or even open up a BROWSE window of the dbf and change a price and vendor there, the trigger automatically will move the current record into history.
If you use table buffering it needs a TABLEUPDATE() to trigger the update trigger, but that's no extra effort, that just is needed anyway when working with buffering.
And, well, of course, I assume so far you did always only add records by append blank or insert. Well, now if you have a product and vendor already in your list, you have to update the price, not add a new record, so at best you make the combination of productid and vendorid a candiadate index that disallows two records of the same combination and get used to maintaining prices by updating existing records unless there is a new combination of product and vendor for which you of course still need to insert a first price. Anyway, as the old data seems to be valuable to you anyway, the history stores that, but there is no work for having the latest prices list, that's simply in the main dbf.
Chriss
RE: How to find the last input by product in a table which contains different products
Thank you very much for your valuable contributions, each of which deserves and has received a star from my side.
It's not just the program code that helps me a lot. The recommendations for better file structure, storage of historical data and the use of triggers are also interesting new things that I didn't know before. I'll go through all of this in detail again after our vacation.
Thanks again.
Klaus
Peace worldwide - it starts here...
RE: How to find the last input by product in a table which contains different products
das würde auch gehen:
SELECT NAME, PRICE, MAX( PDATE ) AS MAX_PDATE FROM myTable GROUP BY NAME INTO CURSOR myCursor
Grüße
Thomas
RE: How to find the last input by product in a table which contains different products
VFP9 would not execute this and throw the error "GROUP BY clause is invalid.", you can SET SQLBEHAVIOR 70 to overcome this, but look into an example:
CODE
That's not what Klaus needs. To get the full record of a product grouped by names and with max pdate, you first need to determine that list of only name and max_pdate and then join back all other fields of the table, otherwise you don't always get the data from the same record that has the max(pdate).
It also throws an error with VFPs engine behaviors 80 or 90, only VFP7 or older versions allow this group by and that's not a feature, it's a bug that was resolved with VFP8.
Chriss
RE: How to find the last input by product in a table which contains different products
you are absolutely right, this only works with SET ENGINEBEHAVIOR 70.
Regards
Thomas
RE: How to find the last input by product in a table which contains different products
Tamar
RE: How to find the last input by product in a table which contains different products
That's where Thomas has one point. That is undocumented, but known, so when you arrange data in chronological order, which it normally has, and look for the price at the max date, then you get the right record. The point is that this is a requirement important to know and to comply with, to get correct results. And then I'm not sure what exactly happens with the latest price per vendor and per other group defining values in random order in the table. So I'd rather not depend on order of data for the right query result.
But if I'd make the right order the main point then I'd use an index for that, and I would maybe go back to the other idea of mikrom:
I don't know, but I doubt using the way VFP7 allows wrong group by is faster than doing it that way with a scan, so I don't see fit for using the enginebehavior 70 to get something done unconventionally.
I'd recommend maintaining the current prices and having an archive of the price history by using triggers, anyway. Even if it has more effort in programming, it gives you what you need with neither SQL nor scans to produce that result, just triggers that maintain data in that form at all times. (Yes, and they use code whenever data changes, but then you have what you need with no more effort).
Chriss
RE: How to find the last input by product in a table which contains different products
I tested the SQL statements from mikrom and Chriss and was very happy with the result.
There were no problems with VFP 9.
I was also convinced by the information about better normalization of data.
I will definitely pay more attention to this in the future.
Thanks - I'm glad to know this forum.
Klaus
Peace worldwide - it starts here...
RE: How to find the last input by product in a table which contains different products
Hello Chris,
thank you for your comments and again something learned.
Thomas
RE: How to find the last input by product in a table which contains different products
There is a technique called "Control Break Processing" or in German known as "Gruppenwechsel" or "Normierte Programmierung". With it you can create more sophisticated reports with totals subtotals
In the past i posted some examples in several forums here on Tek-Tips:
https://www.tek-tips.com/viewthread.cfm?qid=167490...
https://www.tek-tips.com/viewthread.cfm?qid=180954...
https://www.tek-tips.com/viewthread.cfm?qid=165588...
https://www.tek-tips.com/viewthread.cfm?qid=165554...
RE: How to find the last input by product in a table which contains different products
sorted_table.txt
CODE
then this simple loop will do the job
sorted_table_report.py
CODE
Instead of pseudocode I used rather python and this is the output of the script
CODE