how to Duplicate a records depending on a field. 1

[judyf60]

Hi,
I have a standard report designed from a single table. I have no grouping done to the report, It is a simple report with fields mostly in the detail sections (details have 7sections in them). I have a field (table.qty)which is a number and I need the record to be displayed say 5 times if the quantity is 5.
In short, when i look at te print preview, the first 5 pages should display the same record and then the next record should be displayed again depending on the qty. The special field Page NofM should read 1of5 ,1of10. which means the page number should be reset for every new record.I am using crystal(7.0). Could anyone please help me in this. Any and all suggestions are welcome.

Thanks
judy.

 

[IdoMillet]

Judy,

Create a simple table called "replicator" with a single numeric column called "how_many." Insert records with values of: 1, 2, 3, 4, 5, 6, etc.

Add a condition in your SQL
whereby {table.qty} <= {replicator.how_Many}
This would replicate each record as many times as the value in table.qty.

Group by a unique field to allow resetting of
page numbers.

Cheers,
- Ido
ixm7@psu.edu

 

[judyf60]

Ido,
How do I link the 2 tables (table,Replicator)? I don't see any common field. OR Do I just give a Sql statement

Select * from table, replicator
whereby {table.qty} <= {replicator.how_Many} .

Will it work? I am not good in SQL except familiar with few basic SQl statements.

Thanks
judy

 

[IdoMillet]

There will be no join between the 2 tables other than a WHERE condition:
---------------------------------------
Select * from table, replicator
WHERE table.qty <= replicator.how_Many
---------------------------------------

You can do this using a SQL statement in Crystal SQL Designer or simply add the replicator table in Crystal,
DON'T establish any join to this table, and add a record selection formula of

{table.qty} <= {replicator.how_Many}

Cheers,
- Ido



ixm7@psu.edu

 

[kenhamady]

For a description of how to use this technique within CR (without modifying the SQL) you can read an article in one of my previous newsletters:

http://www.kenhamady.com/news0111.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dgillz]

Ken-

This article appears to be what I am looking for but I cannot get the less than or equal to join to work. All the join options are grayed out and I cannot alter them. I am connecting to the data through ODBC.

When you say &quot;in SQL environments&quot; what exactly does that mean? I need to use Crystal SQL designer to design the report as oppossed to Crystal Reports?

Also, when you say &quot;this requires that you have the ability to add a simple table to your database&quot; - is this literally adding a new table to your database or can I just add another database to my report?

I had planned on doing this by creating a MS Access database with 1 tables, populated with 1 to 1000 in it and then linking that table to my Open Purchase Order line file in Macola, but thats NOT the same as adding a new table to a database.

To literally add a new data table to Macola I would need to modify the Macola ddfs and I do not like to do that for obvious reasons.

Any ideas would be appreciated. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[kenhamady]

It is also possible that Btrieve ODBC doesn't support all of the join options of most SQL based ODBC drivers. But, to use the special joins I am afraid that the table has to be in the same environment because when you cross-platforms in SQL, you are usually limited to Equal joins.

You do need a new table to store the numbers. Without that you can't force the inflation.

However, here is a workaround using MS Access table and an equal join. Just add more records as the numbers get higher, the number of records equal to the quantity:

1

2
2

3
3
3

4
4
4
4
etc.

That way, the quantity of 3 finds 3 matches and forces inflation to three records.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dgillz]

Ken-

Pervasive's ODBC drivers do support these types of joins -- I just tested it. It apparently becomes lost when you cross platforms as you suggested.

Your workaround works fine, however on Tuesday I will be testing this on a new btrieve file, populated from 1 to 1000, but I will be keeping this new btrieve file separate from my macola data, once again for obvious reasons. I will let you know what happens. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[kenhamady]

Don't forget to index the new file. With 1000 values you will have lots of records. About 500,000 I believe. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[judyf60]

I have created a simple table with numeric datatype and added the table to the report. I linked the Qty to newtable.field with a join type &quot;<= &quot;. Also in the record selection I used {table.qty}<={newtable.field}. Thou I am getting duplicate records. Duplication does not stop when
Qty = Field . Each record is getting duplicated 25 times thou the qty is 10(or different value).

When I donot give {table.qty}<={newtable.field}in the record selection I donot get any records. The report is grouped by table.ordernumber. Where I am going wrong?

Thanks
Judy

 

[kenhamady]

It sounds like the link fields aren't both the same data type. What database are you using? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[judyf60]

Thanks a lot all of you. I was able to solve the problem when I changed both the fields to the same datatype (one had int and other numeric ) .Also by changing my join type to &quot;Greater than or equal to&quot; the required results were acquired.
I am using Sql server(7.0).

Judy

 

[dgillz]

if anyone out there needs to automatically populate a table like this in MS Access, I posted the question in the MS Access Queries/SQL forum and had an answer in 10 minutes, or I can email you the code. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office