Using Commandtext Instead of Tables

[Former Member]

Hi.  My report used to use a single table.  The requirements for it have changed and I'm going to need to add a datacursor inside a commandtext instead.

For the most part, I see that the report prints from fields within the original table. For example, the first field is {pohistory.ponumber}.  By changing over {pohistory} to a {commandtext}, will I have to rewrite the report?  I already have everything formatted the way I wanted for printing.  I do not want to remove the old fields and drag the new ones in, for starters because it was a pain to get them to print how/where I wanted in the first place.

In the field Explorer, there is a folder named "Database Fields".  In there it references my original tables and if you expand the field list, you see the fields being used with a checkmark.  Now, if I replace the table with a command, is there a way to change the fields in the report to come from the new command rather than the table?  I have a sinking feeling I'll have to re-design the report from scratch but I'd really rather not have to do that.

Thanks!

Debbie Erickson

[abhilash_kumar · Accepted solution]

Hi Debbie,

Use the 'Set Datasource Location' (Database > Set Datasource Location) feature to instantly switch between datasources.

If Crystal Reports cannot match the fields automatically, you might have to do it manually but since you already know fields from both the tables, it should be an easy walk for you.

You do not need to re-design the report, pheww!

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash.  Thanks for answering.  I did try this and ran into another problem.  Maybe you can help with that, too?

My original report was based on 3 tables.  I had some group breaks based on certain fields in those tables.  I changed the datasource as you said, then deleted the 3 tables.  I got lots of errors on formulas and group breaks.  I knew how to drag-and-drop the command fields into my formulas to replace the table-based fields.  But I don't know how to change the group breaks.  I sent into the section expert and there's a lot of ??? where the tablename.fieldname was.  How do I modify the section groups so that breaking occurs on the new command table?

Thanks!

[abhilash_kumar · Accepted solution]

Hi Debbie,

When you do a set data source location, it is supposed to replace all the tables and fields from the old table with the new table.

I did not get this part where you say - "I changed the datasource as you said, then deleted the 3 tables". Why was this needed?

When you did the 'set datasource location', did you get an option to 'map' unmatched fields? Remember I told you that CR will try to map most of the fields from both data sources. Anything that does not map needs to be mapped manually.

My guess is, there were some fields (and perhaps fields that were used in formulas for grouping) that went unmapped after you changed the datasource.

Could you revisit the set datasource location option and make sure each table is mapped with a corresponding table from the command object and also make sure each field from the tables has a match in the corresponding table?

So, instead of clicking on the connection name and then clicking on the 'Update' button, select one table after another and update.

-Abhilash

[Former Member · Accepted solution]

Hi Again.  The automatic field mapping never happened.  It might be the version of Crystal I'm using.  I have an old Crystal 11 because I use the VCL in Delphi.  I had to manually change each area (formulas, group breaks, detail field line).

As for the removal of tables, I had 3 individual tables listed in the Database Expert in the right panel.  I replaced them with the command object.  My command object had all 3 tables, and more.  Since the original ones were no longer supposed to be referenced, I clicked the left arrow(delete) to remove them from the graphical representation in the Database Expert.  Picture below is what I was trying to explain. 

Anyway, none of my fields mapped over to the command object even though all the tables were in.  I'll have to keep playing with it.  Luckily I have a backup to start over.  The most disturbing thing was losing my chart object.  Sigh. At least I don't have to redo the entire report.  Next time, I'll add the command object, manually change the mappings to it, THEN remove the other table objects.