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

FileMaker - Accounting Database

Sergey,

You asked me for a video of how my FileMaker database works. This is what I came up with last night. I will lead you through a series of .png’s with explanations.

The database is a bare-bones setup because of its legacy. Early computers had very limited memory and processing capabilities. I have ported basically the same setup through numerous computers and operating systems. Throughout all these data migrations the database has had the same 9 fields and there are now 71,000 records in it stretching back to 1988. The database has its beginnings in the late 80’s with a HyperCard stack on a MacSE. That is where the current layout setup was made. It was carried over when I moved to the 4D database (great database) a few years later. I went into business for myself in 1987 and soon upgraded to a Sun unix workstation and had to migrate to Informix. Finally, when lightening struck almost on my house and literally fried my Sun workstation I went back to Apple (by this time with a unix kernal) and started using FileMaker.

I have used FileMaker for probably 25 years and have probably not modified the current setup in the last 10 years. This is why I am not conversant with JavaScript or SQL. There was no need.

OK. Here is my setup.

1. The first .png - ManageDatabase_Relationships is a diagram of the database. What I am interested in here are the table in the upper left of the diagram - Transactions, Accounts, & Constants. Those are the core of the database. The other tables are add-on to take care of my business activities over the years. I may or may not put them into the new setup. I am not really active in them anymore.

2. The ManageDatabase_Tables and ManageDatabse_Fields show the tables for the entire database and the fields for the Transactions table.

3. When I open the database the InitialScreen and the TransactionsView forms appear (note to keep the size down my working database is limited to the current year. Prior years are archived in a big Transactions_All database).

4. To get to my input screen I click the “Input” button in the Initial Screen. This action adds Input to my desktop. It opens with the first record showing.


5. Clicking the red “Open’ button in the upper left corner changes the form to Input_after_Open which is a clear screen ready-to-go.
First, some explanation of the form.

The key portion of the form and the legacy part from HyperCard is the blue fields section - Input_KeyPortion which includes the field, the operating buttons (Open; Enter; Cancel; and Clear)

The upper right buttons Input_ToOpenNewForms take me to other form/layouts.

The buttons to the right Input_ButtonsToPre_EnterData are the ones to pre-enter oft used data. Which is what all this dialog is about - how to make it work with Valentina

The lower group of buttons concern business and investment activities Input_Invesment&BusinessButtons.

6. I should probably digress here and deal with the buttons. They are added to a layout from a menu bar and drug to where you want them positioned (I probably don’t need to get into the weeds on this, but will if you want me to). After positioning an empty button one opens the ButtonSetUpPanel and selects the script to attach to the button ManageScriptsPanel.

7. The key buttons on the Input_after_Open screen are the operating buttons (Open; Enter; Cancel; and Clear). Here are what they do.

Open - red button in the upper left corner. It executes a number of nested scripts (Script_TransInitialScreen_Btn_Open and Script_TransView_Btn_Open_Initial). The scripts go out to the Constants table and retrieve the current TransNo (This is the table ID field and is unique and incremented, but it is not auto-incremented. When I started I was not able to auto-increment. The scripts retrieve the current TransNo to open and another script increments it by 1 on closing and places the new number into the Constants table. In practice this has turned out to be good, because if a series of new records is screwed up, I can go back into the Constants table and enter a new TransNo with little effort). The script also makes sure each field is clear and it positions the cursor into the date field ready for entry.

Enter - yellow button ( Script_Trans_Script_InputEnter) which saves the record; increments the TransNo and sends it to Constants; calls a new record; goes to Constants and gets the TransNo; clears and sets up the form for the new record (Input_after_Open)

Cancel - yellow button (Script_Trans_Script_InputCancel ) this button deletes the current (usually new) record. It generates a warning (Script_Trans_Script_InputCancel_Warning ). This button is the one which effectively get you out of the input mode. It results in putting you back to the last record entered.

Clear - yellow button (Script_Trans_Script_InputClear ) is seldom used it clears all the date in the fields in case you have made a mistake entering (clicked the wrong pre-enter button to the right) and just want to get back to an empty record while using the same TransNo.

8. The Transactions - View screen ( TransactionsView) has several set of buttons.

Brown upper left. Move you around the form (first, last, next, previous) and it will reshow all the records if a find(fliter) has truncated the records.

Orange middle. Opens other form/layouts

Green left - can be used to step through a field in number of records pasting data from copy - very helpful and timesaving. The green “Find” button opens the filtering routine.

That is about it. Over the years I have added features which automate frequent tasks. If you have any suggestions on how I can carry over some of them with Valentina I would welcome them.

Let me know if you have any questions.

Joe

Joe,

I can answer a few of these.

> B. Where is the Import Wizard? I have tried my best to find it, but have failed, so I cannot reinsert the records.

Go to the Databases section (not the Projects section). Open database. Locate table. Right click on table. Import from CSV.

> 4. What is the script to move the cursor to a particular field in a form?

this.Comments.setFocus();

In the example above Comments is the name of the field.

Kevin

Joe, I am somewhat familiar with JavaScript (previous programs, and I currently use another program that is built on the Qt framework that allows scripts). I trip up in the syntax (maybe not the correct word). What is this "thing" called? What goes first? Thus, the general JavaScript tutorials one can find on the web are applicable here. The specific Qt "Class" info ... very important because it shows you what is possible. How to use it in code ... another story, of course. But once you know how to use something, using any of the other similar somethings is nearly identical. Kevin

Kevin & Sergey

I have been reopening the Projects I started using the Serial option Rusian let me use for a month. This latest version of Studio Pro is superb. I have mainly been exploring my most used databases using Data Editor. It gives me great flexibility and the split-screen linking will let me do some things I had to write some elaborate scripts to accomplish in Filemaker. I am finding I can do almost everything I need to from Data Editor. At this point it appears I will need to have Forms strictly to input new data. You two gave me most of what I needed to set up my forms, so I am probably 80% of the way to being up and running using Studio Pro instead of Filemaker.

I looked at the Qt->QLineEdit section of the documentation. It is still an opaque area for me. I probably need to take Kevin’s advice and do some JavaScript tutorials. I am clueless on that subject and I do not really understand how the idea of Classes works. It remains to be seen if I will invest a significant amount of time to get a working knowledge of JavaScript. There are a couple of big hurdles to overcome. I am 72 and do not have the mental energy I once had and you two have graciously given me the scripting elements I have needed.

I will now hunker down and clean up my current projects.

Joe

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=valentinaroducts: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:

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

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\' );' );

Syntax highlighting helps us to catch such problems, please compare to the attached screenshot

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

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.

Thank you, Kevin, right, this.reject(); is the correct code for Close button.