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

Convert Number to String then link to SubReport - ODBC

Convert Number to String then link to SubReport - ODBC

(OP)
Problem: I need to convert character types and then link a SubReport via the new character type.

Example: Job_Operation.Job_Operation (number) and Attachment.Owner_ID (string)

I have attempted to use the SQL Expression as follows: {fn CONVERT('job_operation.job.operation',SQL_varchar)}. Then I am using the SQL Expression as a parameter to link with the Attachment.Owner_ID (string) field.

So far, no luck. The SubReport returns no data.

Any thoughts?

Thanks,

BMB

RE: Convert Number to String then link to SubReport - ODBC

Have in a formula attempted a cstr({Job_Operation.Job_Operation}) then assign that to a shared variable to use in the selection editor in the sub-report. It is just thought, I do not know if it will even let you do this.

RE: Convert Number to String then link to SubReport - ODBC

There is no reason why your approach shouldn't work, as long as the result of the SQL Expression is in the same format as the sub-report source data (SQL Expression syntax is database dependent) and it is being passed to the sub-report correctly.

I note your SQL expression is: {fn CONVERT('job_operation.job.operation',SQL_varchar)}.
Shouldn't it be {fn CONVERT('job_operation.job_operation',SQL_varchar)})

A simple CR formula should also work in place of the SQL Expression.

CODE

ToText({job_operation.job_operation}, '#') 

If it still isn't working, check the sub-report link and the record selection formula in the sub-report.


Cheers
Pete

RE: Convert Number to String then link to SubReport - ODBC

(OP)
Pete:

The CR formula worked perfectly, thanks!

One last question. Now that the attachment paths are showing in the SubReport, I need the filepath to be an executable link.

Example: Q:\PRINTS\CUSTOMER_NAME\00108-01 REV C\00108-01 REV C.pdf

Do you happen to know the CR formula I can use for that?

Thanks again!

BMB

RE: Convert Number to String then link to SubReport - ODBC

You can make a text box, or a field into a hyperlink. Right click on the field, choose Format Field. Click on the Hyperlink tab. Choose A File. Then click on the Formula Expert button and put the path field in the formula.

RE: Convert Number to String then link to SubReport - ODBC

(OP)
Kray4660:

I need this to be done automatically. We have thousands of links. So I need a formula to make the field to be an executable link to a file.

I see where this can be done. I just don't know how to write the formula.

Any thoughts on how to do this?

RE: Convert Number to String then link to SubReport - ODBC

If there is logic behind how the path and filename is determined it will be possible to create it dynamically.

The basic approach is to use a formula to construct a sting that contains the full path and filename, and use that formula as the hyperlink.

If you need further assistance, please explain how the path/filename is determines and someone should be able to assist.

Cheers
Pete

RE: Convert Number to String then link to SubReport - ODBC

(OP)
Pete:

All filepaths will be as follows:

Q:\PRINTS\'customername'\'customerpart' rev 'n'\'customerpart' rev 'n'.pdf

All items in 'field value' are attributes from a table.

Example: Q:\PRINTS\LEXICAR\00849-01 REV A\00849-01 Rev A.pdf

Would you be able to assist with a formula?

Thanks,

BMB

RE: Convert Number to String then link to SubReport - ODBC

Assuming all of the path/filename components are database fields, the formula will look something like this:

CODE

'Q:\PRINTS\' + {Table.customername} + '\' + {Table.customerpart}' + ' Rev ' + {Table.Rev} + '\' + {Table.customerpart} + ' Rev ' + {Table.Rev} + '.pdf' 

The formula simply concatenates the static string components with the dynamic database values.

Hope this helps.

Regards
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