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.
Thomas J. Hall, III Accepted Answer
Kevin

You are correct. My Clear button will not clear everything.
CLEAR record in EDIT and COMMIT Form
this.records.discardChanges();
this.records.updateRecord();
this.records.reloadRecords();
this.records.lastRecord();

I had tried it after clicking my “Kroger” button
this.Name.text = 'KROGER';
this.records.getField( 'Name' ).value = 'KROGER';
this.Debit.text = '1000';
this.records.getField( 'Debit' ).value = '1000';
this.Credit.text = '5014';
this.records.getField( 'Credit' ).value = '5014';
and those fields were cleared. But if I manually enter data into other fields, the button does not work.


I changed the Clear button to this.
this.records.setBlank();
this.records.updateRecord();
this.records.reloadRecords();
this.records.lastRecord();
It didn’t work.

I finally changed the Clear button to:
this.records.setBlank();
this.records.updateRecord();
It didn’t work either.

I guess I will have to do away with that button and manually change any fields that need it.

Concerning AUTO_INCREMENT, I may do away with it and manually increment or try to set up the procedure I use in FileMaker. There I have a separate table (Constants) holding my ID integers. I retrieve the latest one and paste it into the ID field when I open/add a new record. When I close/accept the record I copy the ID, increment it by 1, and paste it into the proper field in the Constants table. This has worked well because at times I will screw things up with a record(s) and I want to go back one or more ID numbers. It is very easy to simply open the Constants table and enter the ID number I want.

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

>2. How to set the AUTO_INCREMENT back one integer if I close or delete the record.

Is your current FileMaker database in SQLite or are you going to export / import the data?

If the latter, consider structuring the table you are going to import into so that the AUTO_INCREMENT field (must be an Integer) is the Primary Key (and thus the RowID). I did this for my MS Access databases and it has worked very well. I no longer think about auto increment.

Good info here: https://sqlite.org/autoinc.html#summary

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

>1. To figure out how to have the form either stay visible or immediately refresh after “accepting” the new record

You can delete the Accept the button. Your Update button accomplishes the same thing. Thus, the form will stay open, until you close it via the Close button.

My form is also Edit and Commit.

Based on my own tests over the last several days and your scripts:

Clear button:

I am assuming this is a kind of undo, clear the fields, either before Update or after.

Does it work for you? It doesn't work the way I thought it would work.

There is a pre-built script to null a field. Would need to be entered for each field. Copy, paste, copy, paste. Enter field names. You don't have many fields, so not a big deal.

Update button:

You only need this:

this.records.updateRecord();

Accept button:

You don't need this button. The Update button accomplishes the same thing.

accept() and reject() close the form. Do they have other "powers"? Under Signals, each can trigger "something" (one would have to code the "something"; maybe open another form if one clicks on a button with accept() but opens a completely different form when one clicks on a button with reject()). Maybe, they have other "powers", but that is the one that stands out to me.

Close button:

You probably know this. Using an Edit and Commit form ... if you enter data, don't move away from the current record, forget to click on the Update button and click on the Close button ... your data will be saved.

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

Using an EDIT AND COMMIT form, I have been successful in adding a new record, seeing the AUTO_INCREMENT value, using a button to pre-enter date, editing the values, updating the form, and accepting it. For me it works better than using the parent EDIT for and the child ADD form.

I may not need all 6 buttons and may cut one or two. I will also remove the "New" button which is a holdover form the parent/child actor tutorial.

Now I need
1. To figure out how to have the form either stay visible or immediately refresh after “accepting” the new record
2. How to set the AUTO_INCREMENT back one integer if I close or delete the record.

Thanks for all the hints which I incorporated.

Joe
Attachments (8)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 84
Thomas J. Hall, III Accepted Answer
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
Attachments (19)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 85
Sergey Pashkov Accepted Answer
Kevin,

No bad things, just an error message if a new record can't be inserted with all default values (e.g. some field is unique)

But we are free to pre-fill such a field

this.records.setBlank();
this.records.getField( 'field1' ).value = 'my_value';
r = this.records.addRecord();
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 86
Kevin Accepted Answer
Sergey,

So something like this:

this.records.setBlank();
r = this.records.addRecord();

>It may not work in all cases

What are the possible bad things that could occur?

>What database is used?

SQLite.

Thank you for your assistance,

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

I click on the button with the reload records and go to last record code. It doesn't work.

The only code I added is for reloading and moving to the last record after adding.
What database is used?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 88
Sergey Pashkov Accepted Answer
It may not work in all cases, but if there are default values for all fields - it should be ok.

addRecord() performs INSERT command and shifts the current position to inserted record.

Also, it is correct to call this.records.setBlank() before this.records.addRecord() to clean up any changes to the current record.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 89
Kevin Accepted Answer
Testing / modifying Sergey's suggestion ...

If you want to add a record directly to your "edit and commit" form or "edit" form (Joe, you should review the differences; one may be more applicable to what you want than the other), create a button with the following:

r = this.records.addRecord();

That is it. It takes you directly to the new record. Begin data entry.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 90
Kevin Accepted Answer
Hi Sergey,

I tried the code in your video and it didn't work here. Is there not related code to what we are trying to achieve under another Signal?

In an attempt to try to figure this out (before your video), I placed a button on my "edit and commit" form. The button has this code:

this.records.reloadRecords();
this.records.lastRecord()

I am on my "edit and commit" form. I click on the button that opens my "add" form. I enter data. Prefill some fields. Edit. Review. Looks good. I click on the Add button. The "add" form closes and I am back at the "edit and commit" form. I click on the button with the reload records and go to last record code. It doesn't work. In other words, the latest record is not visible. It is of course in the table, because the "add" form works.

If I close out the "edit and commit" form and then open it via Execute. The button works.

Clearly, I am missing something. A part of me thinks the form, itself, needs to be refreshed / repainted / reloaded with the reloaded records.

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

Thanks for the new addRecord script. I will try it tomorrow morning when I am fresh.

Concerning th Dropbox video, I do not have Dropbox. I downloaded a zip file of what you sent and from what I can tell it is a copy of your forms_tutorials.vsp. If so, I already have that file and have watched and studied it several times. That is how I set up my parent Transactions - Form form and my child Add form. I mimiced the actor forms in that .vsp.

I am not quite sure how I could video my FileMaker input working. Let me think on it overnight to see if I come up with anything. I might have to go screenshot to screenshot. You must understand. I am an old fart (72). I have an old flip phone and it only calls and answers - no texting. So I cannot whip out my smartphone and make a video. I can get around a computer fairly well. I ran a Sun unix machine in the late 80's before moving to Apple when Apple moved to a unix core for their OSX. I will try to see if there is some application I can use on my computer to make a video record of FileMaker. If you know of something, let me know.

We are making progress

Joe
Comment
The default QuickTime Player can make a video, "File->New screen recording" in the app menu.
  1. Sergey Pashkov
  2. 1 year ago
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 92
Sergey Pashkov Accepted Answer
In the current implementation, we use separate forms to add and edit.
Usually, the set of fields for existing and a new record is different.
For example, there is no Transaction ID for a new transaction yet, so no need to ask to input it.

Maybe we'll add a combined Add/Edit mode in the future.

I uploaded a short video on how two forms may interact:
https://www.dropbox.com/s/7swacs06sdwbz61/show_add_form.mp4?dl=0

But if it is necessary to use the same form - it is still possible with some manual JavaScript/SQL.
Joe, could you please add a video of how your FileMaker form works both for adding and editing (it is not very clear from static screenshots)? Maybe I can make an example.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 93
Sergey Pashkov Accepted Answer

this.records.updateRecord();
this.records.reloadRecords();
this.records.lastRecord();
this.accept();


But there is no code which adds a new record.

That's how a wizard generates code to add a new record:

r = this.records.addRecord();
if( r instanceof Error )
QMessageBox.critical( 'Add Record', r.message );
else
this.accept();
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 94
Thomas J. Hall, III Accepted Answer
Kevin,

You were correct about numeric and integer fields. See Sergey's response. I am using an SQLite database.

My experience with the new record differs from yours. If I close the Add form with a
this.records.updateRecord();
this.records.reloadRecords();
this.records.lastRecord();
this.accept();
When I reopen the Transactions parent form and update it I do not find a new record nor do I find one in the database. It is like it was never there. Which I guess is true. I never saw it.

If however, I open the Add form by itself and click the Add button and then close with the Close button I do find the new record in my database (it even includes the data inserted by the Kroger button) and the new record shows in the Transactions parent form.

Getting closer, but my question now is why do I have to use an Add form? Why can't I do the same thing with an "add" button in the main Transactions form? And why can't an update show what I have entered, allow me to modify it, and then save/commit all the changes?

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

No, it is necessary to assign a string to 'text' property of LineEdit control, e.g.:
this.Amount.text = '5.05'

A table field accepts any value type, so assigning numeric value should be perfectly fine:
this.records.getField( 'Amount' ).value = 5.05;
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 96
Kevin Accepted Answer
Joe,

>if I want to use this method to enter data into a numeric field ('Amount') or an integer field ('TransNo') do I merely substitute "numeric" or "integer" for "text' in the this.Name.text = 'Kroger' statement.?

I am almost sure the answer is no. Wait for Sergey to be sure. Is your database SQLite?

>Still not seeing the new record in the Add form

You won't. Basically, it adds the record and closes.

>the new record did not show up in the database

It should be there ... however, seeing it on the fly as one closes and opens forms ... you tried and I tried without luck.

If you close all forms manually and Execute your "edit and commit" form you will see the record you added from your "add" form. Not ideal, but still learning. At least you can verify the "add" form entry.

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

It works. Hooray.

I modified my Transactions - Form (Screenshot#1) to Edit and Commit from Edit to match what you did. I entered your three fields data into one of my buttons, saved the changes, clicked my way to the Add Form, and clicked the button. It was like a dream to see the data appear in the correct fields. Now I see how to fill in the script Sergey sent me.
this.field_name_control.text = 'my_value';
this.records.getField( 'field_name' ).value = 'my_value';
I could not figure it out on my own. I am getting closer.

As an aside, if I want to use this method to enter data into a numeric field ('Amount') or an integer field ('TransNo') do I merely substitute "numeric" or "integer" for "text' in the this.Name.text = 'Kroger' statement.?

I was getting ready to point out I was still not seeing the new/added record in the Add Form nor was it showing up after closing the Add form. Then Kevin posted his question/explanation to Sergey.

I stopped and modified my forms in accordance with Kevin's suggestions.
1. Modified my "New" button to Execute the "edit and commit" form, click on a button that closes the "edit and commit" form and opens the "add" form.
2. Changed the script in my "Update' button on Transactions - Form to include Kevin's text
this.records.reloadRecords();
records = this.table.getTableCursor( this.uuid );
records.lastRecord();

That did not work to enable me to see or to save the changes and they did not show up on the database. I first changed the script on the Close button on my Add form from this.reject(); to this.accept();, but that did not work. I got an error message: Transactions - Add::accepted:2: SyntaxError: Expected token `identifier' at the bottom of my Transactions - Form.

So I amended the script to:
this.records.updateRecord();
this.records.reloadRecords();
this.records.lastRecord();
this.accept();
That did no good. Still not seeing the new record in the Add form and the new record did not show up in the database.

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

What are the best practices regarding opening and closing forms? Specifically, keep forms open? Open and close? Which order?

In my simple example, I open the "add" form from the "edit and commit" form.

In MS Access, I would close out the "edit and commit form". The order: open the "add" form, close the "edit and commit" form.

In Valentina 9, at least, the opposite order seems to work best.

After I click on the Add button in my "add" form, the form automatically closes. How best to open the "edit and commit" form after an add? I added the template script to open the "edit and commit" form. That seems to work - sometimes. Sometimes, it throws an error.

To recap: I Execute the "edit and commit" form, click on a button that closes the "edit and commit" form and opens the "add" form.

For the "edit and commit" form, I have the following under QObject - created:

this.records.reloadRecords();

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

The assumption being upon opening (created), the records would be reloaded and I would be looking at the last record (the one I just added).

The above works if the form is opened via Execute.

The above doesn't work when the form is opened from the "add" form via the Add button (when it doesn't throw an error).

Thanks,

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

Thank you for the TextEdit example. Works.

In one of my forms I used a TextEdit control for a comments field.

Kevin
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 100


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