Convert Number to String then link to SubReport - ODBC 1

[Benjamin Becker]

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

 

[kray4660]

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.

 

[pmax9999]

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.

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

 

[Benjamin Becker]

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

 

[kray4660]

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.

 

[Benjamin Becker]

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?

 

[pmax9999]

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

 

[Benjamin Becker]

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

 

[pmax9999]

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

'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