copy records marked "unpaid" 1

[mikeisvfp]

Hello Experts

I would like to copy only the records marked "UNPAID" to another table, So far i am able to search for any records marked "UNPAID"

by smiply doing this

SELECT billing

SCAN FOR status = 'unpaid'
<do something>

EXIT
ENDSCAN

Please help

 

[Dan Freeman]

If you truly mean COPY, then just do this:

Insert into (othertable) ;
  Select * from billing  Where status = "unpaid"

 

[Olaf Doschke]

Why copy to another table?

I'd just use the select part of Dan's Insert sql and select into cursor, then do whatever you need to do with the cursor result.

Copying to another table introduces the simple risc of the records being marked paid there, later, without also marking them paid in the main billing table. Keep an info at one place, and you prevent the risc of contradictionary information. That principle is a very basic rule of database normalisation.

Every data you need to process should come from that single instance of persistant storage into temporary storage. Of course you can delete the table after having processed the copied records, but a cursor does that for you automatically.

Bye, Olaf.

 

[Dan Freeman]

Why copy to another table?

Perhaps that's what he needs.

To be honest, if it's going to "another table" I'd be inclined to think it should be a move instead of a copy and that has other implications (which is why I remarked as I did), but he didn't ask that.

 

[Olaf Doschke]

I also don't see why you would move (instead of copy) records to another table, as the records only differ in status. If you only have statusses "paid" and "unpaid" you could then eleminate that status, but it would nevertheless not make much sense in database design to keep technical similar data in two different tables, just because of their meaning. It makes querying data more complex in general.

You know I often don't just give an answer to the question asked but question the wanted solution.

Mike knows best what he needs and I don't ask this as a rethorical question. If mike has a good answer why he needs a copy of the data I second your answer or point out COPY TO FOR status = 'paid' as another solution, which also covers creating the new dbf.

I just food for thought on redundancy of data.

Bye, Olaf.

 

[mikeisvfp]

I was thinking of a reminder list, but your right, why create another table when i can create some sort of reminder list using the same name's and aliases, lets say a Grid and filter on
status = "unpaid". Would you say that works better?

 

[Olaf Doschke]

Yes, a filter on status="unpaid" could be fine, or a view or the query part of dan's answer: Select * from billing Where status = "unpaid" into cursor curGrid, setting grid.recordsource to "curGrid". You can also INDEX ON customer Tag xunpaid FOR status="unpaid", then SET ORDER TO xunpaid will also show unpaid records in order of customer names. Many solutions to the same goal. The essence is a table is for persisting data, no more, no less.

The term "view" does reflect the need of different point of views on the same data and in this sense is the number one choice for being able to get the data needed for a certain aspect. If views weren't technically limited in some senses, I'd perhaps use them more. As long as you don't need updates on the data you want to show on forms, a query is the most dynamic thing you can do to get data in the form best for the current purpose of your GUI or workflow. The query result of a cursor can best be shown in all the different foxpro controls, so it's my number one choice instead of views.

The database should not be concerned about what would make such queries most easy, but the database is all about data persistence and integrity only.

If you go about designing your tables for the several purposes and copy data all over the place, what get's more complicated is keeping all data consistent and in sync. And if this get's more an more complicated this get's like herding cats, or frogs. Whatever uncomforts you more .

Of course in the end you also need data entry forms and editing of existing data, eg setting an unpaid bill to paid in the best case. While this can't be done through nonupdatable query result cursors, you can always have secondary usage of the table(s) to make the changes in them. As the display cursor would have the ids of the table included, you can always locate the corresponding record(s) and make that change. While this seems overhead the solution is quite simple to achieve and reuse in any situation/form.

Bye, Olaf.

 

[mikeisvfp]

Frogs by the way, thank you Olaf for the detailed piece of information. Very well explained.

Also, as many say, Select is much better than filter. So yes I will go with Select.

Thanks

Mike