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
Sergey, Kevin, & Rusian

Thanks guys for helping me get my feet on the ground with Valentina. I am at the end of the trial period Rusian gave me to see if I could find a way to use Valentina’s Forms to replicate my old FileMaker databases. With your assistance, I can say it was a success. When I can get my other transition issues for my move from OSX to Linux sorted out, I will sign up. It may be a month or so, but I will be sure to contact you again if I need help.

Joe
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Joe,

Is a multi-window capability in the planning or developement stage? If so, how soon do you anticipate it being available?


It's on the list to implement, I can't tell exactly, but it shouldn't take too long.
Comment
There are no comments made yet.
Thomas J. Hall, III Accepted Answer
Sergey,

During the night I mentally replayed the way I use my databases. I seldom use one without at least two, but usually 3-4 separate windows open for the same database table.

When I am entering accounting transactions I will have a form based input window (the one I sent you a screenshot of with the pre-filling buttons) but in addition I will have at least 2 tables of my transactions open - one will be sorted in TransNo order which will let me see the new record as it is entered; another will be in sorted alphabetically by Name which I can use to copy Name or Comment data to paste into the Input form; and if I am entering income data there will be a third filtered table to which I can refer for information or from which I can copy.

My Contacts database is much more detailed than the commercial contacts or address book programs. I have the usual name, address, phone, email, etc., but I also have extensive notes of intereactions with dates, people delt with (for businesses), subjects discussed, actions taken or promised, etc.(I even have a Valentina record). If I am on the phone or I am emailing a person or business I typically have a table view open and I may have more than 3 separate forms open to refer to. That ability to quickly see data is crucial for me.

When I did my mental replay of how I use my data, I see that it is absolutely essential that a Valentina database would allow me to have multiple windows open for the same table and maybe even open for different tables or databases. Last night I realized that the lack of this ability might just veto my being able to use Valentina. I truly like your product and would like to use it. Plus, the people I have interacted with at Valentina have been superb, but it has to be able to perform certain tasks for me to use it.

Is a multi-window capability in the planning or developement stage? If so, how soon do you anticipate it being available?

Joe
Comment
There are no comments made yet.
Thomas J. Hall, III Accepted Answer
Sergey,

I am pleased to hear that formating and navigation are pending.

I hope I piqued your interest in aggregation functions. Most of my table forms have several of them. I use them all the time. The process is very simple in FileMaker. One creates a "summary" field (which can be placed anywhere you like on the layout). The action within the field is selected from a menu, in the case of my Transactions Layout, one field has the script "=Count of TransNo" and the other script is "=Total of Amount".

As to FileMaker, yes I keep several separate forms open at one time both for reference and for possible data modification of an existing record in a different table. Again, very helpful. I hope Valentina will implement it.

Joe
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Joe,

I am inclined to wait and hope Valentina will give me the option down the road. As I said above, it is not a deal breaker. It is an esthetic thing.


Yes, it is absolutely necessary to have formatting (as we have it in printed reports).

I have about given up on using Form Editor Table View to do anything. I can’t get the navigation buttons (next record, last record, etc.) to take me to any record other than the first one.


Changes for navigation be available in the next version.

Is it possible in Data Editor to calculate (in the Filter row at top or a separate field) the sum of a field - ie.

No, but it looks interesting to apply aggregation functions

Also is it possible to have more than one form open at a time and to move back and forth between them?
.
No, non-modal forms are not fully functional. You used it in FileMaker, right?
Comment
There are no comments made yet.
Thomas J. Hall, III Accepted Answer
Sergey & Kevin


I have been experimenting with horizontal/spreadsheet views and forms. I have about given up on using Form Editor Table View to do anything. I can’t get the navigation buttons (next record, last record, etc.) to take me to any record other than the first one. The Selection buttons I added to the Vertical View forms from Lesson 6 will not work. I can’t see any advantage of using that version of Forms over using the database with Data Editor.

I am very happy with Data Editor and the ways I can filter the date and link different table is awesome. I am beginning to think all I will need are a few Vertical View Forms for entering data and will use Data Editor for viewing and manipulate the records.

Is it possible in Data Editor to calculate (in the Filter row at top or a separate field) the sum of a field - ie. Amount or the number of records in a filtered return - ie. #of TransNo returned? These are very handy figures to me. I use them a lot. See the attached screenshot of my Transactions - View layout.

Also is it possible to have more than one form open at a time and to move back and forth between them? For example when entering transactions involving dividends it would be helpful to have a Securities List, a Securities Return, and an Accounts forms also open with the ability to interact with them. As things stand now, it looks like I have to Close one form before I can open another.

Joe
Attachments (1)
Comment
There are no comments made yet.
Thomas J. Hall, III Accepted Answer
#106 - TJH

Yes Sergey, I am referring to Data Editor and to Forms. It is a matter of esthetics. I would like to see my amounts in a “Currency” format and my dates in the format I am used to seeing.
Reading between the lines with you statement that “currently” I am not able to reformat as I would like to, do I detect that the capability to do so is either in the works or planned? I hope so. If it isn’t, please add it to a list of requested features.

Kevin,
I understand your statement that I can simply enter the last two digits when entering data. My problem is I have almost 71,000 transaction records and I can’t go back and manually change them. I might be able to go back to FileMaker and change the field format to have each entry recorded as “.00”. I know all of the fields were formatted as Currency with 2 decimal places and that they came into the .db file from the CSV file truncated.

I am inclined to wait and hope Valentina will give me the option down the road. As I said above, it is not a deal breaker. It is an esthetic thing.

Joe
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Joe,

Good questions. No, currently only DB formatted values are available.
It is in the Data Editor and Forms, right?
Comment
There are no comments made yet.
Kevin Accepted Answer
>1. Can I format a field (in my case - Amount) to show Currency with two digits to the right of the decimal?

I am assuming you are entering 100 and want to see 100.00.

If one enters 100.00, it will display 100.00.

You can add this script to the Amount's field's Signals' editingFinished:


records = this.table.getTableCursor( this.uuid )
field = records.getField( 'Amount' )
var curvalue = field.value

if (curvalue.indexOf(".") == -1) {
this.Amount.text = curvalue + ".00"
field.value = curvalue + ".00"
}



It seems to work, but it may be incomplete. What it is doing is changing the value you entered, not changing the display of the value you entered. That is a huge difference. If you want 100 in the database, but only want to see 100.00, I don't think it can be done. The above does not work if you enter data outside of the specific form. It won't work if you are just viewing records and see a previous field (before you added the code) without the decimal. You need to enter the field.

2. Can I enter Dates as “7/20/20” (preferable) ...

You can enter dates any way you like, really. I come from Access. Based on what I read about SQLite ... for me ... changing dates over to yyyy-mm-dd will make my life a lot easier. Thus, I changed all the dates over, before importing; and now enter dates in that format.
Comment
There are no comments made yet.
Thomas J. Hall, III Accepted Answer
I am uncovering more possibilities as I explore Data Editor. It looks like I will have an easy time incorporating some of the income reporting I had set up in FileMaker with Valentina’s linking; Related Table Editor Mode; and the ability to embed child forms within parent forms. I will have all the tools I need.

I have a couple of questions about formatting.
1. Can I format a field (in my case - Amount) to show Currency with two digits to the right of the decimal?

2. Can I enter Dates as “7/20/20” (preferable) or as “7/20/2020”and have them recorded correctly and also have them display as “7/20/2020”? My imported CSV files all read in the “7/20/2020” format. I am aware that SQLite saves dates as yyyymmdd, but I don’t want to have to use that.
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
It can be opened from the Schema Editor.
Just double-click a table.
Also, when you select a table - Data Editor button becomes enabled, additionally "Open Data Editor" button is shown.
Attachments (1)
Comment
There are no comments made yet.
Thomas J. Hall, III Accepted Answer
OK. I have to ask for help again. I started reading back through Valentina documentation. I started out with Valentina Studio Features (http://www.valentina-db.com/docs/dokuwiki/v9/doku.php?id=valentina:features:vstudio:vstudio). I was intrigued with Data Editor and what it could do with a database table. I moved on to look at Data Editor Help (roducts:vstudio:help:data_editor:data_editor" target="_blank" rel="nofollow">http://www.valentina-db.com/docs/dokuwiki/v9/doku.php?id=valentina:products:vstudio:help:data_editor:data_editor). That looks like exactly what I would like to use to set up top filter boxes, sorting, etc. My problem is I cannot find a way to open Data Editor and I have spent close to an hour trying everything I could think of - Schema Editor; going to the open database to the project; going to one of my table forms for the project; Toolbar; etc. Nowhere could I get to a point that I could go into the Toolbar and find Data Editor clickable.

Valentina Studio Features and Data Editor Help both are great for tell me what Data Editor can do, but they fail to tell me how I can open and use it. Can you tell me?
Comment
There are no comments made yet.
Kevin Accepted Answer
Joe,

You can copy your "working" form that contains the buttons and scripts.

Open the copy and create the layout you want.

Kevin
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Re # 97

Joe,

Now that I can set up 20-40 buttons, is there some way I can copy a button with its attached script and paste it?


No, unfortunately, in 9.7, there is no copy/paste for form objects, it was added later.
Comment
There are no comments made yet.
Thomas J. Hall, III Accepted Answer
Re #96

Kevin,

Yes, I learned the trick of prototyping years ago. I currently have two extra copies of the forms I am trying to make and to modify. I keep trying different things until something works or until you or Sergey give me an answer.
Comment
There are no comments made yet.
Thomas J. Hall, III Accepted Answer
Re: #95

Sergey,

Thanks it worked.

You realize understanding how to word and work with these objects and scripts is like learning a foreign language for me. I am doing what my son (who speaks 3 languages) suggested. He told me when learning a new language just plunge in and don't be afraid of making mistakes. People who speak the language (like you and Kevin) will work with you and help you get fluent. You two are.

Now that I can set up 20-40 buttons, is there some way I can copy a button with its attached script and paste it?
Comment
There are no comments made yet.
Kevin Accepted Answer
Joe,

I saw you were on 9, so everything I have been doing is on 9.

With forms ... i usually create a test form, even if I have one that is working well, to practice layouts. You can delete whatever you drag and drop, plus it is a test.

Kevin
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
1. Switch to Inspector tab
2. Right-click the FormLayout and in the context menu select Wrap To->BoxLayout Horizontal (wrap_to_hbox.png)
3. Switch to Palette tab
4. Drag BoxLayout - Vertical from the palette and insert it at the right of the fields (insert_vbox_1.png)
4. Place another BoxLayout - Vertical to the right of just inserted layout (insert_vbox_2.png)
5. Add button to the first layout (insert_button_1.png)
6. Add button to the second layout (insert_button_2.png)
...
Add the last button (insert_button_10.png)
Attachments (6)
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Joe,

Actually, it is possible in 9.7, I'll add a few screenshots on how to do it
Comment
There are no comments made yet.
Thomas J. Hall, III Accepted Answer
Kevin

Re: #88 Box Layout - Vertical

That is exactly what I want to do. I tried it this morning without success. I cannot place them to the right of the From Layout. Everything has to be stacked one above the other. The ability to place them as you have in your example is, I am sure, a feature which was implemented in a version after the 9.7 I am using. However, knowing it is possible is all I need. It means I can do it when I get a current version. Thanks

Joe
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