1. Thomas J. Hall, III
  2. Valentina Studio
  3. Wednesday, July 08 2020, 08:51 PM
  4.  Subscribe via email
I began using Pro Serial this week, so I am new at the game.

I am trying to figure out how to add a Button or a Tool Button to a form and have it perform an SQL statement. I am using a local database with SQLite.

I have an 8 field table into which I have some 15-20 frequently occurring entries. I want to have a series of buttons which will pre-fill certain fields so I will not have to manually enter the data (I have this implemented in FileMaker which I am trying to replace). I have written an INSERT INTO statement in SQL Editor which puts the data into the correct fields when I click the “Execute” button on SQL Editor. However, I have not been able to hit on the correct method to attach the statement to a button and have the procedure executed. Can someone help me?
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Joe,

Does the TableCursor have to be called or reset before using it? What if it is used again for a different record?

TableCursor is just the "records" of the table.
When we click prev/next buttons we use the same TableCursor, just the current position is switched.
So it is really used for multiple records.

records = this.table.getTableCursor( this.uuid ) is equivalent to
records = this.records

so usually it is enough to write this.records and the form gets cursor on its own.

We don't specify the table name as it was defined during the form creation or later using the Records combo box in the top-right corner of the Form Editor area.


Does "this" limit the statement to one particular record while the one without can deal with multiple records?


A current record is always modified, that is not because of this.
We can use "records" without "this" because previously we assigned TableCursor to this variable.

Is it possible to respond to each post individually, but I couldn’t figure out how to click somewhere around the Comment box to do so? Which would you two prefer - individual comments to a particular post or periodic comments to multiple posts?


It is possible with the "Add Comment" button, but it seems not convenient for long answers, so let's keep as it is.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 61
Sergey Pashkov Accepted Answer
Kevin,

Ok, thank you, I think I understood.
So the task is to open a columnar form at a specified position.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 62
Sergey Pashkov Accepted Answer
Here's how to open a form with a specified record.

1) Create method in the child form, e.g. show_record, with code:

this.records.position = arguments[0];


It assigns the first argument to the cursor position

2) Call this method in a parent form after import:

import '/city_detail' as dlg
dlg.show_record( 5 );
dlg.exec();
Attachments (2)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 63
Thomas J. Hall, III Accepted Answer
Sergey and Kevin

I was working this afternoon on Clearing the form by inserting NULL or an empty string.

I tried two scripts for my CLEAR button and each of them returned an error message:

Transactions - Form::clicked:3: SyntaxError: Expected token `)'

The two scripts were:

records = this.table.getTableCursor( this.uuid );
records.getField( ‘Date’ ).isNull = true;
records.getField( ‘RefNo ).isNull = true;
records.getField( ‘Name’ ).isNull = true;
records.getField( ‘Amount’ ).isNull = true;
records.getField( 'Debit' ).isNull = true;
records.getField( 'Credit' ).isNull = true;
records.getField( 'Stmt' ).isNull = true;
records.getField( 'Comment' ).isNull = true;
records.updateRecord();


records = this.table.getTableCursor( this.uuid );
records.getField( 'Date’ ).value = "";
records.getField( ‘RefNo’ ).value = "";
records.getField( ‘Name’ ).value = "";
records.getField( ‘Amount’ ).value = "";
records.getField( 'Debit' ).value = "";
records.getField( ‘Credit’ ).value = "";
records.getField( ‘Stmt’ ).value = "";
records.getField( ‘Comment’ ).value = "";
records.updateRecord();

I will attach a copy of the form.

You can see I was also trying to make the GoTo button work but I couldn’t, gave up, and moved on. I haven’t had a lot of time today.

Joe
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 64
Thomas J. Hall, III Accepted Answer
Pardon me. As I was closing I think I see what happened. Once again smart quotes snuck in. I had opened another editor and forgot to disable the smart quotes.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 65
Thomas J. Hall, III Accepted Answer
Sergey & Kevin,

I opened Lesson 6 on filtering (roducts:vstudio:tutorials:forms:lesson6" target="_blank" rel="nofollow">http://valentina-db.com/docs/dokuwiki/v10/doku.php?id=valentina:products:vstudio:tutorials:forms:lesson6) and read through it. It seemed above my level of competence.

I dropped back to Lesson 5 (roducts:vstudio:tutorials:forms:lesson5" target="_blank" rel="nofollow">http://valentina-db.com/docs/dokuwiki/v10/doku.php?id=valentina:products:vstudio:tutorials:forms:lesson5) on Simple Filtering and it seemed more on my ability level, so I gave it a shot.

I left the Line Edit box in place and simply changed the name showing (I don't really understand what this type of button does, so I am using it as a de-facto label. I then went to Database>Tables>Transactions>TransNo and pulled that field onto my form. I followed the directions and changed its Data Mode to Filter and Apply. I saved and executed. It works like a charm. Any TransNo I enter will immediately show in the form. I can enter new ones one after the other and they will pop up. That is great.

BUT PROBLEM - I cannot use the other navigation buttons |< < > >|. The other buttons Update, Clear, etc. work - even the Kroger button. I have to Close the form and re-Execute to be able to use the navigation buttons again
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 66
Sergey Pashkov Accepted Answer
Yes, this is because filtering changes the set of shown records.
You can clear a filter with a button.

But the only way to implement "Go To" functionality exactly as you described is in reply #55
It is just one or two lines of code
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 67
Kevin Accepted Answer
Sergey,

>"Go To" functionality

Wow. Thanks.


rn = parseInt( this.RecordNumber.text ) - 1;
import '/frm_Transactions_Main' as dlg
dlg.gotoRecord( rn );
dlg.exec();


On the TableView, I have a LineEdit not associated with a field named RecordNumber. In TableView, one sees row numbers and that is what probably one will enter into the LineEdit, thus the subtraction of 1 (Sergey had previously written: "(record numbers are 0-based)". Assign to a variable.

It works.

Again, thank you for your assistance.

Kevin
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 68
Thomas J. Hall, III Accepted Answer
Sergey
Re: #55 & #66

I have gone back to Tutorial 6 and implemented it as set out, but I keep getting an error message: [clicked] ReferenceError: leSearch is not defined

Here are the screen shots. I added the new Horizontal BoxLayout directly below the form.

Joe
Attachments (7)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 69
Kevin Accepted Answer
Joe,

I have attempted this lesson yet, but looking at the code and the error message ...

I think this:


var search = leSearch.text;


Should be this:
var search = this.leSearch.text;




The other thing it might be is I (capital i) vs l (small L) ... the field name and the code might contain different characters.

Kevin
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 70
Kevin Accepted Answer
Wish there was a way to view a post before posting or an edit. (Maybe there is and I don't see it.)

The edits:

I haven't attempted this lesson yet, but looking at the code and the error message ...

I think this:


var search = leSearch.text;


Should be this:


var search = this.leSearch.text;


The other thing it might be is I (capital i) vs l (small L) ... the field name and the code might contain different characters.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 71
Thomas J. Hall, III Accepted Answer
Kevin,

I tried: var search = this.leSearch.text;
I got another error message: [clicked] TypeError: Value is undefined and could not be converted to an object

I also went back and made sure the Properties>Name of the line edit box was leSearch with an "l" (lower case L). I did the same in the script for the button.

Joe
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 72
Thomas J. Hall, III Accepted Answer
Sergey
Re: #56

If you declare a field like that (without AUTO_INCREMENT):
...
TransNo INTEGER PRIMARY KEY
...
TransNo becomes an alias for ROWID, and therefore auto-incremented.
Each inserted row gets TransNo=max(TransNo)+1;
So if the new record is deleted, its TransNo will be used for the next INSERT.


I did this for my TransNo field. I turned off AUTO_INCREMENT. Now if I delete a record, even if I have Updated it, the TransNo for the next Add is the last TransNo used +1. Such a simple solution. Thanks.

Joe
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 73
Sergey Pashkov Accepted Answer
Kevin & Joe,

Yes, this.leSearch is a correct form (this was optional previously).
I'll revisit this example now.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 74
Kevin Accepted Answer
Sergey,

Is there a way to display the variable values as the code works it way through? Or some code that creates a pop up with the variable value?

When I see this:


var condition = '"' + fldName + '"' + this.cbCriteria.currentText + "'" + search + "'";


I am thinking the final result will be:

"Name"LIKE'Kroger'

I don't see any spaces being built in. Or is that what is occurring upstream with all the replace code.

Kevin
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 75
Sergey Pashkov Accepted Answer
Kevin,

Right, it generates it like this, and SQLite and Valentina support such a query.

Popup can be found in the Standard Dialogs->Information template
QMessageBox.information( 'Title', condition );

Also, we can print to console (but it is necessary to open it beforehand):
console.log( condition );
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 76
Kevin Accepted Answer
I got the complex filter working here. Did update leSearch. And I put in spaces between the field and operator and search term. I had one typo that was breaking things.

Joe, looking through your screen prints again ... I see you have the discard filter named tb_ ... but in the code there isn't an underscore.

Sergey, thanks for the info regarding the QMessageBox and console.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 77
Sergey Pashkov Accepted Answer
Exactly! The button has different name than it has in the example.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 78
Thomas J. Hall, III Accepted Answer
re: #76 & #77

Oops I had missed the underline.

I got the filter working thanks to Kevin's suggestion to put a space between the = and this.
My discard button is still not working.

records = this.table.getTableCursor( this.name );
records.clearFilters();
records.applyFilters();

this.tbDiscardFilter.enabled = false;
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 79
Kevin Accepted Answer
Joe, if you kept the underscore in the field name for the discard button, you need to add the underscore to the above script. Your previous screen print shows you had the underscore in the script, but you might have changed it.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 80


There are no replies made for this post yet.
However, you are not allowed to reply to this post.