Solved

multitable views in vfp 9

Posted on 2012-03-14
11
492 Views
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,;
  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
0
Comment
Question by:gaby12
  • 7
  • 2
  • 2
11 Comments
 

Author Comment

by:gaby12
ID: 37720308
Correct mistake please:
SQL view
0
 
LVL 29

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.
0
 

Author Comment

by:gaby12
ID: 37722989
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
0
 

Author Comment

by:gaby12
ID: 37722998
0
 

Author Comment

by:gaby12
ID: 37723001
As I said, I attached the PrtScr of the main table and the resulting view.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Expert Comment

by:jrbbldr
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:
   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
0
 

Author Comment

by:gaby12
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.
0
 
LVL 12

Assisted Solution

by:jrbbldr
jrbbldr earned 100 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
0
 

Author Comment

by:gaby12
ID: 37726001
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
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 400 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.
0
 

Author Closing Comment

by:gaby12
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,
Gabriela

Thank also jrbbldr for your reccomendations.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now