How to create a sort value on invoice records, then re-sort report on value?

[Allan Ogilvie]

I have multiple records on invoices. I need to extract a value from the first record on each invoice, then add it to the other records on that invoice, then re-sort the report on that value.

How do I do this?

My thought is to create a shared variable for the extracted value from the first record and add it to the other records on that invoice, then run a second pass that sorts on that value. However, I am not sure how to do this.

 

[lbass]

You won't be able to sort on a variable. Can you explain why the value you want to add to the others appears only in the first record? Maybe use an example. The issue is that once it is sorted again, that record will likely not still be in the first position. Why is it there now?

-LB

 

[Allan Ogilvie]

First record on the invoice is a driver item. The other have no code that I can use to identify that they are related to this driver. I have many invoices with this driver, as well as other drivers. I need to extract report for each driver and their other related items that were invoiced.

I cannot find a way to do this with Crystal Reports reading the posted data, so I exported transactions to Excel and used formulas to create the driver code for all invoice lines, then used Crystal Reports to read the Excel file. That worked great, until I had to re-create my Excel file from another export. Now I cannot read my Excel file!

 

[lbass]

But what is the database you are using before export? How does the database ‘know’ that the records go with that driver? I’m wondering whether there are other tables that need to be linked to the invoice table that would allow you to then group on the driver or place the driver field in the details section.

-LB

 

[kenhamady]

One way to do this is with a self referencing join. Take the invoice details table and add it a second time. Link this to the original table using the invoice number. Then in the select expert add a rule that records from this new instance have to be one of your driver records. What should happen is that every record in the original invoice now links to the record of the driver, so it now has two sets of info - the original info and the driver info. You can sort and filter these records based on this new value.

If the table you added twice was linked to the item master you might need to add a second instance of that table as well to decode the Item ID into the actual driver.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/