multitable views in vfp 9

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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gaby12Author Commented:
Correct mistake please:
SQL view
Olaf DoschkeSoftware DeveloperCommented:
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.
gaby12Author Commented:
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.
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

gaby12Author Commented:
gaby12Author Commented:
As I said, I attached the PrtScr of the main table and the resulting view.
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
gaby12Author Commented:
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.
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
gaby12Author Commented:
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

Olaf DoschkeSoftware DeveloperCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gaby12Author Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.