Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


multitable views in vfp 9

Posted on 2012-03-14
Medium Priority
Last Modified: 2012-03-17
Hello experts,
I need your help.
I created a multitable view. All seemed OK until I wanted to use the SKIP command with a VCR command buttons. When using the skip command the order in which the records are displayed is corresponding to index of the last table in join, not from the main table.
The view has the order to the main table.
This is the code:
Create SWL view Hotarari_judecatoresti_view as;
SELECT Hotarari_judecatoresti.id_hotarare,;
  Hotarari_judecatoresti.id_fel_hot, Hotarari_judecatoresti.id_rg,;
  Hotarari_judecatoresti.data_rg, Hotarari_judecatoresti.id_inst,;
  Lista_instante.den_inst, Hotarari_judecatoresti.nr_dosar,;
  Hotarari_judecatoresti.data_dosar, Hotarari_judecatoresti.id_obiect,;
  Lista_obiecte_actiuni.den_obiect, Hotarari_judecatoresti.id_stad_pr,;
  Lista_stadii_proces.den_stadiu, Hotarari_judecatoresti.term_judec,;
  Hotarari_judecatoresti.nr_anrp, Hotarari_judecatoresti.reclamant,;
  Hotarari_judecatoresti.id_parat, Lista_parati.den_parat,;
  Hotarari_judecatoresti.id_solutia, Hotarari_judecatoresti.descr_sol,;
  Hotarari_judecatoresti.id_dir, Hotarari_judecatoresti.id_cons,;
  Hotarari_judecatoresti.observatii, Lista_instante.id_inst,;
  Lista_obiecte_actiuni.id_obiect, Lista_parati.id_parat,;
    LEFT OUTER JOIN index_rg!lista_instante ;
   ON  Hotarari_judecatoresti.id_inst = Lista_instante.id_inst ;
    LEFT OUTER JOIN index_rg!lista_obiecte_actiuni ;
   ON  Hotarari_judecatoresti.id_obiect = Lista_obiecte_actiuni.id_obiect ;
    LEFT OUTER JOIN index_rg!lista_stadii_proces ;
   ON  Hotarari_judecatoresti.id_stad_pr = Lista_stadii_proces.id_stadiu ;
    LEFT OUTER JOIN index_rg!lista_parati ;
   ON  Hotarari_judecatoresti.id_parat = Lista_parati.id_parat;
 ORDER BY Hotarari_judecatoresti.id_hotarare

How can I control the order in which the records are displayed ?
Thank you
Question by:gaby12
  • 7
  • 2
  • 2

Author Comment

ID: 37720308
Correct mistake please:
SQL view
LVL 30

Expert Comment

by:Olaf Doschke
ID: 37721864
A view creates a view cursor without any index, you can add an ORDER BY to your sql to order or INDEX ON something everytime you open the view (A view has no permanent index).

Setting an index on any table is not influencing the order of an SQL-Select and so not the SKIPping, you SKIP in physical view cursor order.

Bye, Olaf.

Author Comment

ID: 37722989
The purpose of the multitable view is to display the names of different objects named in the main table only with the index of this object.
I don't understand why when the displayed record of the view is , say, 8 after clicking the 'Next' button the next record is 2 - that correspond to the value of the index from the last table in the join.
For better understanding which is my problem, I'll attach a print screen of the form and a print screen of the data example.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 37722998

Author Comment

ID: 37723001
As I said, I attached the PrtScr of the main table and the resulting view.
LVL 12

Expert Comment

ID: 37723292
As Olaf has said, forget about any Index settings on the source table(s).

They have NOTHING to do with how the records are displayed in the Cursor/Table which results from running your SQL Query.

Executing the SQL Query will result in a new totally separate record set from the original table records within its own Cursor/Table - they are a Copy of those record table fields & values.

displayed record of the view is , say, 8 after clicking the 'Next' button the next record is 2

When you check the Record Number are these the record numbers of the resultant Cursor/Table itself?
Or do they represent the record number you might expect if you are using the original table records as a reference?

If the later, then "no surprise", as I said the new record set is totally separate from the original records and the new record numbers will quite possibly NOT match those of the original tables.

Again, as Olaf has said, if you want to create a record sequence in the new record set, you can either do it by using the ORDER BY <whatever> clause within your SQL Query command itself.
Or, once the new Cursor/Table is created, you can create an Index on it.

It might be more clearly explained if you were to view the free tutorial videos at:
Perhaps these particular ones might be of value to you:
   Navigating Records in a Table
   Sorting Records
   Indexing Records
   Building a Simple Application - Pt. 1 through Pt. 6

If, however, the Record Number that you are viewing when you click on the "Next" button should be the record number of the new record set itself, then you might have some Active Index or Filter working on that new record set whereby the "Next" does indeed move the record pointer to the next sequential record - but due to the Index 'ordering' or Filter 'record hiding' it may not be the next numerical value.

Good Luck

Author Comment

ID: 37723751
I know that the new record set is totally separate from the original records and the new record numbers will quite possibly NOT match those of the original tables.
I have no filter (as you can see in the code from my first submitting).
But why there is a difference between the Record Number when you browse the new cursor (I made a little program to show the reccno())  and the displayed data. More than that, the test set of data has 14 records, but I cannot display sequentialy more than 6. After the 6-th record it returns to the second record. Reaching the last record is possible only with the 'Bottom' button, and it's realy the last record. When trying to show the records in (reverse) sequential order, the active cursor makes an uncontrolled jump to the record no.2. Believe me that, before asking your help, I tried different ways to understand what it is happening: changed data in the tables (and the records where the jump takes place changed - that is the reason why I suspected that the value of the ID of the object in the last table of the join has something to do with this behaviour), put and removed ' ORDER BY ... '
Please answer me a simple question: the order showed in the browse window of the view (as in the PrtScr I attached) is the real order of the records ? Why these jumps? Who controls the active record ?
Thank you for the documentation you reccomended me. I'll look at it.
LVL 12

Assisted Solution

jrbbldr earned 400 total points
ID: 37724801
But why there is a difference between the Record Number when you browse the new cursor and the displayed data.

Actually there CANNOT be a difference between the Record Number and the Record Pointer which is pointing to the specific record which is actually being displayed - they are one and the same.

I'd suggest going into the Click Method of your "Next" button and insert a SET STEP ON command as the first line of code prior to the execution of any other code.

Then, in VFP Development mode, when your SQL Query results are being viewed, click on the "Next" button.   That will force the code to suspend and force the TRACE & WATCH windows to open.    

I'd first put the following into the WATCH window:   ORDER()  to see if somehow an Index is Active.    If this shows no value, then no Index is active

Then I'd add:   FILTER()   to see if somehow a Filter is Active
Again, if this shows no value, then no Filter is active

Lastly I'd add:   RECNO()    to be able to actually see the current Record Number that the record pointer is on.

Finally single step through the code of the "Next" button Click method and watch it work paying attention to the RECNO() value in the WATCH window.

We have no idea of the code of your "Next" button Click Method.  Who knows - Perhaps it 'thinks' that next should be something other than the next sequential record.

Good Luck

Author Comment

ID: 37726001
Thank you for your answer. All I want is to understand what is happening !
I did as you said.
No filter, no order.
The sequence of records is OK, but the data displayed in the fields doesn't correspond to the record number, even each fild has a corresponding 'Control Source'.

The code in the Next button.Click :

IF thisform.List1.Visible= .T.
      thisform.List1.Visible= .F.
      thisform.Label13.Visible= .F.
      thisform.Label14.Visible= .F.
      thisform.Label3.Visible= .F.
      thisform.Label4.Visible= .F.

SELECT hotarari_judecatoresti_view
GOTO n_nr_inreg

IF n_nr_inreg < nr_total

IF n_nr_anrp>0
      DO progs\var_dosare_anrp
IF !EMPTY(ch_id_dir)

SELECT consilieri_view
LOCATE FOR id_cons=n_id_cons
      ch_mesaj="NU am gasit '"+TRANSFORM(n_id_cons)+"' in consilieri_view !!!"

SELECT hotarari_judecatoresti_view

LVL 30

Accepted Solution

Olaf Doschke earned 1600 total points
ID: 37730722
There is much more going on in your NEXT button, than I would have thought. You combine GOTO command going somewhere with SKIP, you're not just skipping one row. The outcome of this is quite random, I would say, which fits your experience.

As the view is a totally new rowset, the numbers you selected into it, are not it's record numbers any more. Records in a result set don't have the same recno(), this would not work in all cases, would it, record numbers just number the physical records.

It's unlcear what your code does, the GOTO is suspicius, not nkowing where n_nr_inreg comes from, what it is and from where it is, if your grid displays the main table or the view and much more unknonws, it's impossible to tell what's going on. Relations could be set causing movement of a record pointer in one alias movement of the record pionters in other aliases.

If you are at a certain record in the grid, I would expect the NEXT button to do no mopre than IF !EOF("somealias") SKIP 1 IN somalias ENDIF. I imagine the resot of the code positions accordingly in other aliases. This has to be rethought, I think.

In regard of RELATIONS, open the datasession window while debugging and see if you find relations between aliases visualised there.

In regard of the GOTO, see where n_nr_inreg really comes from and how it changes from NEXT to NEXT click.

Bye, Olaf.

Author Closing Comment

ID: 37733391
Hi Olaf,
Thank you making me think about where to find the mistake. Really I found it - I used two views on the same object - a combobox (one view as RecordSource and the other as ControlSource).  
I re-thought also the Data environment of the form, making a more complex multitable view and now all works OK.
Thank you again,

Thank also jrbbldr for your reccomendations.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question