How to manipulate a View

[k2a]

I created a view from 2 tables and used this view to build report. Within my app the original view data are manipulated and the results are placed into a target view with the very same structure as the original view.

Is it possible to use my report to display the data from my target view?

Of course, I could make it easy and create a new table instead using that target view. However I’d to implement it without a new table.
Running now my report it will of course show my original view data, the report was built from. But is there a way to replace the content of the original view with content of my target view?

Thanks for any help.
Klaus

 

[Mike Lewis]

There are two ways you could go about this.

One option would be to build the target view at design time, that is, while you are creating the report. Then, when building the report, you can specify the full field names for each expression. By "full field name" I mean[tt] alias.fieldname[/tt]. For example, if the view has the alias [tt]MyView[/tt], a field might be called something like[tt] MyView.CustomerName[/tt]. You can either type that name straight into the field properties, or select if from the expression builder.

If it is not convenient to create the view at design time, you can still do as above (enter each field name in the form [tt]alias.fieldname[/tt]), but you won't have the benefit of the expression builder, and you won't be able to use the Verify button to check the validity of the name.

The other option is simply to enter each field name without an alias. So just [tt]CustomerName[/tt] rather than [tt]MyView.CustomerName[/tt]. Then, when you are ready to run the report, make sur the view exists and is selected in the current work area.

The main point is that a view behaves just like a table. So driving a report with a view is basically no different from the way you would normally do it.

EDIT: I just re-read your original post and realised that you have already created the report based on the old view. In that case, it's even easier. When running the report, just open the new view and make its alias the same as the name of the orignal view. Like this: [tt]USE NewView ALIAS OldView.[/tt] Then everything should work as required.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[k2a]

Hi Mike
I tried the first option you mentioned those with the alias. Actually that modification is accepted but it display just one record from my target view and that record is repeated over a full page. Looking the target view itself there are many records in it. This does not happen when original view is send to that reports.

However, when I try USE <target view name> ALIAS < original view name> then an error occurs <target view name>.dbf does not exist.

Klaus

 

[Mike Lewis]

when I try USE <target view name> ALIAS < original view name> then an error occurs <target view name>.dbf does not exist.

Are you sure the database (that is, the DBC that holds the view) was open in the current work area when you tried to open the view? If it was not, that would explain the error.

I don't know what is causing the first problem (the same record being repeated throughout the report). What do you see when you browse the view?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[k2a]

My original view is within the database and I’m running the program script out of a form.
The created target view contains the records I want to see in my report.
Klaus

 

[Mike Lewis]

But that doesn't answer my question. Is the database open when you try to use the view?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[k2a]

Mike help me!
I supposed when running a view which is sitting within a database then the database will open.
If that is wrong, please tell me how to open the database or how to check and see that it is open.
Klaus

 

[Olaf Doschke]

I don't know why you're talking about original view and target view.

If you have a view2 based on a view1, but in the query of view2 specify an alias name not known as table or view name in the DBC, your view2 depends on a workarea having that alias name.

the situation should be like this:

a) database with tables, let's say table1, table2
b) view1 making a query on those tables
c) view2 based on view1, perhaps including some further tables, doesn't matter.

view2 can be based on view1, as long as the view definitions name is view1. If you aliased view1 as viewX during definition of view2, then view2's query literally works on viewX, doesn't find that alias as name of a table or view in the DBC and fails to run.

In that case view2 will depend on you opening view1 ALIAS viewX, before USE view2 would work.

Also, since view1 is the name of a database view, you should never USE view2 ALIAS view1. If you think that means to USE view2 based on view1, you think in the wrong direction. ALIAS is giving whatever you use that alias name.

Before we all get confused, can you simply post names of the dbc tables and views involved and view definitions? Then code you do to use the views before report form? Or does the report have a private datasession, in which you added the views?

That needs correct order of views, too and could be problematic. For example initialselectedalias will not necessarily be the first opened table or view, the DE will just make sure that is the selected alias at the end of the initialisation of the DE.

Bye, Olaf.

 

[k2a]

As I said at the beginning of my post that the original view (view1) was used to build the report. Just removed view1 from the report’s Data Environment. Select the target view (view2) which has the very same structure as view1.

Now that finally gives me the correct output now.

Thank you all for the help.
Klaus