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
Hello Thomas,

Execute SQL using the current datasource:
this.dataSource.sqlExecute( 'INSERT INTO...' );

You can generate code templates using Templates button, Templates->Database->Execute SQL..., in the top-right corner of the button script editor.
Also, the templates are accessible in the context menu of the button Set Action->Database->Execute SQL...
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 1
Thomas J. Hall, III Accepted Answer
Sergey,

Thank you for your suggestions.

I do not know if this will affect my attempts to attach SQL statements to a Form button, but I am using Studio 9.7. I have an old MacMini which cannot use any OSX later than 10.12, so I cannot use the latest version of Studio. In fact the reason I am trying to transfer all my database to SQL using Valentina is to let me obtain a newer computer and run the latest version of Linux Mint.

Anyway, I tried your first suggestion - Execute SQL using the current datasource:
this.dataSource.sqlExecute( 'INSERT INTO...' );
I opened an Add form which I modified from Lesson 4 of your Studio Forms tutorial (roducts:vstudio:tutorials:forms:lesson4" target="_blank" rel="nofollow">https://www.valentina-db.com/docs/dokuwiki/v8/doku.php?id=valentina:products:vstudio:tutorials:forms:lesson4).
I moved a button onto the form from Palette. I clicked on it. I chose the “pushButton…:QPushButton - clicked” option in the upper right portion of the “Signals” pane.
I entered:
this.dataSource.sqlExecute( INSERT INTO "Transactions" ("Amount", "Comment", "Credit", "Date", "Debit", "Name", "RefNo", "Stmt", "TransNo";)
VALUES (0, ' ', '5014', CURRENT_DATE, '1000', 'KROGER', ' ', 'NO', NULL); into the button and clicked “OK”
I chose “Execute” to open the Form.
I clicked the button and got this error message:
Transactions - Add::clicked:2: SyntaxError: Expected token `)'
This is the same SQL statement I ran/executed from SQL Editor yesterday. It worked.
What does the error message :SyntaxError: Expected token `)’ mean? This is the same error message I receive numerous times yesterday while I was trying every way I could think of to make a button or a tool-button execute my SQL statement.

Concerning your second suggestion about a Template button - I cannot find any reference to a Template button. It is not on Palette. I cannot find it on the Signals panel when working with buttons.
I found and tried the “Set Action->Database->Execute SQL” option by entering my INSERT INTO statement into the “Query Text” screen which pops up, but I could not get that to work either.

I appreciate your working with me

Joe Hall
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 2
Sergey Pashkov Accepted Answer
Do you have quotes around the query text?
Note about quoting - if you use single quotes - it is necessary to escape (with \ character) single quotes that are part of the query, if double quotes - escape double quotes.

The Templates button is in the following dialog:
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 3
Thomas J. Hall, III Accepted Answer
Sergey,

I am having trouble understanding your suggestions. Do you have quotes around the query text?

Do you mean:
1. “this.dataSource.sqlExecute( ‘INSERT INTO …’ );”
2. ‘this.dataSource.sqlExecute( ‘INSERT INTO …’ );’
3. \“this.dataSource.sqlExecute( ‘INSERT INTO …’ );\“
4. \’this.dataSource.sqlExecute( ‘INSERT INTO …’ );\’

5. this.dataSource.sqlExecute( ” ‘INSERT INTO …’ ”);
6. this.dataSource.sqlExecute( ‘ ‘INSERT INTO …’ ‘);
7. this.dataSource.sqlExecute( \“ ‘INSERT INTO …’ \“);
8. this.dataSource.sqlExecute( \’ ‘INSERT INTO …’ \“);

I tried all of the above inserted into both the Tool button and the button without success

Also, my actual entries came out like this:
“this.dataSource.sqlExecute( ‘INSERT INTO "Transactions" ("Amount", "Comment", "Credit", "Date", "Debit", "Name", "RefNo", "Stmt", "TransNo";)
VALUES (0, ' ', '5014', CURRENT_DATE, '1000', 'KROGER', ' ', 'NO', NULL[u][u]);’ );”
or
his.dataSource.sqlExecute( \“‘INSERT INTO "Transactions" ("Amount", "Comment", "Credit", "Date", "Debit", "Name", "RefNo", "Stmt", "TransNo";)
VALUES (0, ' ', '5014', CURRENT_DATE, '1000', 'KROGER', ' ', 'NO', NULL);’ \“);

Are these );’ );” or );’ \“); correct or do I need to modify the last part of the INSERT INTO statement?

Finally, I did not understand you statement:
The Templates button is in the following dialog:
this.dataSource.sqlExecute( ‘INSERT INTO …’ );
I looked in both the Set Action->Dialog and the Set Action->Standard Dialogs for both the regular button and the Tool button and could find no mention of Templates. Are Template buttons a separate type of button from the regular button or the Tool button? Would a Template button work better for what I am trying to do than either one of the ones I am trying?

I am a novice and am struggling here. Please bear with me.

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

Complete query example (execute_sql.png):
this.dataSource.sqlExecute(
'INSERT INTO "actor" ( "first_name", "last_name") VALUES ( \'first_name\', \'last_name\' );' );


I attached the Templates button in the pressed state (templates_button_menu.png).
Note, there is an issue with quoting in the "Execute SQL..." template - it generates wrong escape characters, so it is necessary to fix them manually.
Attachments (2)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 5
Thomas J. Hall, III Accepted Answer
I took your example, modified it, and entered it into the “pushButton…:QPushButton - clicked” option for my button.

Your example:
this.dataSource.sqlExecute(
'INSERT INTO "actor" ( "first_name", "last_name";) VALUES ( \'first_name\', \'last_name\' );' );[/code

I tried all the following. They did not work. The error message for each of them read:
Transactions - Add:: clicked:3: SyntaxError: Expected token ‘)’

NO SPACE AFTER BEGINNING ( NOR BEFORE ENDING )
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ("Amount", "Comment", "Credit", "Date", "Debit", "Name", "RefNo", "Stmt", "TransNo";)
VALUES (0, \’ \’, \’5014\’, CURRENT_DATE, \’1000\’, \’KROGER\’, \’ \’, \’NO\’, NULL);' );[/code]

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ("Credit", "Date", "Debit", "Name";)
VALUES (\’5014\’, CURRENT_DATE, \’1000\’, \’KROGER\’);' );[/code]



SPACE AFTER BEGINNING ( AND BEFORE ENDING )
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Date", "Debit", "Name" )
VALUES ( \’5014\’, CURRENT_DATE, \’1000\’, \’KROGER\’ ) ;' );[/code]

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Debit", "Name" )
VALUES ( \’5014\’, \’1000\’, \’KROGER\’ ) ;' );[/code]

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Name" )
VALUES ( \’KROGER\’ ) ;' );[/code]


REMOVED [/code]
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Name" )
VALUES ( \’KROGER\’ ) ;' );

✦ ✦ ✦ ✦ ✦ ✦

I did find the Template reference. Thanks. I read your “Note, there is an issue with quoting in the "Execute SQL..." template - it generates wrong escape characters, so it is necessary to fix them manually.” So, I did not try anything there. I am already having enough trouble with my INSERT INTO problem.

What am I still doing wrong?

Joe Hall
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 6
Sergey Pashkov Accepted Answer
There are a lot of instead of ' quotes, please try to replace them.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 7
Thomas J. Hall, III Accepted Answer
Sergey,

I had no idea I was using the wrong quotations marks. I went back in and disabled the smart quotes and modified all my INSERT INTO tries to:

Notice, I could not tell if you left a space before each " Field" or not "Field"; not with ' Value' or not 'Value'. I also took out all the spaces at the end of the Value string - KROGER\');'); not KROGER\' ); ');.

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ("Credit", "Debit", "Name";)
VALUES (\' 5014\',\' 1000\',\' KROGER\');');

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ("Name";)
VALUES (\'KROGER\');');

However, that still did not work. I am still getting the same error message - SyntaxError: Expected token `)'
I do note now that the first character of the error message is ` and not ' is there any significance to this?



This may be exceeding the scope of what you can offer as help, but here is what I am trying to do. I have attached a .png of the input form for my FileMaker accounting database which I have used for the past 15+ years. As you can see it has a slew of push buttons which I can use to pre-fill fields in the form to keep from having to manually enter each one separately.

The second .png is the input form I have put together in Valentina. I want to be able to push the KROGER button and have the Name, Debit, and Credit fields pre-fill. Ideally, to mimic my FileMaker procedure the cursor would end up on the Amount field and all I would have to do is enter the dollar amount and save the transaction.

These pre-filling buttons save me a ton of time and the ability to implement them in my SQLite database is a make-or-break requirement for me. Setting up a database that would require me to manually enter each and every field entry would be a big step back and probably not worth it to me.

Could you give me a solution to allow me to implement the pre-filling buttons?

After playing around with Valentina, doing the tutorials, and just trying things out, it looks like I will be able to do the other things I need like linking tables, opening other forms, etc. I just have this one big hurdle which I cannot seem to get over.

Joe Hall
Attachments (2)
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Hello Joe,

Please add a screenshot of the window with a button script.

I'll add more details about the pre-filling buttons later.
Comment
There are no comments made yet.
Thomas J. Hall, III Accepted Answer
Sergey,

If I understood you correctly. Here are three of the scripts for three different FileMaker buttons.

If you meant the Valentina Input form buttons, those scripts are the INSERT INTO you have been trying to get me to use.

Joe
Attachments (3)
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Yes, screenshots of scripts in the Valentina Studio.
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Hello Joe,

JavaScript requires an escape character before a new line if a string literal is spread across multiple lines

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Debit", "Name" ) \
VALUES( \'5014\', \'1000\', \'KROGER\' );' );


Syntax highlighting helps us to catch such problems, please compare to the attached screenshot
Attachments (1)
Comment
There are no comments made yet.
Thomas J. Hall, III Accepted Answer
Sergey,

It looks like I will have to dig into JavaScript. I had not considered that.

SUCCESS!
The escape character “\” worked. The information was entered into the new record.

On to my next problem. The execute screen immediately disappears so I cannot enter additional data into other fields. I can go to the Transaction database and see the data there, but I do not get the opportunity to finish my entry. I have tried several ways to solve my problem by adding to the original script:
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Debit", "Name" ) \
VALUES( \'5014\', \'1000\', \'KROGER\' );' );

After a half-dozen different approaches I tried this which worked to keep the form alive, but any additional entries were entered into the next-to-last record. The TransNo field auto-increments and I can’t move forward to that new record:

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Debit", "Name" ) \
VALUES( \'5014\', \'1000\', \'KROGER\' );' );
this.records.updateRecord();
this.records.lastRecord();

I also tried:

this.records.lastRecord();
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Debit", "Name" ) \
VALUES( \'5014\', \'1000\', \'KROGER\' );' );
this.records.updateRecord();
this.records.lastRecord();
this.records.reloadRecords();

and

this.records.lastRecord();
this.records.reloadRecords();
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Debit", "Name" ) \
VALUES( \'5014\', \'1000\', \'KROGER\' );' );
this.records.updateRecord();
this.records.reloadRecords();
this.records.lastRecord();

For some reason I cannot get the last record to come up for modification.

The last problem is when I close the “Execute” panel the program adds another record filled with only the INSERT INTO data. The “Close” button has the following script: this.accept(); . How can I have the panel just close without making any changes to the database?

Joe Hall
Attachments (4)
Comment
There are no comments made yet.
Kevin Accepted Answer
The “Close” button has the following script: this.accept(); . How can I have the panel just close without making any changes to the database?



this.reject();


I have a form that adds records. Thus, Data Mode is set to Add. To close the form without adding any records I use the above code. It seems to work.
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Thank you, Kevin, right, this.reject(); is the correct code for Close button.
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Valentina Studio forms have Data Mode property with the following possible values:

  • Add - for adding a new row, INSERT INTO statement is executed automatically with values entered in form
  • Edit - for editing an existing row, in order to apply changes we have to call this.record.updateRecord(), which issues UPDATE statement
  • Edit And Commit - much like Edit but updateRecord() is called automatically
  • ReadOnly - viewing without editing


So we have to create different forms for adding and editing.
As you can see, it is possible to create forms without any manual SQL and use SQL only when built-in functionality is not enough.

this.records - is a set of records from table/view or a result of the execution of an arbitrary query.

When we need to refresh this set (e.g. records were modifed/added with SQL) we can call this.records.reloadRecords();
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
As for the pre-filling buttons.
If a LineEdit control is bound to a field, a button code should look like:

this.field_name_control.text = 'my_value';
this.records.getField( 'field_name' ).value = 'my_value';


The first line sets the value to control (field_name_control).
The second line sets it to the field 'field_name' of the current record in the underlying recordset.
(Most probably the second step will not be necessary in the next versions)
Comment
There are no comments made yet.
Thomas J. Hall, III Accepted Answer
You can generate code templates using Templates button, Templates->Database->Execute SQL..., in the top-right corner of the button script editor.

Also, the templates are accessible in the context menu of the button Set Action->Database->Execute SQL...


Sergey,

Thank you for your suggestions.

I do not know if this will affect my attempts to attach SQL statements to a Form button, but I am using Studio 9.7. I have an old MacMini which cannot use any OSX later than 10.12, so I cannot use the latest version of Studio. In fact the reason I am trying to transfer all my database to SQL using Valentina is to let me obtain a newer computer and run the latest version of Linux Mint.

Anyway, I tried your first suggestion - Execute SQL using the current datasource:
this.dataSource.sqlExecute( 'INSERT INTO...' );
I opened an Add form which I modified from Lesson 4 of your Studio Forms tutorial (roducts:vstudio:tutorials:forms:lesson4" target="_blank" rel="nofollow">https://www.valentina-db.com/docs/dokuwiki/v8/doku.php?id=valentina:products:vstudio:tutorials:forms:lesson4).
I moved a button onto the form from Palette. I clicked on it. I chose the “pushButton…:QPushButton - clicked” option in the upper right portion of the “Signals” pane.
I entered:
this.dataSource.sqlExecute( INSERT INTO "Transactions" ("Amount", "Comment", "Credit", "Date", "Debit", "Name", "RefNo", "Stmt", "TransNo";)
VALUES (0, ' ', '5014', CURRENT_DATE, '1000', 'KROGER', ' ', 'NO', NULL); into the button and clicked “OK”
I chose “Execute” to open the Form.
I clicked the button and got this error message:
Transactions - Add::clicked:2: SyntaxError: Expected token `)'
This is the same SQL statement I ran/executed from SQL Editor yesterday. It worked.
What does the error message :SyntaxError: Expected token `)’ mean? This is the same error message I receive numerous times yesterday while I was trying every way I could think of to make a button or a tool-button execute my SQL statement.

Concerning your second suggestion about a Template button - I cannot find any reference to a Template button. It is not on Palette. I cannot find it on the Signals panel when working with buttons.
I found and tried the “Set Action->Database->Execute SQL” option by entering my INSERT INTO statement into the “Query Text” screen which pops up, but I could not get that to work either.

I appreciate your working with me

Joe Hall

_______________________________________
Do you have quotes around the query text?
Note about quoting - if you use single quotes - it is necessary to escape (with \ character) single quotes that are part of the query, if double quotes - escape double quotes.

The Templates button is in the following dialog:
this.dataSource.sqlExecute( ‘INSERT INTO …’ );
_______________________________________
Sergey,

I am having trouble understanding your suggestions. Do you have quotes around the query text?

Do you mean:
1. “this.dataSource.sqlExecute( ‘INSERT INTO …’ );”
2. ‘this.dataSource.sqlExecute( ‘INSERT INTO …’ );’
3. \“this.dataSource.sqlExecute( ‘INSERT INTO …’ );\“
4. \’this.dataSource.sqlExecute( ‘INSERT INTO …’ );\’

5. this.dataSource.sqlExecute( ” ‘INSERT INTO …’ ”);
6. this.dataSource.sqlExecute( ‘ ‘INSERT INTO …’ ‘);
7. this.dataSource.sqlExecute( \“ ‘INSERT INTO …’ \“);
8. this.dataSource.sqlExecute( \’ ‘INSERT INTO …’ \“);

I tried all of the above inserted into both the Tool button and the button without success

Also, my actual entries came out like this:
“this.dataSource.sqlExecute( ‘INSERT INTO "Transactions" ("Amount", "Comment", "Credit", "Date", "Debit", "Name", "RefNo", "Stmt", "TransNo";)
VALUES (0, ' ', '5014', CURRENT_DATE, '1000', 'KROGER', ' ', 'NO', NULL);’ );”
or
his.dataSource.sqlExecute( \“‘INSERT INTO "Transactions" ("Amount", "Comment", "Credit", "Date", "Debit", "Name", "RefNo", "Stmt", "TransNo";)
VALUES (0, ' ', '5014', CURRENT_DATE, '1000', 'KROGER', ' ', 'NO', NULL);’ \“);

Are these );’ );” or );’ \“); correct or do I need to modify the last part of the INSERT INTO statement?

Finally, I did not understand you statement:
The Templates button is in the following dialog:
this.dataSource.sqlExecute( ‘INSERT INTO …’ );
I looked in both the Set Action->Dialog and the Set Action->Standard Dialogs for both the regular button and the Tool button and could find no mention of Templates. Are Template buttons a separate type of button from the regular button or the Tool button? Would a Template button work better for what I am trying to do than either one of the ones I am trying?

I am a novice and am struggling here. Please bear with me.

Joe Hall
_______________________________________
Hello Joe,

Complete query example (execute_sql.png):
this.dataSource.sqlExecute(
'INSERT INTO "actor" ( "first_name", "last_name") VALUES ( \'first_name\', \'last_name\' );' );


I attached the Templates button in the pressed state (templates_button_menu.png).
Note, there is an issue with quoting in the "Execute SQL..." template - it generates wrong escape characters, so it is necessary to fix them manually.

execute_sql.png execute_sql.png
this.dataSource.sqlExecute(
'INSERT INTO "actor" ( "first_name", "last_name";) VALUES ( \'first_name\', \'last_name\' );' );

templates_button_menu.png
pushButton…:QPushButton - clicked->Templates->Database->Execute SQL

✦ ✦ ✦ ✦ ✦ ✦

Hello Joe,

Complete query example (execute_sql.png):

this.dataSource.sqlExecute(
'INSERT INTO "actor" ( "first_name", "last_name";) VALUES ( \'first_name\', \'last_name\' );' );


I attached the Templates button in the pressed state (templates_button_menu.png).
Note, there is an issue with quoting in the "Execute SQL..." template - it generates wrong escape characters, so it is necessary to fix them manually.
_______________________________________

I took your example, modified it, and entered it into the “pushButton…:QPushButton - clicked” option for my button.

Your example:
this.dataSource.sqlExecute(
'INSERT INTO "actor" ( "first_name", "last_name";) VALUES ( \'first_name\', \'last_name\' );' );[/code

I tried all the following. They did not work. The error message for each of them read:
Transactions - Add:: clicked:3: SyntaxError: Expected token ‘)’

NO SPACE AFTER BEGINNING ( NOR BEFORE ENDING )
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ("Amount", "Comment", "Credit", "Date", "Debit", "Name", "RefNo", "Stmt", "TransNo";)
VALUES (0, \’ \’, \’5014\’, CURRENT_DATE, \’1000\’, \’KROGER\’, \’ \’, \’NO\’, NULL);' );[/code]

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ("Credit", "Date", "Debit", "Name";)
VALUES (\’5014\’, CURRENT_DATE, \’1000\’, \’KROGER\’);' );[/code]



SPACE AFTER BEGINNING ( AND BEFORE ENDING )
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Date", "Debit", "Name" )
VALUES ( \’5014\’, CURRENT_DATE, \’1000\’, \’KROGER\’ ) ;' );[/code]

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Debit", "Name" )
VALUES ( \’5014\’, \’1000\’, \’KROGER\’ ) ;' );[/code]

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Name" )
VALUES ( \’KROGER\’ ) ;' );[/code]


REMOVED [/code]
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Name" )
VALUES ( \’KROGER\’ ) ;' );

✦ ✦ ✦ ✦ ✦ ✦

I did find the Template reference. Thanks. I read your “Note, there is an issue with quoting in the "Execute SQL..." template - it generates wrong escape characters, so it is necessary to fix them manually.” So, I did not try anything there. I am already having enough trouble with my INSERT INTO problem.

What am I still doing wrong?

Joe Hall

_______________________________________
There are a lot of ’ instead of ' quotes, please try to replace them.

_______________________________________
Sergey,

I had no idea I was using the wrong quotations marks. I went back in and disabled the smart quotes and modified all my INSERT INTO tries to:

Notice, I could not tell if you left a space before each " Field" or not "Field"; not with ' Value' or not 'Value'. I also took out all the spaces at the end of the Value string - KROGER\');'); not KROGER\' ); ');.

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ("Credit", "Debit", "Name";)
VALUES (\' 5014\',\' 1000\',\' KROGER\');');

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ("Name";)
VALUES (\'KROGER\');');

However, that still did not work. I am still getting the same error message - SyntaxError: Expected token `)'
I do note now that the first character of the error message is ` and not ' is there any significance to this?



This may be exceeding the scope of what you can offer as help, but here is what I am trying to do. I have attached a .png of the input form for my FileMaker accounting database which I have used for the past 15+ years. As you can see it has a slew of push buttons which I can use to pre-fill fields in the form to keep from having to manually enter each one separately.

The second .png is the input form I have put together in Valentina. I want to be able to push the KROGER button and have the Name, Debit, and Credit fields pre-fill. Ideally, to mimic my FileMaker procedure the cursor would end up on the Amount field and all I would have to do is enter the dollar amount and save the transaction.

These pre-filling buttons save me a ton of time and the ability to implement them in my SQLite database is a make-or-break requirement for me. Setting up a database that would require me to manually enter each and every field entry would be a big step back and probably not worth it to me.

Could you give me a solution to allow me to implement the pre-filling buttons?

After playing around with Valentina, doing the tutorials, and just trying things out, it looks like I will be able to do the other things I need like linking tables, opening other forms, etc. I just have this one big hurdle which I cannot seem to get over.

Joe Hall

_______________________________________
Hello Joe,

Please add a screenshot of the window with a button script.

I'll add more details about the pre-filling buttons later.

_______________________________________

Sergey,

If I understood you correctly. Here are three of the scripts for three different FileMaker buttons.

If you meant the Valentina Input form buttons, those scripts are the INSERT INTO you have been trying to get me to use.

Joe
_______________________________________

Yes, screenshots of scripts in the Valentina Studio.
_______________________________________

Here are the Valentina Screenshots
_______________________________________
Hello Joe,

JavaScript requires an escape character before a new line if a string literal is spread across multiple lines
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Debit", "Name" ) \
VALUES( \'5014\', \'1000\', \'KROGER\' );' );


_______________________________________
Sergey,

It looks like I will have to dig into JavaScript. I had not considered that.

SUCCESS!
The escape character “\” worked. The information was entered into the new record.

On to my next problem. The execute screen immediately disappears so I cannot enter additional data into other fields. I can go to the Transaction database and see the data there, but I do not get the opportunity to finish my entry. I have tried several ways to solve my problem by adding to the original script:
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Debit", "Name" ) \
VALUES( \'5014\', \'1000\', \'KROGER\' );' );

After a half-dozen different approaches I tried this which worked to keep the form alive, but any additional entries were entered into the next-to-last record. The TransNo field auto-increments and I can’t move forward to that new record:

this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Debit", "Name" ) \
VALUES( \'5014\', \'1000\', \'KROGER\' );' );
this.records.updateRecord();
this.records.lastRecord();

I also tried:

this.records.lastRecord();
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Debit", "Name" ) \
VALUES( \'5014\', \'1000\', \'KROGER\' );' );
this.records.updateRecord();
this.records.lastRecord();
this.records.reloadRecords();

and

this.records.lastRecord();
this.records.reloadRecords();
this.dataSource.sqlExecute(
'INSERT INTO "Transactions" ( "Credit", "Debit", "Name" ) \
VALUES( \'5014\', \'1000\', \'KROGER\' );' );
this.records.updateRecord();
this.records.reloadRecords();
this.records.lastRecord();

For some reason I cannot get the last record to come up for modification.

The last problem is when I close the “Execute” panel the program adds another record filled with only the INSERT INTO data. The “Close” button has the following script: this.accept(); . How can I have the panel just close without making any changes to the database?

Joe Hall
_________________________________________________

This is a notification to let you know that Kevin has replied to the discussion on the site. You may find the snippet of the reply below:
The “Close” button has the following script: this.accept(); . How can I have the panel just close without making any changes to the database?



this.reject();


I have a form that adds records. Thus, Data Mode is set to Add. To close the form without adding any records I use the above code. It seems to work.

_________________________________________________
Sergey, Kevin,

The this.reject(); seems to work.

I have worked a bit more this afternoon with the Kroger scripts. I have discovered if I click the “Execute” button in the pop-up window used to enter the script things work differently than if I save the script and Execute from the Form.

If I Execute from the Form the next-to-last record shows up and the Form will not advance to the new record and any additional data entered fouls the next-to-last record. When I Close (using the this.reject(); script) I can find the true last record in the database, but none of the additional information shows - only the INSERT INTO data from the button.

However, if I click the “Execute” button in the pop-up window used to enter the script (Sceenshot#2); click OK to get out of the pop-up window(Sceenshot#2B); click the Save button in the top bar (Sceenshot#3); click Execute in the Form window (Sceenshot#3B); and click the Last Record button on the Form I find the true last record with the INSERT INTO information entered (Sceenshot#4). I can add additional data (Sceenshot#5) and click Update (Sceenshot#6) . Also on clicking Close I find the new record as the last record in the database (Sceenshot#7) I do not get a second record created. This works but it is a kluge. What is happening here?

Here is the sequence from the Input Form on Execute and click the Last Record button (Sceenshot#8). I click KROGER2 button (Sceenshot#8B) and there is no change to the screen. I cannot see the new record which would be TransNo 2192143. Clicking the Update button does nothing and trying to click the Last Record button does nothing. Any update or change affects TransNo 2192142 (Sceenshot#9) and updated (Sceenshot#10). If I hit Close and move to the database and hit refresh, I find record TransNo 219142 has been altered (see Sceenshot#7 above for the original data entered therein). Plus there is a new last record TransNo 219143 which never showed up on the Input Form and which I had no opportunity to modify (Sceenshot#11).

I have no idea what is happening.

Joe Hall

Wait. I was just getting ready to send this when Sergey sent me his reply. I will try to work through his suggestions tomorrow. It has been a long day and I have other duties to see to. You guys are being great. Thanks
Attachments (11)
Comment
There are no comments made yet.
Kevin Accepted Answer
Sergey,

Thank you for providing the pre-fill code! Before your post, I had tried to figure it out, but was unsuccessful.

> "If a LineEdit control is bound to a field"

Is this correct: It doesn't work in a TextEdit control bound to a field. (It didn't work here.)

Joe,

> "which will pre-fill certain fields"

If this was my database, I would go with your original idea, pre-fill, and not SQL. Pre-fill, review, add more data, review, and then add the record to the database.

Kevin
Comment
There are no comments made yet.


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

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories