possible in crystal

[QuietRiot]

I have a table and there is a field called memo and it starts with Control Number: 3478884803.

the first 3 digits are the dealer number

so I changed the format to be Mid 18,3.

i also have a table called Dealers and it has dealerNumber and DealerName

I want to add dealer names into my report based off of the new Dealer number that I took from memo field. Is this possible.

 

[Kempes]

You could achieve this using a subreport.

go to insert then subquery. Follow the wizard and bring on the relevant data.
Then, make sure you choose the links tab, and link your formula field to your dealernumber.

This should work.

 

[boblarson]

You can just use the Selection Wizard to do it as well. Create a function that returns the value you want from the field and then set the selection equal to that function.

 

[QuietRiot]

You could achieve this using a subreport.

go to insert then subquery. Follow the wizard and bring on the relevant data.
Then, make sure you choose the links tab, and link your formula field to your dealernumber.

This should work.

Ok so I have a subreport that contains DealerNumber and DealerName

now on my main report I have a formula that is
if left({FundTransactionLines.Memo},7)="Control" then CDbl (Mid({FundTransactionLines.Memo},18,3))

and this is now the DealerNumber2.

How can I add Dealer Name on the main report based off of DealerNumber2 now? IM so close..

 

[QuietRiot]

I've tried a bunch of different ways and can't get this to work right at all.

So on my main report right now I have a formula that displays a dealer number after formatting.

my subreport would be the dealers table and I want to take the dealer name from that so all the dealer names show on my main report.

 

[boblarson]

Can you post your db? I could try building a sample with screenshots in Crystal 8.5 (my only version available) which SHOULD give you enough to take care of it.

 

[Kempes]

Another way, and maybe a more simple option, would be to make the change within your database query to strip the number out as a new field.
Then, update/verify the database in crystal so it shows up, and link this new field to your dealer table in database table linking.

This way, subreports would not be required.

What sort of database are you using out of interest?

 

[QuietRiot]

Another way, and maybe a more simple option, would be to make the change within your database query to strip the number out as a new field.
Then, update/verify the database in crystal so it shows up, and link this new field to your dealer table in database table linking.

This way, subreports would not be required.

What sort of database are you using out of interest?

Thanks guys,

is this option above doable in crystal. (im fairly new to crystal)

if its possible to do it in crytal then this sounds the best and I wouldn't have to deal with subreports.

Also I can't post my db since its financial. I also don't have permission to make any changes to the db so alls I have is crystal.

currently using sql server 2000

 

[boblarson]

Can you post a BLANK copy of your mdb file (in other words, just make a copy and delete all of the data).

 

[boblarson]

Thanks guys,

is this option above doable in crystal. (im fairly new to crystal)

if its possible to do it in crytal then this sounds the best and I wouldn't have to deal with subreports.

Also I can't post my db since its financial. I also don't have permission to make any changes to the db so alls I have is crystal.

currently using sql server 2000

Sorry, I guess you can't do a blank db since it isn't Access. I was hoping to help by showing the steps needed, with your exact structure. But, if I get time I'll try to throw together something.

 

[QuietRiot]

Thanks bob.

im also looking into the add command custom sql method right now but again im lost. If the field is called Memo and its text how do i write a select statement for a new field called DealerNumber and have it be Mid(18,3) and a number. Doesn't look like there is a MID function either.

 

[Kempes]

I think the issue may lie with your formula field in your main report.

a subreport is probably your only option. I found this on the crystal help facility which better explains what I was trying to suggest.

Linking to/from a formula field.
There are situations in which you may need to link to or from a formula (calculated) field. For example, an employee ID could be an 11 character value that consists of a two-character department code followed by the employee's nine-character Social Security Number (for example, HR555347487).

The formula language makes it easy to extract the Social Security Number from this field:

{employee.EMPLOYEE ID} [-9 to -1]

- or -

{employee.EMPLOYEE ID} [3 to 12]

For the value HR555347487, either formula would return the value 555347487.

While the return value is a valid Social Security Number, the fact that it comes from a formula prevents you from using the field to link to a Social Security Number field in another table. You can report on and coordinate the values in the two tables, however, by using a subreport.

To link to/from a formula field
Create the primary report using a table that includes the Social Security Number field.
Create (or import) a subreport using the formula that extracts the Social Security Number from the Employee ID field (for this example, {@EXTRACT}).
See Inserting subreports.

Place the subreport where you want it to appear in the primary report.
Link the subreport to the primary report by linking the Social Security Number field in the primary report ({file.SSN}) to the formula that extracts the number in the subreport ({@EXTRACT}). See Linking a subreport to the main report without modifying the selection formula.