Avatar of gaby12
gaby12Flag for Romania asked on

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,;
  Lista_stadii_proces.id_stadiu;
 FROM ;
     INDEX_RG!HOTARARI_JUDECATORESTI ;
    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
FoxProMicrosoft Applications

Avatar of undefined
Last Comment
gaby12

8/22/2022 - Mon
ASKER
gaby12

Correct mistake please:
SQL view
Olaf Doschke

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.
ASKER
gaby12

Hi,
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.
hotarari-judecatoresti.png
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
gaby12

ASKER
gaby12

As I said, I attached the PrtScr of the main table and the resulting view.
jrbbldr

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:
   http://www.garfieldhudson.com/FreeVideos.aspx
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
gaby12

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.
SOLUTION
jrbbldr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
gaby12

Hello,
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.
ENDIF

SELECT hotarari_judecatoresti_view
GOTO n_nr_inreg

IF n_nr_inreg < nr_total
      SKIP
ENDIF
n_nr_inreg=RECNO()
ch_id_dir=id_dir
n_id_cons=id_cons
n_nr_anrp=nr_anrp

IF n_nr_anrp>0
      DO progs\var_dosare_anrp
ELSE
      ch_car_anrp=""
      d_data_anrp=CTOD("")
ENDIF
thisform.CAR_ANRP1.Value=ch_car_anrp
thisform.DATA_ANRP1.Value=d_data_anrp
IF !EMPTY(ch_id_dir)
      ch_den_dir=directie(VAL(ch_id_dir),2)
      thisform.Combo2.Value=ch_den_dir
ELSE
      thisform.Combo2.Value=""
ENDIF

SELECT consilieri_view
LOCATE FOR id_cons=n_id_cons
IF FOUND()
      ch_id_user=id_user_a
      ch_den_consilier=ALLTRIM(nume_pre)
      thisform.Combo6.Value=ch_den_consilier
ELSE
      ch_mesaj="NU am gasit '"+TRANSFORM(n_id_cons)+"' in consilieri_view !!!"
      MESSAGEBOX(ch_mesaj,64)
ENDIF

SELECT hotarari_judecatoresti_view
GOTO N_NR_INREG

thisform.Refresh
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
gaby12

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,
Gabriela

Thank also jrbbldr for your reccomendations.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23