1. Thomas J. Hall, III
  2. Valentina Studio
  3. 水, 7月 08 2020, 08:51 PM
  4.  メールで購読
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?
コメント
There are no comments made yet.
Thomas J. Hall, III 承諾済みの回答
Sergey,

That is slick. Thanks.

Your program is so well done I was sure you had some way of doing it.

Joe
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
Joe,

There are two drop-down lists in the top-right corner of the form editor: the first one is the datasource you created in project and the second is the table/view/query this form is bound to.
添付ファイル
コメント
There are no comments made yet.
Thomas J. Hall, III 承諾済みの回答
Kevin & Sergey

Another question. How can I successfully copy/paste forms from one project to another? Your Studio Pro website gave me the impression one could do so or could generate forms not attached to any particular database and then attach them later. There must be some procedure to enable that to happen, but I have not found it. This is why I am asking.

I was cleaning up my Valentina db folder yesterday deleting all the extra versions of the databases I had made to try to import CSV files without showing NULL. I inadvertently deleted the .vsp file for my Accounting project. I went to my original backup file still on my Mac and moved it over to my Linux computer. The copied .vsp file will not recognize any of my forms which were originally created with it and which I had copied to the Linux machine earlier (they worked with the .vsp file I deleted). The error message I am getting reads: “Transactions - Form::needUpdate:2: TypeError: Cannot read property 'position' of null.” I also updated the database that was the original datasource and I changed its name. I am unable to link the new database to the forms using it as their datasource. I don’t know if this is relevant, but my databases went from the fields being nullable; to their having “nullable” unchecked; and then back to being nullable when I found I could not properly import my CSV files without enabling null.

I did a check using another db and a new project. I opened the new project and duplicated an old db giving it a new name. I then copy/pasted a form from the old project which worked with the original db into the new project which had the duplicated db as a datasource. The copy/pasted form exhibited the same problems and had the same error message as my Accounting project. I had originally thought my changes in the nullable character of the fields might have played some part in my problems with my Accounting .vsp because the error message references “null”, but this particular database has not been subjected to any changes in nullable.

In both the above instances I later tried a halfway regeneration. I tried to re-designate the individual fields on the form to the current database. I opened a form in the editor/Palette section, right-clicked on a particular field, chose “select field”, and I got the error message “Need to select a datasource first”. As far as I could tell I had already connect the project and its forms to a database.

I realize I can simply regenerate the forms in the new project, but I suspect that is not needed.

Joe
コメント
There are no comments made yet.
Thomas J. Hall, III 承諾済みの回答
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
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
Joe,

Did your reference to “non-modal forms” refer to my request that Studio Pro be able to open more than one form at a time so that I can have additional data available for reference? If it is available in your beta version, should I attempt to use that version (remembering I am still a rank beginner with Studio Pro) or should I wait for its implementation in a coming version?


Yes, non-modal - means you can run form A, and from this form (for example, on button click) you can open form B and then, without closing form B, switch to form A.

Looks like this functionality needs more testing, I'll let you know when it will be completely ready.
コメント
There are no comments made yet.
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
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
Joe,

I have looked at all those “Class” programming items off the Palette page, but they do not seem applicable to me and I haven’t yet come upon anything explaining these type of commands - which I am guessing are Valentina specific. Yes?


Valentina forms are built on top of the Qt framework, so they are mostly Qt-specific.

LineEdit on the form is QLineEdit Qt class, so all its methods and properties are listed in Classes->qt->QLineEdit section of documentation
添付ファイル
コメント
There are no comments made yet.
Thomas J. Hall, III 承諾済みの回答
Kevin & Sergey

Yes, I am back again. Thanks for responding.

Kevin. I found the Import Wizard. I had been looking for a separate item like SQL Editor, Data Editor, etc.

Thanks too for the information on how to get the cursor to set upon a particular field. It works like a charm. I noticed when I entered this.Comments.setFocus(); into my button script I got a pop-up list with suggested ways to complete the script. Is this where you got the script? Is there a place I can go to find these great scripts you and Sergey have given me? I have looked at all those “Class” programming items off the Palette page, but they do not seem applicable to me and I haven’t yet come upon anything explaining these type of commands - which I am guessing are Valentina specific. Yes?

Sergey. I have been working this morning to scrub “NULL” from my database, but I have not been successful. I set up a dummy database and disabled Nullable. Then I tried to import my CSV files. I had repeated failures apparently caused by records with completely empty fields. I tried to fix this by duplicating the import file and inserting double spaces into each empty field in a record. That still did not work. So I went back and made the fields Nullable again and the imports went without a hitch. It looks like I will have to live with “NULL” showing in my databases until you come out with a way to hide it in one of the coming versions.

I will wait for the ability to format a displayed field in a database (Currency, decimal places, etc) and to be able to utilize Generated Columns (my Oil&Gas database has a number of calculated fields - Net Income; Total Costs; Total Production; etc).

Did your reference to “non-modal forms” refer to my request that Studio Pro be able to open more than one form at a time so that I can have additional data available for reference? If it is available in your beta version, should I attempt to use that version (remembering I am still a rank beginner with Studio Pro) or should I wait for its implementation in a coming version?

Joe
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
Joe,

Non-modal forms were already implemented. This feature is available in the beta version.
参照
  1. http://valentina-db.com/download/beta/10.5b20/lin_64/vstudio_x64_10_lin.deb
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
Hello Joe,

1. A. Is this still the best way to do it?

It is the only way, but I think we can add an option to hide <NULL> in the next version.

2. Is it possible yet to have Dollar amounts show as $###.## in the db

Not yet.

3. Is it possible yet to utilize the new ability of SQLite to have “Generated Columns” which are available in version 3.31 - ie. GENERATED ALWAYS AS (price * (1-discount) * (1+tax))? If so, how?


It is not fully supported yet. It is possible to execute queries to tables with generated columns (in forms and SQL Editor) but in the Schema Editor and Data Editor, they may not be shown at all.
コメント
There are no comments made yet.
Kevin 承諾済みの回答
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
コメント
There are no comments made yet.
Thomas J. Hall, III 承諾済みの回答
Sergey,

I have purchased my Linux version of Studio Pro. I am enjoying the upgrades from my free Mac version 9.7. I have been working on my input forms.

1. With my free version Studio I complained about not being able to get rid of the “NULL” entry showing up in my databases. I read the discussion at (https://www.valentina-db.com/en/discussions/7199-how-can-i-display-fields-with-value-as-empty-fields) where it was suggested I clear the records, disable NULL, and reimport the records. I have two questions:
A. Is this still the best way to do it?
B. Where is the Import Wizard? I have tried my best to find it, but have failed, so I cannot reinsert the records.

2. Is it possible yet to have Dollar amounts show as $###.## in the db

3. Is it possible yet to utilize the new ability of SQLite to have “Generated Columns” which are available in version 3.31 - ie. GENERATED ALWAYS AS (price * (1-discount) * (1+tax))? If so, how?

4. What is the script to move the cursor to a particular field in a form? For example, to set the value in a field I am using this script: records.getField (‘Name’).value = ‘KROGER’;. Would it be something like: records.gotoField (‘Name’)? This would be a nice time-saver. I have set up buttons to pre-enter data into a form and as the last action I would like to have the cursor end up at a particular field so I could just enter new data there.

Thanks.
Joe
コメント
There are no comments made yet.
Thomas J. Hall, III 承諾済みの回答
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
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
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.
コメント
There are no comments made yet.
Thomas J. Hall, III 承諾済みの回答
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
コメント
There are no comments made yet.
Thomas J. Hall, III 承諾済みの回答
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
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
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?
コメント
There are no comments made yet.
Thomas J. Hall, III 承諾済みの回答
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
添付ファイル
コメント
There are no comments made yet.
Thomas J. Hall, III 承諾済みの回答
#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
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
Joe,

Good questions. No, currently only DB formatted values are available.
It is in the Data Editor and Forms, right?
コメント
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.