INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Pass values from different details sections to a field

Pass values from different details sections to a field

(OP)
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.

RE: Pass values from different details sections to a field

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

RE: Pass values from different details sections to a field

(OP)
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.

RE: Pass values from different details sections to a field

OK, I have a few more questions:

  • 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?
  • Do you just want to show a single record for each invoice or is there multiple items you want to show for each invoice?
  • Will the invoice no always appear in the same spot, eg can the number of characters prior to the invoice number change?
  • Is the invoice number always the 21st line, of can that vary?
Depending on the answers to these questions, I think that variables can be used to extract the data you want.

Cheers
Pete

RE: Pass values from different details sections to a field

(OP)
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.

RE: Pass values from different details sections to a field

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

CODE

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

Hope this helps


Cheers
Pete

RE: Pass values from different details sections to a field

(OP)
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

RE: Pass values from different details sections to a field

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:
  • LineType;
  • BoldType;
  • PrintLine;
  • CompanyNo.
In your report, what does the field explorer show as the database name, and what fields are listed?

RE: Pass values from different details sections to a field

(OP)
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.

RE: Pass values from different details sections to a field

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

RE: Pass values from different details sections to a field

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 

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

CODE

WhilePrintingRecords;
Global StringVar INV; 


Hope this helps

Cheers
Pete

RE: Pass values from different details sections to a field

(OP)
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...

RE: Pass values from different details sections to a field

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close