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

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();

Kevin,

Ok, thank you, I think I understood.
So the task is to open a columnar form at a specified position.

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.

Sergey,

Go to ... I was trying to create a script based on what Joe wanted. Joe was starting at a TableView. Would see a record he wanted to open in formLayout. So I created a button on the TableView and entered the go to script. I like starting at the basics before adding complexity. I figured it would automatically scroll to the record. It is OK it doesn't because that wasn't what Joe wanted it for. He wanted to see a record, enter the record number, and click on a button and go to that record in a different form. I initially had the variable line above the Import. Assign record number entered into box to variable, import form, go to record using the variable. That didn't work. Remove complexity of the variable and hard code a record number to see if that even works. It did not. The formLayout opens, but opens at the first record. No errors.


Kevin

Re: #53
So TableCursor gets the focus to a particular table and this.uuid further focuses the action to a particular record or records. I surmise you do not have to actually name the table (this.table.getCursor) nor do you need to specify the record(s). It seems the statement this.table.getTableCursor( this.uuid ); merely alerts the program/script that it is to perform some action on the current record.
Does the TableCursor have to be called or reset before using it? What if it is used again for a different record?

Re:#54
In 9.7, unfortunately, no, but we removed coloring in the latest versions.
OK, I can live with the color on these prototype Forms. I will be upgrading, but I am still on my MacMini. Rusian set it up so I could use this ProSerial for 30 days to work with Forms to see if I could put together a replacement for FileMaker. I plan to move to LinuxMint and get StudioPro for that system.

An empty string is a string with no characters, also it is necessary to use the value property for assignment:
records.getField( 'Debit' ).value = "";
I am starting to see certain tools reused. That is good. I note this statement is almost the same as the one you gave me to set up my pre-fill buttons. The pre-fill buttons statement was this.records.getField( 'Name' ).value = 'KROGER'; and this one drops the "this." before "records." Does "this" limit the statement to one particular record while the one without can deal with multiple records?

Re: #55 & 56
Exactly what I needed. Will give it a try today

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?

Kevin,

So you need to scroll to some record in a TableView?

Position property is integer, so "go to" would look like, just checked with SQLite database

Sergey, Thanks. I was using the "go to" on a TableView, that is why it wasn't working.

import '/frm_Transactions_Main' as dlg

//rp = parseInt( this.RecordNumber.text ) - 1;

if ( dlg.exec() )

    this.records.position = 25;

The above code doesn't work. Going from a TableView to Single Record View. Had found an example in Valentina's documentation (Form Editor - Scripts), but I am not sure if it is using something from one to drive the conditions in the other. I had tried the variable. Thought maybe that was the problem and went with a hard coded just to see if it worked. No errors. Just goes to the first record.

Joe,

Are you saying that if I use ROWID as my primary key instead of my TransNo field that I can still have the TransNo increment, but it will only do so if the new record is saved? If the new record is deleted it will not increment?

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 f the new record is deleted, its TransNo will be used for the next INSERT.

But using the approach with a single form to add/edit it is necessary to delete it explicitly when the Cancel button is pressed, like:

this.records.deleteRecord()

Otherwise, this record will remain in the database.

Joe and Kevin,

Position property is integer, so "go to" would look like, just checked with SQLite database:

this.records.position = 5;

You can write the following code for the returnPressed signal to navigate to the record number entered in lineEdit control (record numbers are 0-based)

this.records.position = parseInt( this.lineEdit.text );

Also, you can display the current record number in this control using needUpdate signal

this.lineEdit.text = this.records.position.toString();

I have been playing with some horizontal forms (like spreadsheets) today and have found any fields that are NULL are a hideous shade of pink which overshadows the actual data in the form. Is there some way to get rid of the color. I may have to go back and enter an empty string into them as you suggested above.

In 9.7, unfortunately, no, but we removed coloring in the latest versions, so if you'll be using new versions, there is just <NULL> text and no special color.

An empty string is a string with no characters, also it is necessary to use the value property for assignment:
records.getField( 'Debit' ).value = "";

Joe,

records = this.table.getTableCursor( this.uuid );
What in the world does that mean? What is a TableCursor? Is this a JavaScript something?

Not JavaScript. It's more database related.

• A simple cursor is based on an arbitrary query (e.g. SELECT f1, f2 FROM table1 WHERE f3 < 5) - it is a result (recordset, row set). In some cases, it can update the record, but in the general case, it is a read-only object
  
• A TableCursor is a cursor based on a table, it can access any record and field of this table - insert/update/delete.
  

this.uuid - is an internal identifier of the form. Different forms work with different table cursors.
So

this.table.getTableCursor( this.uuid );

returns the set of records for this form.

Actually, we can write this.records instead, it does the same thing internally.

Forum parser broke the links.
For the TableView only a complex filter lesson can be applied, I added a link at the bottom of the post.
It is 'complex' in terms that we can manually specify any condition.

Here's how to do it:

records = this.TableView.records;

records.clearFilters();

records.addFilter( 'city_id < 5' );

records.applyFilters();

Looks like the first two links in my last post got mangled.

Try again.

Form Simple Filter Tutorial:

roducts:vstudio:tutorials:forms:lesson5" target="_blank" rel="nofollow">Title

Form Complex Filter Tutorial:

roducts:vstudio:tutorials:forms:lesson6" target="_blank" rel="nofollow">Title

Joe,

If you want to build filters into the forms:

Form Simple Filter Tutorial:

roducts:vstudio:tutorials:forms:lesson5" target="_blank" rel="nofollow">https://valentina-db.com/docs/dokuwiki/v9/doku.php?id=valentinaroducts:vstudio:tutorials:forms:lesson5

Form Complex Filter Tutorial:

roducts:vstudio:tutorials:forms:lesson6" target="_blank" rel="nofollow">https://valentina-db.com/docs/dokuwiki/v9/doku.php?id=valentinaroducts:vstudio:tutorials:forms:lesson6

For filtering, I open the database in the Data Editor (see tutorial below). Or I use queries outside of forms.

Data Editor - Filters - Tutorial:

https://www.youtube.com/watch?v=ia2ESd3PV70

Go to record, there is a template one can place in a button:

this.records.position = ...

I couldn't make it work at its most simplest (going to a record in the current table). Unclear what it wants after the "...".

No error, but doesn't work:

this.records.position = 49

Error (19:58:59 [option<Integer>] Bad option access.):

this.records.position = '49'
this.records.position = "49"

I thought something like this would work:

this.records.position = this.RecordNumber.text

Where RecordNumber is the name of a LineEdit widget not associated with a field. One would enter the record number into this widget.

But I couldn't get the simplest to work, so that needs to be solved, before adding layers of complication.

Kevin

Sergey and Kevin,

I like this way of clearing the fields.
records = this.table.getTableCursor( this.uuid );
records.getField( 'Debit' ).isNull = true;
records.getField( 'Credit' ).isNull = true;
records.getField( 'Stmt' ).isNull = true;
records.getField( 'Comment' ).isNull = true;
records.updateRecord();
You mentioned “assigning an empty string” as a value.
Would that be:
records.getField( 'Debit' ) = " "

I have another few questions.

You both have suggested scripts which use this language:
records = this.table.getTableCursor( this.uuid );
What in the world does that mean? What is a TableCursor? Is this a JavaScript something?

I have been playing with some horizontal forms (like spreadsheets) today and have found any fields that are NULL are a hideous shade of pink which overshadows the actual data in the form. Is there some way to get rid of the color. I may have to go back and enter an empty string into them as you suggested above.
I would also like to be able to go direct to a record from a button in the Horizontal Box Layout at the bottom of a form. I have seen in implemented (ex. DB Browser for SQLite which has a box reading “Go To” and next to it an empty box into which one can enter a record number and be taken to that record). This would be very helpful in navigating through my Vertical forms which only show one record at a time. How would I do that?

I think I about have the pre-defined buttons solved. Now I need to work on filtering. Again, in DB Browser for SQLite there is the ability to have a filter box at the top of each column into which one can enter a filter term and the program will perform the filtering immediately. I am linking my Valentina project to a couple of databases setup and saved by DB Browser for SQLite and I still have that ability if I open the underlying database. What I wonder is if the same thing can be done on a Form or do I have to use a Report or Query?

After I get the filtering settled, I need to dig into links if I decide to incorporate some of the income allocation (ie. assigning income to a specific security) capabilities I have set up on FileMaker.

Re: AUTO_INCREMENT. Sergey I struggled with it some this morning before other tasks took me away. I was not very successful. Your latest post was most interesting and I will ponder it. Your statements about the differences with INTEGER PRIMARY KEY will take some thought. Are you saying that if I use ROWID as my primary key instead of my TransNo field that I can still have the TransNo increment, but it will only do so if the new record is saved? If the new record is deleted it will not increment? I will dig into your links tomorrow morning. I am still wavering here and as I told Kevin I may punt and go back to the way I have done it for the past 20-30 years.

Joe

Or even shorter:

records = this.table.getTableCursor( this.uuid );

records.getField( 'Debit' ).isNull = true;

records.getField( 'Credit' ).isNull = true;

records.getField( 'Stmt' ).isNull = true;

records.getField( 'Comment' ).isNull = true;

records.updateRecord();

Also, if some field is not-nullable, it is necessary to assign an empty string as a value instead of isNull.

Joe,

Here's a good explanation about SQLite autoincrement:
https://www.sqlitetutorial.net/sqlite-autoincrement/

So it is not necessary to specify AUTO_INCREMENT flag if you have INTEGER PRIMARY KEY, as it is an alias for ROWID which is autoincremented.
Also, if the last record is deleted, SQLite reuses its ID for the next inserted record.

With AUTO_INCREMENT flag, ID of the deleted record is not reused.
However there is a workaround for such a situation as written here, and you can manually set the current autoincrement value:
https://stackoverflow.com/a/26332544

Joe, I just noticed the code I posted has a records.updateRecord() after each. Only the last one is needed. Kevin

Joe,

>I had tried it after clicking my “Kroger” button ... and those fields were cleared.

Ah it appears its power is undoing a single action (update, import?, I haven't tested it, just guessing). Imagine if one updated hundreds of records at once and realized it needed to be undone.

Create a new Clear button and add the following:

records = this.table.getTableCursor( this.uuid )
field = records.getField( 'Date' )
field.isNull = true
records.updateRecord()

records = this.table.getTableCursor( this.uuid )
field = records.getField( 'RefNo' )
field.isNull = true
records.updateRecord()

records = this.table.getTableCursor( this.uuid )
field = records.getField( 'Name' )
field.isNull = true
records.updateRecord()

records = this.table.getTableCursor( this.uuid )
field = records.getField( 'Amount' )
field.isNull = true
records.updateRecord()

records = this.table.getTableCursor( this.uuid )
field = records.getField( 'Debit' )
field.isNull = true
records.updateRecord()

records = this.table.getTableCursor( this.uuid )
field = records.getField( 'Credit' )
field.isNull = true
records.updateRecord()

records = this.table.getTableCursor( this.uuid )
field = records.getField( 'Stmt' )
field.isNull = true
records.updateRecord()

records = this.table.getTableCursor( this.uuid )
field = records.getField( 'Comment' )
field.isNull = true
records.updateRecord()

I am assuming TransNo is your auto increment field, thus I didn't include that.

Kevin