Hi All,

I was wondering if there is a way to change many records to fewer records. The first table shows 21 records of a Ford F-150 ranging in years from 2015 to 2017 with two different engine sizes. Would it be possible to create a procedure that could translate the many and create fewer records in another table as shown in the second table? The first table also has redundancy that would be eliminated with the new records.

NOTE: This post may be similar to my previous post “Listing lowest and highest values only” but would create new records in a separate table instead of just listing them.

Thanks again for all your help.

Looks like both images didn't upload. Here is a pic of both tables. Many records on top and fewer records on bottom. 

Easy peasy ;-)

You simply need to create a copy of  your table and then write a DQL along the lines.
define "vMake" text .
define "vModel" text .
define "vYear" text .
define "vEngineLiters" . (as many of these as you need to distinguish a unique record).
For OldTable  ;
list records
Make in order ;
Model in order ;
year in order ;
EngineLiters in order . -- same as above. You need to order your records so "equal" records come in order.
if Make != vMake or Model != vModel or year !=vYear or EngineLiters != vEngineLiters then
enter a record in New Data
copy all from OldTable .
vMake := Make .
vModel := Model .
vYear := Year .
vEngineLiters := EngineLiters .
end


This is basically the framework you need. It is written from the top of my head so might be bugs etc but you should get the idea.

You "comb" all the data in the right order so the "surplus records is grouped and then you simply copy the first record in each "cluster" into a new table and ignore the ones that are clones. 

Good luck

Thank you for your help. I really appreciate it.

The following script seems to function but only displays the data. It does not enter any new records in NewTable 

define "vMake" text .
define "vModel" text .
define "vSubModel" text .
define "vYear" text .
define "vEngineLiters" text .
For OldTable ;
list records
Make in order ;
Model in order ;
SubModel in order ;
year in order ;
EngineLiters in order .
if Make = vMake or Model = vModel or SubModel = vSubModel or Year = vYear or EngineLiters = vEngineLiters then
enter a record in NewTable
copy all from OldTable .
vMake := Make .
vModel := Model .
vSubModel := SubModel .
vYear := Year .
vEngineLiters := EngineLiters .
end

ALSO: I had to remove the ! from "if Make != vMake or Model..."

We could also simplify things. If I wanted to create just one record of all makes in the old table would the script look like this?

define "vMake" text .
For OldTable ;
list records
Make in order .
if Make = vMake then
enter a record in NewTable
copy all from OldTable .
vMake := Make .
end

When I run this script is only displays the data and does not create any new records in the New Table.

Try this

define "vMake" text .
For OldTable ;
list records
Make in order .
if Make not = vMake then
enter a record in NewTable
copy all from OldTable .

end .
vMake := Make .
end

"if Make != vMake or Model != vModel or year !=vYear or EngineLiters != vEngineLiters then"

Thanks Peter and sorry Bolt-On-Trailer...

You can tell that HTML and JavaScript is a big thing in the upcoming DE9/DataEase 4 Web/LegEasy 4 Web....

!= is not = in JavaScript so when you write from the top of you head it can quickly go wrong.

Thanks for pointing it out Peter.

The difference between Peters and ours (other than his compiling...;-) is that ours take into consideration more than one column for uniqueness.

This is why its important to sort all the necessary columns in order and allocate them to temporary variables so you can spot when there is a change and save that record while skipping all the ones that fall in the same category.

If might be a good idea to start with Peters version and then add more "filters" as you test. That way you see the result as you go along.

you can add

delete records in NewTable . 

At the beginning then you start fresh for each run through of your DQL.

Testing (as you see in our example) is essential for a correct result ;-)

Thank you DataEase and Thank you Peter for your help.

The following does work and creates one record of each make in the new table. It still however displays the data on screen and I have to scroll through to the end in order for all the records to be created. Any way to have it simply create the records and not display the data on screen? Maybe print to file?

define "vMake" text .
For OldTable ;
list records
Make in order .
if Make not = vMake then
enter a record in NewTable
copy all from OldTable .

end .
vMake := Make .
end

To simply not display it on the screen, remove the following two lines:-

list records
Make in order .

Oops!

"To simply not display it on the screen, remove the following two lines:-

list records

Make in order ."

Be careful not to pour the baby out with the bathwater now. If you do this you won't get Make in order and hence the if statement won't be correct.

The initial approach is the "nice" and elegant approach where one use logic and niftyness to achieve ones goal.

It is the combination of ordering things so similar records come after one another and then skipping all but the first occurrence that make it work. If one don't order it, it will not work.

But there is a "nasty" and "brutal" way of doing this too.

Just create the new field with all the fields that make the record unique as exactly that - Unique. (Uniqueness in DataEase is not on each field being unique but the combination of all fields in the table being unique together).

Then simply run a DQL like this:

for Table1 ;
enter a record in table2 
copy all from table1.

this will result in a big error log of unique constraint breaches but it will be enforced and you will achieve the goal of sorting out the table.

"We" - programmers - have a problem with things that give error message as it doesn't seem to be "proper" programming but in this case it might be more "cost" effective.

For the more interested of our readers we can add the following:

The problem Mr. Trailer has is the "singular" reason for us looking into ExecDQL.

The DQL in DFW is flawed in many ways and one main one is that you NEED list records to sort the records but you can't have a DQL with List Records without generating an output.

This has been debated to "death" over the 25 years since DFW was released but I guess one last time listing the ways to work around this problem can't hurt.

In 8.5 the best way is simply to use ExecDQL but some traditional users find it hard to get their head around it so if you are using traditional document DQL the ways are :

1. Include an export to in your DQL.

Example:
for SomeData ;

list records

Name in order ;

ID .

end

export to "NUL" .

.items

.end

By some stroke of "brilliance" when they added the export option (in panic) back in the early 90s they killed the possibility of having both printing/output and export in the same DQL. 

In good DataEase tradition a bug/shortcoming quickly became a feature and this is the "best" way of achieving no output from a trad. DQL.

However the Export feature is so badly made that for bigger and more complex DQL (lots of data) it might slow to a halt or simply GPF however it works great in cases of limited output (less than 10.000 records).

2. Setting up a NULPRINTER.
This is a little more work and it need to be done on all the workstations that use an app so only good if you are using this feature a lot (i.e. DQL that need both sorting and no output.)

First step is to set up a NUL printer on your computer. This is a printer that will send all output to the NUL device i.e. to the big black hole in space.

Open the Windows Add Printer Wizard.

Select to define a local printer. Click Next.

Create a new port selecting Local port.

Enter NUL for the Port name. Click OK.
Then set up a printer (driver doesn't really matter we normal use Generic/Text only) and select the NUL: printer port.


When you have set up the null printer the best way of using it in your application is to set it up as a Application Printer so you can "bake" it in to your application.


And then lastly you use this in Printing Options on your DQL.



This is the recommended method for large and heavy processes that you will use as an integral part of your application but again using ExecDQL will be 100 times more effective at least.

Oooops indeed!

That just shows the dangers of typing something off the top of one's head as you are "walking out of the door"!

If I remember correctly, old age and senility notwithstanding, if Make was indexed there was a way of specifying the order by using the statement :-

for OldTable with Make > "*" ;

Just tested and that most likely worked in DFD but not in DFW.... 
One of the many small "insignificant" differences that made migration from DFD to DFW a nightmare....