Day 013 - Navigating through records and creating a query

Today's installment begins at page 106 in the tutorial, the Generic Search topic. This is a locator type search, where you type some text and find the records where the specified field begins with the entered text.

I created another entry field and a button, and added the following code to the button. As I was typing the code I noticed a help window popping up, and noting the hot key text I realized I could press Alt-F1 at any time in a parameter list to get help on that specific parameter. 

As the tutorial points out, you can force an exact match with HReadSeek by using the hIdentical constant:

The code works as advertised, which is that the first matching record is displayed, unless no record is found in which case the Error function displays a message. Annoyingly (to me) the message is accompanied by the Windows critical error sound. I tried the Message() statement but that just puts a message in the status bar. I couldn't find a noise-less Error function. 

Modifying records

Displaying a record is one thing - what about modifying it? Here's some button code that does just that:

ScreenToFile()
HModify(Person)
ListDisplay(COMBO_Person,taCurrentSelection)

ScreenToFile copies the screen variables to the file buffer.HModify updates the file. But there's still that combo box on the screen with a drop list of names. The ListDisplay function refreshes the combo box, retaining the current selection. 

Navigation

Navigating through records is pretty straightforward. Here's the code for the First, Previous, Next and Last buttons:

The various HRead... functions attempt to retrieve a record, and the HOut function indicates whether or not the record is empty. 

The Info dialog uses a marginally less annoying sound than the Error dialog, but I still can't find a way to display a message without triggering a sound. 

Queries

So far the tutorial has looked at record by record data retrieval. But there's another day to retrieve data, and that's by using a query. 

Queries are separate, reusable entities in WinDev. You can create them via File | New | Query.

WinDev has a pretty broad definition of a query. I tend to think of queries as ways of returning data from one or more tables in the database, but WinDev also allows you to define "queries" that insert, update or delete records. If you're familiar with SQL, then you may want to think of WinDev queries as encompassing both select statements and stored procedures. 

If you creat a Select query you'll see the following window:

Here I've defined the fields to include, and I've specified that I want to sort on the Movement.DateMvt field:

Per the instructions I've also defined a filter which requires me to pass in two parameters:

In the query editor window note the "Everyday Language" description of the select statement. I can also view the SQL code:

SELECT 
  Account.AccountNum AS AccountNum,  
  Movement.DateMvt AS DateMvt,  
  Movement.Heading AS Heading,  
  NATUREMVT.Heading AS Heading_NA,  
  Movement.Amount AS Amount
FROM 
  Account,  
  Movement,  
  NATUREMVT
WHERE 
  Movement.DateMvt BETWEEN {Param1} AND {Param2}
ORDER BY 
  DateMvt ASC

This SQL looks pretty odd to me as there are no JOINs. I'll dig into that tomorrow.