Pass values from different details sections to a field

[djieon]

Hi All, thanks for looking at this thread.

I have a report that uses a csv file as its database, unfortunately the csv is already formatted which is rubbish, but I cant get that changed.

With that in mind is there a way that I can create a formula/field to show me the value for something along the lines of

details record number = 6 and mid (job number,54,6)

Thanks in advance

David.

 

[pmax9999]

Can you explain this a bit more please. Some sample data and an example of what you want and where you want it would also help.

Cheers
Pete

 

[djieon]

Hi Pete, here is one invoice from my csv file

lineType,boldType,printLine,companyNo
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDON"," I N V O I C E","01"
"SMALL","BOLDOFF","","01"
"SMALL","BOLDOFF","","01"
"SMALL","BOLDON"," VAT Regn. No. 000000000","01"
"SMALL","BOLDON"," Invoice Address Job Location","01"
"LARGE","BOLDOFF"," Jack's Test Company AS INVOICE ADDRESS","01"
"LARGE","BOLDOFF"," Market House","01"
"LARGE","BOLDOFF"," Lenten Street","01"
"LARGE","BOLDOFF"," Alton","01"
"LARGE","BOLDOFF"," Hants, GU34 1HG","01"
"LARGE","BOLDOFF"," -------------------------------------------------------------------------------","01"
"SMALL","BOLDON"," Account No Job Number Engineer Your Order No Invoice No Invoice Date Page","03"
"LARGE","BOLDOFF","","03"
"LARGE","BOLDOFF"," JTW001 000001/001 JK 100005 03/02/00 1","01"
"LARGE","BOLDOFF"," -------------------------------------------------------------------------------","01"
"LARGE","BOLDON"," Amount","01"
"LARGE","BOLDOFF"," Test T/sheet Posting 100.00","01"
"LARGE","BOLDOFF"," TESTING FEED THROUGH 10000.00","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF"," NETT TOTAL £ 10100.00","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF"," V.A.T. £ 1767.50","01"
"LARGE","BOLDOFF","","01"
"SMALL","BOLDOFF","---------------------------------------------------------------------------","01"
"SMALL","BOLDON"," Code Vat Description Goods Rate Vat Due","01"
"LARGE","BOLDOFF"," 1 STD 10100.00 17.50 1767.50","03"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDON"," TOTAL DUE","01"
"LARGE","BOLDOFF","","01"
"LARGE","BOLDON"," £ 11867.50","01"


Each line of the above appears as a separate record in crystal, so for instance if I want to be able to get the 'order number' so I need a formula that looks for the following:-

**record number = 21 (line 21) and mid({3rd field},55,6)** and that would return the value "100005" which I can then use in the report to format etc as I want...

Does that make sense?

thanks

David.

 

[pmax9999]

OK, I have a few more questions:

[ul]
[li]Does each CSV file contain 1 invoice or multiple invoices? Assuming there is more than 1, what is is in the data that indicates that these 57 line belong to the one invoice?[/li]
[li]Do you just want to show a single record for each invoice or is there multiple items you want to show for each invoice?[/li]
[li]Will the invoice no always appear in the same spot, eg can the number of characters prior to the invoice number change?[/li]
[li]Is the invoice number always the 21st line, of can that vary?[/li]
[/ul]

Depending on the answers to these questions, I think that variables can be used to extract the data you want.

Cheers
Pete

 

[djieon]

Hi Pete,

1. No the csv is always 1 invoice only.
2. its creating a details record for each line of that csv file, so there will always be 57 details records to one invoice.
3. I am led to believe that that it will always be in the same place, so will always be the same number of characters into the line.
4. no the invoice will always be the 21st line.

Thanks

David.

 

[pmax9999]

I achieved it with this formula (where the CSV file name = Data.csv:

If      RecordNumber = 21
Then    MID({Data_csv.printLine},23,6)

Hope this helps


Cheers
Pete

 

[djieon]

Hi Pete, sadly its not working for me.

I am using the following:-

if RecordNumber = 21

then mid({PORTRAITINvixtest_csv.printLine},23,6)

I am getting 'this field name is not know'

Does it matter we are changing the .csv for a _csv

 

[pmax9999]

Not sure why. I created a CSV file using your data and saved it as Data.csv. I created a connevction to that file using Access/Excel (DA0), which results in a dat source called Data_csv, containing the following as columns:
[ul]
[li]LineType;[/li]
[li]BoldType;[/li]
[li]PrintLine;[/li]
[li]CompanyNo.[/li]
[/ul]

In your report, what does the field explorer show as the database name, and what fields are listed?

 

[djieon]

So the database is called 'command'

but when I try using {command.printLine} as the field name it goes through but on the preview the field is blank, is that because of the time it is processing the formula at? Even adding in WhileReadingRecords; its still showing with no values inside the formula...

But the database is called command, and consists of the four fields you listed above...

cheers

David.

 

[pmax9999]

Is there an "Order By" in the command?

If you just add the 4 columns onto the report and run it, is the Invoice No line still line 21?

Can you post the code frome the Command? Alternatively, if you can save the report with data and post the report file I'm happy to take a look at it to see what is happening.



Cheers
Pete

 

[djieon]

Hi Pete, have attached the file...

I have tried to create the formula invoice number but as you will see im not getting a result from the formula.

Cheers

David.

 

[pmax9999]

OK. The issue here is that the formula you are using to display the Invoice Number is in the Report Footer, so a variable is required.

Create the following Formula, place it in one of the details sections and suppress it:

[Code {@InvNo}]
WhilePrintingRecords;
Global StringVar INV;

If RecordNumber = 21
Then INV := MID({Command.printLine},55,6)
Else INV := INV
[/Code]

Then, amend your existing formula {@Invoice Number} as follows:

WhilePrintingRecords;
Global StringVar INV;

Hope this helps

Cheers
Pete

 

[djieon]

Cheers Pete, now getting some values coming through...however is there a way I can make it work if I put the values in the page/report header?

Thanks in advance...

 

[pmax9999]

The only way to get the results in the Report Header is with a Sub-Report.

Create a Sub-Report with just the {Command.printLine} and the new formula I provided in the Details section, and the amended display formula in the Sub-Report footer. Suppress all sections except the Sub-Report footer.

Place the Sub-Report in the Report Header of the main report.

Hope this makes sense.

Cheers
Pete