Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
(OP)
I have a form with a search-list (incremental search).
Works fine.
I only have to type 1 to 3 letters - and I'm already on the right line - and with one click a browse window opens and shows me detailed results from a table.
This is of course easier to represent than to a GRID on the form.
You can also navigate in a browse window without writing any code oder filling headers etc.
My question:
Can a reduced BROWSE window be shown on the form at the same time if there is still enough space on the form?
Or - can a BROWSE windows can only be displayed on their own - but this means that the BROWSE window must first be closed manually in order to get back to the search list on the form.
I hope this question is not too stupid.
Up to now I always tryed to avoid GRIDS - but may be that this "fear" is exaggerated
I am very interested in your opinion.
Klaus
Peace worldwide - it starts here...
Works fine.
I only have to type 1 to 3 letters - and I'm already on the right line - and with one click a browse window opens and shows me detailed results from a table.
This is of course easier to represent than to a GRID on the form.
You can also navigate in a browse window without writing any code oder filling headers etc.
My question:
Can a reduced BROWSE window be shown on the form at the same time if there is still enough space on the form?
Or - can a BROWSE windows can only be displayed on their own - but this means that the BROWSE window must first be closed manually in order to get back to the search list on the form.
I hope this question is not too stupid.
Up to now I always tryed to avoid GRIDS - but may be that this "fear" is exaggerated
I am very interested in your opinion.
Klaus
Peace worldwide - it starts here...
Talk To Other Members
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Use a grid.
Tamar
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
I suggest you read the help file about the BROWSE and the DEFINE WINDOW command
AFAIK - No, it can be only shown in the main VFP Screen, can overlap the form, but CANNOT be shown IN the form
No, you may program it in order to CLOSE when it looses focus or after a certain TIMEOUT you specify.
Well that's your decision but you may want to rethink it - GRIDS are in may ways more powerful than BROWSE
Below a sketch of code how you could address this issue.
CODE -->
PUBLIC oForm oForm = CREATEOBJECT("clssearch") oForm.visible = .t. Read EVENTS CLOSE ALL CLEAR ALL RETURN *********** DEFINE CLASS clssearch AS form Top = 12 Left = 12 Height = 474 Width = 288 MinWidth = This.Width Caption = "Incremental Search" WindowState = 0 AutoCenter = .T. Name = "clssearch" ADD OBJECT cmdBrowse as CommandButton WITH ; Top = 12, Left = 12, Height = 24, Autosize = .T., Caption = "Open Browse Window" PROCEDURE cmdBrowse.Click() lcWord = "" DEFINE WINDOW wBrowse FROM 1, 1 TO 42, 90 ; FONT "Arial", 8 ; CLOSE ; FLOAT ; GROW SELECT csrWords lcWord = ThisForm.List1.List(ThisForm.List1.ListIndex) = INDEXSEEK(lcWord, .T., "csrWords") BROWSE WINDOW wBrowse ; FIELDS cWord :H = "Word", iValOne :H = "Value One", iValTwo :H = "Value Two" ; NOEDIT ; TIMEOUT 25 RELEASE WINDOW wBrowse ENDPROC ADD OBJECT list1 AS listbox WITH ; Height = 408 - 12, ; Left = 12, ; Sorted = .T., ; Top = 60, ; Width = 264, ; Name = "List1", ; Anchor = 15, ; IncrementalSearch = .T. PROCEDURE Load() CREATE CURSOR csrWords ( cWord C(35), iValOne I, iValTwo I) INDEX on cWord TAG tagWord SET ORDER to tagWord ENDPROC PROCEDURE Init LOCAL lnI, lnY, lnWordLength, lcItem, lnUpper, lLower, liAscii lnUpper = 90 &&ASCII lnLower = 65 &&ASCII FOR lnI = 1 to 100 lcItem = "" lnWordLength = INT((35) * RAND()) + 1 FOR lnY = 1 TO lnWordLength lcItem = lcItem + CHR(INT((lnUpper - lnLower + 1) * RAND( )) + lnLower) ENDFOR ThisForm.List1.AddItem(lcItem) INSERT INTO csrWords VALUES (lcItem, RAND() * 5000, RAND() * 1250) NEXT ENDPROC PROCEDURE Destroy() ThisForm.Release() CLEAR Events ENDPROC *!* PROCEDURE Activate *!* thisform.text1.setfocus() *!* ENDPROC *!* PROCEDURE list1.interactivechange *!* thisform.text1.value = this.value *!* thisform.text1.refresh() *!* ENDPROC *!* *!* PROCEDURE text1.interactivechange *!* LOCAL nCnt, sSearchFor, nLen *!* sSearchFor = ALLTRIM(this.value) *!* nLen = LEN(sSearchFor) *!* FOR nCnt = 1 TO ThisForm.List1.ListCount *!* IF AT(sSearchFor, ALLTRIM(ThisForm.List1.List(nCnt))) > 0 *!* Thisform.List1.Selected(nCnt) = .t. *!* thisform.List1.refresh() *!* EXIT &&Found one *!* ENDIF *!* ENDFOR *!* ENDPROC ENDDEFINE **********hth
MarK
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Chriss
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
My answer is Yes
But I am willing to relearn and experience the benefits of a Grid
Klaus
Peace worldwide - it starts here...
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
If you know the structure and the intent (business object) go for a grid
If you don't know the structure, a browse is good.
Ten points to T!
Regards
ing
Griff
Keep
I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.
There is no place like G28 X0 Y0 Z0
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Is it that you need to set so many grid properties and the columns to get the same? Actually a grid you set to rowsourcetype alias and open up an alias automatically becomes a browse of that alias, without further ado. And a FOR clause is just a SET FILTER of that alias..
There's more to browse, but in the end it is a grid, really.
Do your favorite/most complex BROWSE command, but add NAME browsegrid NOWAIT, and then do
browsegrid.saveasclass('mygrids.vcx','thisgrid')
Open the class in the class designer, use the properties window and set it to "nondefault properties only" and you see which specialties (if any) of the grid and its columns are used. On the grid level mainly you have a column count specific to the fieldcount of the workarea you browsed. And in all the columns you have the controlsource of a field. That's mainly all there is to it.
PS: As I see Mikes adice, I want to add that this method(saveasclass) has pros and cons in comparison with Mikes suggestions. First, saveasclass is not special, it's available for any class/object and I pointed it out as it enables you to inspect what the browse command you used actually set up as a grid and at the same time to show the browse actually is a grid. What's not saved - as it's not actually properties of the grid - is which DBFs are open in which workareas, so to use such a grid class later, you still have to open the table first. Mike's way to drag a table onto a form - especially in the case you drag it from the data environment - ensures that you have that DBF open, when you run the form. As always you can also get help to configure a pure empty grid you put on a form from the form controls toolbar, by a right click on it and using the builder. So there are many ways to get to a populated grid.
SaveAsClass is a simple way to "conserve" an object and its current state, too. So one advantage of the saveasclass method is that you can inspect how you get to the same grid as the browse command you used and more generally how a control or any object is set at the time you save it as a class, so it's also a tool for debugging and inspection of a state something was in at the time of saving it.
Chriss
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Keep it simple!
Below a sample how to combine a GRID with INCREMENTAL search and COMPUTED columns.
CODE -->
PUBLIC oForm oForm = CREATEOBJECT("clssearch") oForm.Show() Read EVENTS CLOSE ALL CLEAR ALL RETURN *********** DEFINE CLASS clssearch AS form Top = 12 Left = 12 Height = 474 Width = 930 MinWidth = This.Width Caption = "Incremental Search" WindowState = 0 AutoCenter = .T. Name = "clssearch" Themes = .F. ADD OBJECT lblName as Label WITH ; Left = 12, Top = 12, Caption = "Name to search", FontBold = .T. ADD OBJECT txtBox as TextBox WITH ; Left = 12, Top = 36, Format = "!" PROCEDURE txtBox.GotFocus() This.Value = "" ENDPROC PROCEDURE txtBox.InteractiveChange LOCAL lcSearch lcSearch = ALLTRIM(This.Value) = INDEXSEEK(lcSearch, .T., "csrWords") ThisForm.grdValues.Refresh() ENDPROC ADD OBJECT grdValues AS Grid WITH ; Height = 408 - 24, ; Width = 930 - 24, ; Left = 12, ; Top = 72, ; Anchor = 15, ; ColumnCount = -1, ; RowSourceType = 2, ; RowSource = "csrWords" PROCEDURE grdValues.Init() WITH This .SetAll("FontBold", .T., "Header") .SetAll("BackColor", RGB(0, 250, 250), "Header") ENDWITH WITH This.Column1 .Width = 246 .Header1.Caption = "Name" ENDWITH WITH This.Column2 .Width = 150 .Header1.Caption = "Value One" ENDWITH WITH This.Column3 .Width = 150 .Header1.Caption = "Value Two" ENDWITH WITH This.Column4 .Width = 150 .Header1.Caption = "VO + VT" ENDWITH WITH This.Column5 .Width = 150 .Header1.Caption = "VO - VT" ENDWITH ENDPROC PROCEDURE Load() LOCAL lnI, lnY, lnWordLength, lcItem, lnUpper, lnLower lnUpper = 90 &&ASCII (Z) lnLower = 65 &&ASCII (A) CREATE CURSOR csrWords ( cWord C(35), iValOne I, iValTwo I, iValOT I, iValTO I) INDEX on cWord TAG tagWord SET ORDER to tagWord FOR lnI = 1 to 1500 lcItem = "" lnWordLength = INT(35 * RAND()) + 1 FOR lnY = 1 TO lnWordLength lcItem = lcItem + CHR(INT((lnUpper - lnLower + 1) * RAND()) + lnLower) ENDFOR INSERT INTO csrWords VALUES (lcItem, RAND() * 5000, RAND() * 1250, 0, 0) UPDATE csrWords SET iValOT = iValOne + iValTwo, iValTO = iValOne - iValTwo ENDFOR ENDPROC PROCEDURE Destroy() ThisForm.Release() CLEAR Events ENDPROC ENDDEFINE **********RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
You should definitely take the trouble to learn to use a grid. It gives you everything a Browse gives, plus a lot more. And once you have understood how to use it, it is not at all difficult to program. Browse is useful in the development environment, but the only reason to use it within an application is for backward compatibility with Foxpro 2.x and earlier.
Griff makes the point that you should use Browse If you don't know the structure of the table. But that works for a grid as well.
Deciding to use a grid is a bit like deciding to learn object-oriented programming. You can manage perfectly well without it, but you are not really getting the full benefit of VFP.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Tamar is right. 10 points to her.
Grid is part of the form : if form gets resized or moved, the grid will be resized/moved with it without add.programming. Thats a big advantage if user uses 2 monitors.
Grid has "Dynamic propertys" , so it can show for example columns/rows backcolor as green if an anmount is bigger then xxx and/or as red if the amount is less then yyyy. You can use function and make really complex conditions/colours.
If user moves to another row (or col) within the grid, an event is triggered. We use that to show details for the actual record on form after user moves to/selects another row.
Its very easy to have different fonts / attributes for each columns if you like and you can make columns invisible in runtime or change width,...
There are functions from VFP-programmers which automatically show sums/subsums or present data like a treeview or generate a report from grid (including formatting) with a click.
.....
The other tips should help, its realy worth it.
Best regards
tom
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
It is so far ahead of the equivalent in C#... just miles better.
The only place I use Browse over a grid is in my silly little 'on-the-spot' browser
utility that I install on my servers to enable forensic / ad hoc examination of tables.
Regards
ing
Griff
Keep
I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.
There is no place like G28 X0 Y0 Z0
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Having said that, I'm guilty of using BROWSE in some of my apps where I'm the only user. Instead of a grid, I just add a BROWSE button for a quick & dirty (& lazy) way to locate & change data. Old habit.
Steve
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
1. Open a form in the Form Designer.
2. Drag a table and drop it onto the form. You can drag it from the form's Data Environment, a VFP Project Manager window, or even Windows Explorer.
3. Save and run the form.
You now have a fully-functioning grid, showing all the data in the table. It's that simple. (But, of course, you will almost certainly want to customise the grid in various ways.)
If you only want to show specific fields in the grid, add the table to the form's Data Environment, then multi-select the fields you're interested in (hold down Ctrl and/or Shift while you select the individual fields). Drag these to the form.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Greg
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
My opinion, for what it is worth. Take the time to learn to use the grid. Even if you can get by without it right now, there will likely come a point where a browse window just won't give you the degree of control and options that you really need and a grid will be the solution. Embrace it sooner rather than later. You will be glad you did!
Alec
Santa Barbara, California
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Chriss
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
You can design the data structure with foreign keys and browse data this way, for example - making use of SET RELATION and BROWSE FIELDS:
CODE
Create Database football.dbc Create Table teams (id int autoinc primary key, teamname c(20)) Insert into teams (teamname) values ('eagles') Insert into teams (teamname) values ('chiefs') Use Create Table games (id int autoinc primary key, hometeam int, awayteam int, scorehome int, scoreguest int) Insert into games (hometeam, awayteam, scorehome, scoreguest) values (1,2,35,38) Use Use teams in 0 alias team1 Order id Use teams in 0 alias team2 Order id again Select 0 Use games Set relation to hometeam into team1 Set relation to awayteam into team2 Browse fields host=team1.teamname, guest=team2.teamname, scorehome, scoreguestThis is not that straight forward, you need to dig into the xBase way of setting relations on one hand and also know about the FIELDS feature of browse capable to show fields of other workareas, on the other hand. So this requires several insights into how Foxpro works. And I bet you most beginners wouldn't go for that but simply have the hometeam and awayteam fields being chars and not have a teams DBF at all.
And even if you go that far, know all that, and do your browse this way, you now have readonly columns host and guest. The solution to that using the grid control instead of the browse is to have comboboxes in the team columns and use the team ids as controlsource to show the team name as a combobox item. and also be able to select a teamname and set the team id through it.
Chriss
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
I do not know, why.
So - again: Thank you all very much for your great and kind help - I am now also convinced that it is more efficient to work with GRID than with BROWSE - basically all answers say this in unison.
Too bad I can't spend a round of beer on it here -
that would be easy for me.
Now I need some time to familiarize myself with GRID.
The first question follows immediately.
Klaus
Peace worldwide - it starts here...
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Thank you for your showing me an easy way for you to get started with grids.
That works with a table in this case named Aktien2.dbf
I can see now a form, and the grid1, which shows me all the fields of dbf Aktien2.dbf
The first field-name of Aktien2.dbf is named Ak2begriff
The question is now for me:
I added a commandbutton on the form and in the CLICK of that commandbutton I coded:
Select DISTINCT(ak2begriff) from aktien2 into cursor mycursor
because I wanted to see another content in the grid.
but then I struggled where the following questionmarks are:
SET FULLPATH on
SELECT distinct(ak2begriff) FROM aktien2 INTO cursor mycursor
thisform.grid1.recordsource = ???
thisform.grid1.recordsourcetype = ????
thisform.Refresh
What hast to be done or to be filled here?
Several trials from my side failed.
Thanks
Klaus
Peace worldwide - it starts here...
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
This is where BROWSE simply creates a new grid initially having no columns that can then simply show what's in the current workarea., but once a grid has its columncount and column onbjects each with a controlsource, that doesn't change simply because you change the recordsource.
So you see a grids intent is not to use the same grid to show different workareas. You design it for one structure and then keep it at that. You can make columns movable, so you can interactively change column order, you can set column visibility to turn on or off columns, but once a column exists from the first recordsource the grid had, it's there until you remove it or until you close the alias that is the grids' source of data.
It's not hard to get to what you want, even if it's not the main intent. You can always get rid of the grid content by simply closing it's workarea. So do
CODE
Think about usual applications that use a grid or a control similar to it. Let's say the file explorer files list. You can have very different lists reorder items, resize their widths, pick columns to show or not show, etc. Well, and the VFP grid offers all that too, interactively. You can move columns, resize their width, you can not set them visible=.f. with the user interface alone, but this possibility can be given to the user.
If you would use this concept of closing workarea and setting a new one, you can't add permanent things to the grid that make it more useful than a browse, but at least you'd use the property of the grid to not be a separate window, but a control with position and size on the form. If that's all you need from the grid, then that's your solution.
A variation to not need to close the previous workarea is to remove columns before setting the new recordsource. This way works:
CODE
Thisform.grid1.resettodefault('columncount') Thisform.grid1.RecordSource='mycursor'Interestingly, if you try the simpler Thisform.grid1.columncount=0 setting a new recordsource alias does not trigger the grid to create new columns. You then have to do
CODE
Thisform.grid1.columncount=0 Thisform.grid1.columncount=fcount('mycursor') Thisform.grid1.RecordSource='mycursor'In the aspect of using the grid for such interactively changing data sources you waste some of the possibilities of the grid as you throw away the grid substructure and recreate it. But it's just as good as browses with a fixed position inside your form.
Now, if your next button should bring back the full aktien2.dbf, then you better have 2 grids in the form, of course. Keep grid1 to display aktien2.dbf and use something to switch grids. In the simplest case have a pageframe with 2 pages and a grid on each of them and set activepage. If you design a grid with all bells and whistles at design time, with dynamic coloring and code in column controls, you don't want to throw that away just to display something else temporarily. You could always integrate one such "throwaway" grid or even create new grid objects at runtime with thisform.addobject('gridx','grid') and thereby emulate creating browse windows without having the windows. But that's not truly the best use of a grid.
Chriss
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Chriss
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Don't use DISTINCT. From VFP Hacker's Guide
Furthermore I hope your DBF is indexed on ak2begriff - if not please do so. If you then = INDEXSEEK(...) or = SEEK(...) or SEEK (the command) the record pointer jumps to the 1st record found and highlights it in the grid - the subsequent records are shown below. Please have a look at he code I already posted or the one below (a slightly pimped up version of the former one).
CODE -->
LOCAL loForm loForm = CREATEOBJECT("clssearch") loForm.Show() Read EVENTS CLOSE ALL CLEAR ALL RETURN *********** DEFINE CLASS clssearch AS form Top = 12 Left = 12 Height = 474 Width = 1050 MinWidth = This.Width Caption = "Incremental Search" WindowState = 0 AutoCenter = .T. Name = "clssearch" Themes = .F. ShowTips = .T. ADD OBJECT lblName as Label WITH ; Left = 12, Top = 12, Caption = "Item to search :", FontBold = .T. ADD OBJECT lblFound as Label WITH ; Left = 270, Top = 12, Autosize = .T., Caption = "Item highlighted :", FontBold = .T. ADD OBJECT lblItemName as Label WITH ; Left = 270, Top = 36, AutoSize = .T., Caption = "", FontBold = .T., FontItalic = .T., FontSize = 12 ADD OBJECT txtBox as TextBox WITH ; Left = 12, Top = 36, Format = "!", ToolTipText = "Double-click to clear textbox" PROCEDURE txtBox.DblClick() This.Value = "" ENDPROC PROCEDURE txtBox.InteractiveChange LOCAL lcSearch lcSearch = ALLTRIM(This.Value) = INDEXSEEK(lcSearch, .T., "csrWords") WITH ThisForm .lblItemName.Caption = cWord .grdValues.Refresh() ENDWITH ENDPROC ADD OBJECT grdValues AS Grid WITH ; Left = 12, ; Top = 72, ; Height = 474 - 72 - 12, ; Width = 1050 - 24, ; Anchor = 15, ; BackColor = RGB(224, 224, 224), ; AllowRowSizing = .F., ; HeaderHeight = 21, ; AllowHeaderSizing = .F., ; DeleteMark = .F., ; HighLightStyle = 2, ; HighlightBackColor = RGB(0, 250, 250), ; HighlightForeColor = RGB(0, 0, 0), ; ColumnCount = -1, ; RowSourceType = 2, ; RowSource = "csrWords", ; ReadOnly = .T. PROCEDURE grdValues.Init() WITH This .SetAll("FontBold", .T., "Header") .SetAll("BackColor", RGB(0, 250, 250), "Header") ENDWITH WITH This.Column1 .Width = 246 .Header1.Caption = "Name" ENDWITH WITH This.Column2 .Width = 150 .Header1.Caption = "Value One" ENDWITH WITH This.Column3 .Width = 150 .Header1.Caption = "Value Two" ENDWITH WITH This.Column4 .Width = 150 .Header1.Caption = "VO + VT" ENDWITH WITH This.Column5 .Width = 150 .Header1.Caption = "VO - VT" ENDWITH WITH This.Column6 .Width = 150 .Header1.Caption = "VO + (2 * VT)" ENDWITH ENDPROC PROCEDURE grdValues.AfterRowColChange() LPARAMETERS nColIndex WITH ThisForm .lblItemName.Caption = cWord .Refresh() ENDWITH ENDPROC PROCEDURE Load() LOCAL lnI, lnY, lnWordLength, lcItem, lnUpper, lnLower lnUpper = 90 &&ASCII (Z) lnLower = 65 &&ASCII (A) CREATE CURSOR csrWords ( cWord C(20), iValOne I, iValTwo I, iValThree I, iValFour I, iValFive I) INDEX on cWord TAG tagWord SET ORDER to tagWord FOR lnI = 1 to 1500 lcItem = "" lnWordLength = INT(20 * RAND()) + 1 FOR lnY = 1 TO lnWordLength lcItem = lcItem + CHR(INT((lnUpper - lnLower + 1) * RAND()) + lnLower) ENDFOR INSERT INTO csrWords VALUES (lcItem, RAND() * 5000, RAND() * 1250, 0, 0, 0) UPDATE csrWords SET iValThree = iValOne + iValTwo, iValFour = iValOne - iValTwo, iValFive = iValOne + (2 * iValTwo) ENDFOR ENDPROC PROCEDURE Destroy() ThisForm.Release() CLEAR Events ENDPROC ENDDEFINE **********hth
MarK
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Chriss
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
once again ...thank you for the many new information and hints how to improve my primitive program.
My knowledge dates back to 23 years ago when I retired.
But now I'm happy to get to know the whole development of VFP and OOP after 2002.
In addition, I also have to remember what I knew better than 20 years ago.
Not so easy when you are already 81 years old.
But that's just by the way....I learn more here than I did all the time before 2002.
That's why I only use my current program "quick and dirty" - it started in 2014 when I noted all sales of securities with the spreadsheet LOTUS 1-2-3, but that was much more tedious than it can be done with a database table today.
The table aktien2.dbf-structure is as follows:
and your assumption is correct - it is not yet indexed. The basic index should be on ak2begriff which is the name of a share, which repeats often in the table aktien2.dbf
and the form aktien2 looks like this:
The file "aktien2" is longer, but I shortened it.
All objects on the form show me stock sales in various groups (I used DISTINCT(ak2term) with summation .
e.g
Sales with gain or loss (ak2ergebni)
in total
by name of the day of the week
by name of a single stock via click(List1)
by calendar years
Etc.
This also works - the (old) browse windows are always used
shown where I also have the grand totals at the top of the title
display each.
But it's still totally old-fashioned - not the style of professionals - and
not friendly either, because of course you have to close the browse window again and again to get back to the form.
But it helps me with purchasing decisions, and I can always see if I'm broke or if it's worth moving on.
BTW - if someone tells you they always win at the stock market - run away quick - it's a liar.
I hope to have explained a litte more detailed, what I want to change.
(of course by using your hints then).
Thank you
Klaus
Peace worldwide - it starts here...
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Your post raises more questions than we probably can help to solve
What struck me first is that "Gesamtergebnis" is the sum of "Gewinn gesamt" + "Verluste" and not the difference.
Furthermore you have a field called "ak2datum" and then fields like "ak2monat", "ak2jahr"... Are you aware that VFP has date/datetime handling functions like
and
You find them in VFP Hacker's Guide 7 - which you own and hopefully have read - see your thread184-1812467: Second hand books for Visual Foxpro
Finally I think you need to restructure you table(s) and "normalize" it/them, e.g. You don't need a field like ak2ergebnis since this is the difference between ak2gewinn and ak2verlust ...
And last - we can help you (re)coding. But the conception work will be your job, the computed results seems to have strong influence on your decisions where and when to invest your money.
Hope I wasn't too harsh
MarK
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
...
I forgot the crucial question: how and where would you like us to help you?
MarK
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Thank you for your inquiry and your valuable information.
it is by no means harsh - I even found them to be friendly.
In communication there was an overlap of questions by me and answers from you and others in the forum,
given.
(this is actually a good sign if everyone replies quickly),
but I didn't have the time to answer everything immediately.
Chriss had already suggested a solution that helps me,
from data condensed in a cursor in a grid.
answered my question to avoid switching between form and old Browse window.
Also all the other features of a Grid given by you and all others will help me to work with grids only in future.
But nevertheless your request/advice is still very useful for me.
This question is justified.
When I wrote it I had in mind 2 columns in the base file - one for wins and one for losses -
but of course it is automatically in the calculation and there are of course profits-losses = result.
May also could be a typo by me.
Thank you also for your hints on the evaluation of calendar dates in months, weeks, days.
I have also used these functions in the program, the base table receives an accounting date manually - and I run this directly into the columns of the base file for cmonth, cdow in the file via the program.
This makes coding easier, and given the size of the table, it takes time
maybe probably under a second.(replace-all command)
I can definitely use the other special features and considerations about calendar dates in your detailed information at some point.
Your last question
My Answer:
I intend to rewrite my programm from scratch and I intend to built in as much as what was suggested for improvement here.
However - when new questions arise, I know that the best help can be found here.
Stay healthy
Regards
Klaus
Peace worldwide - it starts here...
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Using a grid is one step towards having the display within the form itself, reusing the rid is questionable for that matter, I already said what else I'd do, but it's still not clear whether I'm right in my guess you used the browse for picking one of the distinct value. If you do, what is done with this selection is still open.
I think I conclude this way here: I see how to handle the grid helped you here, if you have further questions you'll make further posts.
For the current phase and changing from BROWSE windows to grids, it might be helpful to your form design to have a single grid in the form which only has the purpose of showing the data you otherwise would display by BROWSE. Or are there cases, in which you have two or more BROWSE open at the same time?
I also would conclude that having a grid instead of a browse is only a minor improvement of the overall UI, as it just fixes the position and size of the browse. As I said already I think you're misusing a browse for a listbox or combobox to pick a value, not necessarily as an editing tool. Your query Select DISTINCT(ak2begriff) from aktien2 into cursor mycursor doesn't create a readwrite cursor so BROWSE of mycursor will only display it and you can move the record pointer to one of the mycursor records. That's what a listbox also does on a form, or a grid. A listbox is more appropriate for single column data, though indeed you can also display readonly data with multiple columns in a listbox, too.
For a complete rewrite the first thing I'd look into is the data rerstructuring, not the form restructuring, though. Maybe because I'm used to work on the basis of the right data design first. This is a rewriting straategy you couldn't do in small improvement steps. A new data structuring would require to also do all forms from scratch and not get away with slight modifications towards a final goal.
Chriss
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
That is correct.
It is the INIT-Method in List1 (the picklist) with this code where an array was established and filled with distinct(ak2begriff) -
**********************
CODE -->
*USED = aktien2 * List1 - Init IF NOT DODEFAULT() RETURN .F. ENDIF IF NOT USED('aktien2') USE aktien2 IN 0 ENDIF *This creates an array of the list values. LOCAL ARRAY laCompanies[1] SELECT DISTINCT(ak2begriff) AS company FROM aktien2 INTO array laCompanies if _TALLY = 0 THIS.iaData[1] = "(Keine Datensätze gefunden)" && or somesuch endif *This creates an array property for the array data in the control. THIS.AddProperty("iaData(1)") *This makes the array property big enough for the data DIMENSION THIS.iaData[alen(laCompanies,1),alen(laCompanies,2)] *This copies the array data into the array property. ACOPY(laCompanies,THIS.iaData,1,-1,1) *Maybe we need the rowsourcetype. I seem not to have copied that. THIS.RowSourceType=5 THIS.RowSource="THIS.iaData"...and in the Click-Event of List1 (the picklist) I wanted to show max. 50 records of a selected share in a browse-window.
CODE -->
*Click-event of List1 Public suchwert Store This.Value To suchwert Set Talk Off Select ak2begriff, ak2aktie,ak2stueck,ak2stckprs,ak2ergebni,ak2datum,ak2divi From aktien2 Order By ak2datum Into Cursor ttt Where ak2begriff = suchwert Wait Window Fullpath(Dbf()) Select ttt Sum ak2ergebni To gesamtpropapier For ak2begriff = suchwert ********************************************************************************************** *The following code should avoid that the Browse-Window shows more than 50 movements. ********************************************************************************************** Store Reccount() To lastrec Do Case Case Between(lastrec,1,52) Browse Title "Ergebnis dieses Wertpapiers "+ Alltrim(Str(gesamtpropapier))+Fullpath(Dbf()) Case Reccount()>52 Go Reccount()-52 Store Recno() To abrecno Browse Title "Ergebnis dieses Wertpapiers "+ Alltrim(Str(gesamtpropapier)) For Recno()>abrecno Endcase ***As you can see, List1's main purpose is to show unique stock names and the results that go with them -
aktien2.dbf can contain e.g. 5 times the ak2begriff "TESLA" - therefore DISTINCT (in future this would better by using SQL ...GROUP BY)
The other commandbuttons are designed to provide summaries
have to show results by calendar year, or month, or day of the week. etc.
I simply made a practical way of entering new data records with (command-button - use aktien2.dbf - go bottom.)
It's definitely to do all this much more elegant and probably more efficient - and I would be happy to learn that.
It is never too late.
greeting
Klaus
Peace worldwide - it starts here...
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Rainy day - had some spare time. Wrote something for you.
The sample code uses three cursors/tables: one for the shares, one for the buying history and one for the value history. The last two have a similar structure but their functions are totally different. Although the Buy/Sell commandbutton works with RAND() generated data there is no way (yet) to modify the saved data afterwords.
Maybe it gives you some hints. Enjoy.
CODE -->
LOCAL go_Form go_Form = CreateObject("frmForm") go_Form.Visible = .T. go_Form.Show READ Events CLOSE ALL CLEAR ALL ***** DEFINE CLASS frmForm As Form DIMENSION aItems[1], aValues[1] gc_ItemCode = REPLICATE("Z", 12) gi_ActivePage = 1 Height = 360 Width = 690 MaxWidth = This.Width MinHeight = This.Height MinWidth = This.Width MaxButton = .F. MinButton = .F. Caption = "Shares" BackColor = RGB(120, 120, 120) AutoCenter = .T. ShowTips = .T. Themes = .F. *!* ADD OBJECT imgFImage AS image WITH ; *!* Left = 0, ; *!* Top = 0, ; *!* Height = ThisForm.Height, ; *!* Width = ThisForm.Width, ; *!* Stretch = 2, ; *!* Name = "imgFImage", ; *!* Visible = .T., ; *!* Picture = "Picture 098.jpg", ; *!* Anchor = 15 ADD OBJECT lblItems AS Label WITH ; Top = 12, ; Left = 12, ; Autosize = .T., ; BackStyle = 0, ; ForeColor = RGB(254, 254, 254), ; FontBold = .T. PROCEDURE lblItems.Refresh() This.Caption = IIF(ThisForm.gi_ActivePage != 4, "Please choose Item", "Click on any Items page") ENDPROC ADD OBJECT lblWinLoss AS Label WITH ; Top = 12, ; Left = 492, ; Autosize = .T., ; BackStyle = 0, ; FontBold = .T., ; FontItalic = .T., ; FontSize = 12, ; ForeColor = RGB(255, 255, 255), ; Caption = "" ADD OBJECT cmdBuy as CommandButton WITH ; Top = 36, ; Left = 378, ; Height = 24, ; Caption = "Buy/Sell", ; Enabled = .F. PROCEDURE cmdBuy.Refresh() IF ThisForm.gc_ItemCode = REPLICATE("Z", 12) This.Enabled = .F. This.ToolTipText = "Please choose Item" ELSE This.Enabled = .T. This.ToolTipText = "Click to buy/sell" ENDIF ENDPROC PROCEDURE cmdBuy.Click() LOCAL liAnswer as Integer, liQuantity as Integer, lcISIN as Character, lnValue as Number(7,2) LOCAL ARRAY laCheck[1] lcISIN = ThisForm.gc_ItemCode liQuantity = INT((RAND() - 0.5) * 25) liValue = RAND() * 200 liAnswer = MESSAGEBOX("Do you want to buy/sell " + ALLTRIM(STR(liQuantity)) + " shares " + lcISIN + " for " + TRANSFORM(liValue, "999.99") + " € ?", 4 + 32, "Buy/Sell Shares") IF liAnswer = 6 SELECT cISIN, dInDate ; FROM Invest_In ; WHERE cISIN = lcISIN AND TTOD(dInDate) = TTOD(DATETIME() - 86400) ; INTO ARRAY laCheck IF ALEN(laCheck) = 1 AND VARTYPE(laCheck[1]) = "L" INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, liQuantity, liValue) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, 0, liValue) ELSE = MESSAGEBOX("You're allowed to buy/sell " + lcISIN + " only once a day!", 64, "Buy/Sell Shares", 5000) ENDIF WITH ThisForm .pgfBuySell.ActivePage = 1 .POneClick() ENDWITH ENDIF ENDPROC ADD OBJECT cboItems as Combobox WITH ; Top = 36, ; Left = 12, ; Height = 24, ; Width = 354, ; Style = 2, ; ColumnCount = 2, ; ColumnWidths = "210, 120" PROCEDURE cboItems.Init() Select cShareHolder, cISIN FROM Shareholder ; ORDER BY 1 ; INTO ARRAY ThisForm.aItems WITH This .RowSourceType = 5 .RowSource = "ThisForm.aItems" .Requery() ENDWITH This.Value = ThisForm.aItems[1,1] ENDPROC PROCEDURE cboItems.Click() WITH ThisForm .gc_ItemCode = ALLTRIM(This.List[This.ListIndex,2]) .cmdBuy.Refresh() .pgfBuySell.UnBold() .pgfBuySell.Pages(ThisForm.gi_ActivePage).Click() ENDWITH ENDPROC ADD OBJECT pgfBuySell as PageFrame WITH ; Top = 84, ; Left = 12, ; Width = ThisForm.Width - 24, ; Height = ThisForm.Height - 108, ; Anchor = 15, ; PageCount = 4 PROCEDURE pgfBuySell.Init() LOCAL loPage as Object FOR i = 1 TO This.pageCount loPage = This.Pages(i) loPage.AddObject("grdBuySell","grdBase") IF i = 3 loPage.RemoveObject("grdBuySell","grdBase") loPage.AddObject("lblISIN", "Label") WITH loPage.lblISIN .Visible = .T. .Top = 24 .Left = 24 .Caption = "ISIN" ENDWITH loPage.AddObject("lblUpdate", "Label") WITH loPage.lblUpdate .Visible = .T. .Top = 24 .Left = 150 .AutoSize = .T. .Caption = "Last update" ENDWITH loPage.AddObject("lblNewValue", "Label") WITH loPage.lblNewValue .Visible = .T. .Top = 24 .Left = 306 .AutoSize = .T. .Caption = "New Value" ENDWITH loPage.AddObject("cboISIN", "ComboBox") WITH loPage.cboISIN .Visible = .T. .Top = 48 .Left = 24 .Width = 120 ENDWITH loPage.AddObject("txtDateTime", "TextBox") WITH loPage.txtDateTime .Visible = .T. .Top = 48 .Left = 150 .Width = 150 .ReadOnly = .T. ENDWITH loPage.AddObject("txtShareValue", "TextBox") WITH loPage.txtShareValue .Visible = .T. .Top = 48 .Left = 306 .Width = 90 .ReadOnly = .F. .Value = 0 .InputMask = "999,999.99" ENDWITH loPage.AddObject("cmdAddValue", "CommandButton") WITH loPage.cmdAddValue .Visible = .T. .Top = 36 .Left = 420 .Width = 120 .Height = 60 .Caption = "Add Value" .BackColor = RGB(0, 250, 250) ENDWITH ENDIF loPage.Caption = ICASE(i = 1, "Shares bought/sold", i = 2, "Recent evolution", i = 3, "Update Values", "Portfolio") ENDFOR ENDPROC PROCEDURE pgfBuySell.UnBold() LOCAL lnI This.SetAll("FontBold", .F., "Page") FOR lnI = 1 TO This.PageCount IF lnI != 3 This.Pages(lnI).grdBuySell.Visible = .F. ENDIF ENDFOR ENDPROC PROCEDURE Init() BINDEVENT(This.pgfBuySell.Page1, "Click", This, "POneClick") BINDEVENT(This.pgfBuySell.Page2, "Click", This, "PTwoClick") BINDEVENT(This.pgfBuySell.Page3, "Click", This, "PThreeClick") BINDEVENT(This.pgfBuySell.Page3.cmdAddValue, "Click", This, "PThreeCmdClick") BINDEVENT(This.pgfBuySell.Page3.cboISIN, "Click", This, "PThreeCboClick") BINDEVENT(This.pgfBuySell.Page4, "Click", This, "PFourClick") ENDPROC PROCEDURE PThreeCboClick() LOCAL loPage, lcISIN, ldDateTime loPage = ThisForm.pgfBuySell.Page3 lcISIN = ALLTRIM(loPage.cboISIN.List[loPage.cboISIN.ListIndex,1]) ldDateTime = ThisForm.aValues[ASCAN(ThisForm.aValues, lcISIN) + 1] WITH loPage .txtDateTime.Value = ldDateTime .Refresh() ENDWITH ENDPROC PROCEDURE PThreeCmdClick() LOCAL lcISIN, lnValue, ldDateTime, loPage, liAnswer loPage = ThisForm.pgfBuySell.Page3 WITH loPage lcISIN = ALLTRIM(.cboISIN.List[.cboISIN.ListIndex,1]) lnValue = .txtShareValue.Value ldDateTime = .txtDateTime.Value ENDWITH *!* WAIT WINDOW + lcISIN +" - "+ TRANSFORM(ldDateTime) + " - " + TRANSFORM(lnValue) IF lnValue > 0 AND DATE() > ldDateTime liAnswer = MESSAGEBOX("Do you want to update the value of " + CHR(13) + lcISIN + " with " + TRANSFORM(lnValue) +" € ?", 4 + 32,"Update Value") IF liAnswer = 6 INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME(), 0, lnValue) = MESSAGEBOX("The value of " + lcISIN + " has been updated to " + TRANSFORM(lnValue) +" €.", 64,"Update Value", 5000) ENDIF ELSE = MESSAGEBOX("Value 0 is not accepted - Update can be done only once a day !", 64, "Add Value", 5000) ENDIF WITH loPage .txtShareValue.Value = 0 .Click() ENDWITH ENDPROC PROCEDURE POneClick() LOCAL ARRAY laInvest[1] WITH ThisForm .gi_ActivePage = 1 .lblItems.Refresh() .cboItems.Enabled = .T. ENDWITH IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) SELECT cISIN, dInDate, iQuantity * nValue as nPInvest, iQuantity * nValue as nTInvest ; FROM Invest_in ; WHERE Invest_In.cISIN = ThisForm.gc_ItemCode ; ORDER BY 2 ASC ; INTO ARRAY laInvest FOR i = 2 TO ALEN(laInvest, 1) laInvest[i, ALEN(laInvest, 2)] = laInvest[i, ALEN(laInvest, 2)] + laInvest[i - 1, ALEN(laInvest, 2)] ENDFOR CREATE CURSOR csrTInvest(cISIN C(12), dInDate T, nInvest N(7,2), nTInvest N(7,2)) APPEND FROM array laInvest SELECT ShareHolder.cISIN, ShareHolder.cShareHolder, Invest_In.dInDate, Invest_In.iQuantity, Invest_In.nValue, Invest_In.iQuantity * Invest_In.nValue as nInvest, csrTInvest.nTInvest ; FROM ShareHolder ; JOIN Invest_In ON ShareHolder.cISIN = Invest_In.cISIN ; JOIN csrTInvest ON Invest_In.cISIN = csrTInvest.cISIN AND InVest_In.dInDate = csrTInvest.dInDate ; WHERE ShareHolder.cISIN = ThisForm.gc_ItemCode ; ORDER BY 3 DESC ; INTO CURSOR csrTemp ENDIF IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) LOCATE WITH This.pgfBuySell .UnBold() .Page1.FontBold = .T. ENDWITH WITH This.pgfBuySell.Page1.grdBuySell .ColumnCount = -1 .RecordSource = "csrTemp" .Visible = .T. .Column1.Header1.Caption = "ISIN" .Column1.Width = 120 .Column2.Header1.Caption = "Company" .Column2.Width = 78 .Column3.Header1.Caption = "Date" .Column3.Width = 120 .Column4.Header1.Caption = "Units" .Column4.Width = 54 .Column5.Header1.Caption = "Value" .Column5.Width = 60 .Column6.Header1.Caption = "Invest" .Column6.Width = 84 .Column7.Header1.Caption = "Total Invest" .Column7.Width = 84 ENDWITH ELSE WITH This.pgfBuySell .UnBold() ENDWITH = MessageBox("No Item chosen", 16, "Choose Item", 2000) ENDIF This.Refresh() ENDPROC PROCEDURE PTwoClick() LOCAL ARRAY laShares[1, 1] WITH ThisForm .gi_ActivePage = 2 .lblItems.Refresh() .cboItems.Enabled = .T. ENDWITH IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) SELECT Invest_In.dInDate, Invest_In.iQuantity, Invest_In.iQuantity As iTotal ; FROM Invest_In ; WHERE Invest_In.cISIN = ThisForm.gc_ItemCode ; ORDER BY 1 ASC ; INTO ARRAY laShares FOR i = 2 TO ALEN(laShares, 1) laShares[i, ALEN(laShares, 2)] = laShares[i, ALEN(laShares, 2)] + laShares[i - 1, ALEN(laShares, 2)] ENDFOR FOR i = 1 TO ALEN(laShares) STEP 3 UPDATE Invest_Out SET iQuantity = laShares[i + 2] WHERE dInDate >= laShares[i] AND cISIN = ThisForm.gc_ItemCode ENDFOR SELECT ShareHolder.cISIN, ShareHolder.cShareHolder, INVEST_OUT.dInDate, INVEST_OUT.iQuantity, INVEST_OUT.nValue, INVEST_OUT.iQuantity * INVEST_OUT.nValue as nInvest ; FROM ShareHolder ; JOIN Invest_Out ON ShareHolder.cISIN = Invest_Out.cISIN ; WHERE ShareHolder.cISIN = ThisForm.gc_ItemCode ; ORDER BY 3 DESC ; INTO CURSOR csrTemp READWRITE ENDIF IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) LOCATE WITH This.pgfBuySell .UnBold() .Page2.FontBold = .T. ENDWITH WITH This.pgfBuySell.Page2.grdBuySell .ColumnCount = -1 .RecordSource = "csrTemp" .Visible = .T. .Column1.Header1.Caption = "ISIN" .Column1.Width = 120 .Column2.Header1.Caption = "Company" .Column2.Width = 78 .Column3.Header1.Caption = "Date" .Column3.Width = 120 .Column4.Header1.Caption = "Units" .Column4.Width = 54 .Column5.Header1.Caption = "Value" .Column5.Width = 60 .Column6.Header1.Caption = "T-Value" .Column6.Width = 84 ENDWITH ELSE WITH This.pgfBuySell .UnBold() ENDWITH = MessageBox("No Item chosen", 16, "Choose Item", 2000) ENDIF This.Refresh() ENDPROC PROCEDURE PThreeClick() LOCAL liAmount as Integer, lcItemCode as Character, loPage as Object loPage = ThisForm.pgfBuySell.Page3 WITH ThisForm .gi_ActivePage = 3 .lblItems.Refresh() .cboItems.Enabled = .T. ENDWITH WITH This.pgfBuySell .UnBold() .Page3.FontBold = .T. ENDWITH SELECT IVO1.cISIN, IVO1.dInDate ; FROM Invest_Out IVO1 ; WHERE IVO1.cISIN + DTOS(IVO1.dInDate) IN (SELECT IVO2.cISIN + DTOS(MAX(IVO2.dInDate)) FROM Invest_Out IVO2 GROUP BY IVO2.cISIN) ; INTO ARRAY ThisForm.aValues WITH loPage .cboISIN.Style = 2 .cboISIN.RowSourceType = 5 .cboISIN.RowSource = "ThisForm.aValues" .cboISIN.Requery() .cboISIN.Value = ThisForm.aValues[1] .txtDateTime.Value = ThisForm.aValues[2] ENDWITH This.Refresh() ENDPROC PROCEDURE PFourClick() LOCAL ARRAY laWinLoss[1] WITH This .gi_ActivePage = 4 .lblItems.Refresh() .cboItems.Enabled = .F. ENDWITH SELECT IVO1.cISIN, IVO1.dInDate, IVO1.nValue ; FROM Invest_Out IVO1 ; WHERE IVO1.cISIN + DTOS(IVO1.dInDate) IN (SELECT IVO2.cISIN + DTOS(MAX(IVO2.dInDate)) FROM Invest_Out IVO2 GROUP BY IVO2.cISIN) ; INTO CURSOR csrValues SELECT ShareHolder.cISIN, ShareHolder.cShareHolder, ; SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nInvest, ; SUM(Invest_In.iQuantity * csrValues.nValue) as nSValue, ; SUM(Invest_In.iQuantity * csrValues.nValue) - SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nLossWin, ; SUM(Invest_In.iQuantity * csrValues.nValue) / SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nPcLossWin ; FROM ShareHolder ; JOIN Invest_In ON ShareHolder.cISIN = Invest_In.cISIN ; JOIN csrValues ON ShareHolder.cISIN = csrValues.cISIN ; GROUP BY 1, 2 ; ORDER BY 2 ASC ; INTO CURSOR csrTemp SELECT SUM(nLossWin) ; FROM csrTemp ; INTO ARRAY laWinLoss ThisForm.lblWinLoss.Caption = IIF(laWinLoss[1] > 0, "Total Win : ", "Total Loss :") + TRANSFORM(laWinLoss[1], "999,999.99") WITH This.pgfBuySell .UnBold() .Page4.FontBold = .T. ENDWITH WITH This.pgfBuySell.Page4.grdBuySell .ColumnCount = -1 .RecordSource = "csrTemp" .SetAll("DynamicBackColor", "IIF(csrTemp.nPcLossWin < 1, RGB(225, 0, 0), RGB(0, 230, 230))","Column") .Visible = .T. .Column1.Header1.Caption = "ISIN" .Column1.Width = 120 .Column2.Header1.Caption = "Company" .Column2.Width = 78 .Column3.Header1.Caption = "Invest" .Column3.Width = 90 .Column4.Header1.Caption = "Value" .Column4.Width = 90 .Column5.Header1.Caption = "Value - Invest" .Column5.Width = 90 .Column6.Header1.Caption = "Value / Invest" .Column6.Width = 90 ENDWITH This.Refresh() ENDPROC PROCEDURE Load() CREATE CURSOR ShareHolder (cISIN C(12), cShareHolder C(30), cItemDesc c(30)) INSERT INTO ShareHolder VALUES ("DE0008469008", "Dax", "") INSERT INTO ShareHolder VALUES ("FR0008469009", "Chanel", "") INSERT INTO ShareHolder VALUES ("US0008469010", "Dell", "") INSERT INTO ShareHolder VALUES ("LU0008469012", "Post", "") CREATE CURSOR INVEST_IN (iPKey I AUTOINC NEXTVALUE 1000, cISIN C(12), dInDate T, iQuantity I, nValue N(7,2)) INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("DE0008469008", {^2022-12-01}, 72, 120) INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("DE0008469008", {^2022-12-04}, 12, 125) INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("DE0008469008", {^2022-12-07}, 38, 130) INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("DE0008469008", {^2022-12-08}, -5, 130) INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("FR0008469009", {^2022-12-04}, 10, 110) INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("FR0008469009", {^2022-12-05}, 53, 120) INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-03}, 45, 125) INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-05}, -10, 125) INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-10}, -5, 120) INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("LU0008469012", {^2022-12-05}, 32 , 109) INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("LU0008469012", {^2022-12-04}, 23, 99) CREATE CURSOR INVEST_OUT (iPKey I AUTOINC NEXTVALUE 1000, cISIN C(12), dInDate T, iQuantity I, nValue N(7,2)) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("DE0008469008", {^2022-12-04}, 0, 120) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity ,nValue) VALUES ("DE0008469008", {^2022-12-05}, 0, 121) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("DE0008469008", {^2022-12-06}, 0 ,122) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("DE0008469008", {^2022-12-07}, 0, 121) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("DE0008469008", {^2022-12-08}, 0, 123) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("FR0008469009", {^2022-12-04}, 0, 110) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("FR0008469009", {^2022-12-05}, 0, 109) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("FR0008469009", {^2022-12-06}, 0, 111) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("FR0008469009", {^2022-12-07}, 0, 112) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("FR0008469009", {^2022-12-09}, 0, 110) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("LU0008469012", {^2022-12-04}, 0, 99) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("LU0008469012", {^2022-12-05}, 0, 109) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("LU0008469012", {^2022-12-06}, 0, 108) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("LU0008469012", {^2022-12-07}, 0, 107) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("LU0008469012", {^2022-12-12}, 0, 110) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-03}, 0, 125) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-04}, 0, 126) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-05}, 0, 127) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-06}, 0, 126) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-07}, 0, 124) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-08}, 0, 122) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-09}, 0, 120) INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-10}, 0, 120) ENDPROC PROCEDURE Destroy() CLEAR Events ThisForm.Release ENDPROC ENDDEFINE ***** DEFINE CLASS grdBase AS Grid Top = 12 Left = 12 Height = 360 - 108 - (4 * 12) Width = 690 - (4 * 12) BackColor = RGB(0, 230, 230) RowHeight = 21 AllowRowSizing = .F. HeaderHeight = 21 AllowHeaderSizing = .F. DeleteMark = .F. ReadOnly = .T. Anchor = 15 Visible = .F. ENDDEFINE *****MarK
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Thanks for the sample code which uses three cursors/tables.
It makes a very good professional impression - as soon as you call it up.
I think it can help me a lot to improve my own program code.
I don't necessarily need the routines for buying stocks.
I already get a statement from the bank that already shows the result of a transaction, namely:
a) Date of transaction
b) Order no.
c) Number of shares sold
d) Designation of the share
e) ISIN
f) Execution rate on sale
g) gain or loss
I enter this data manually into the VFP file (aktien2.dbf) - it's very quick - and you don't do that every day.
Then it just needs to be evaluated - and so I can see
what each sold title brought as a result,
a)with which title the most was won/lost,
b)on which day of the week the highest result (gain or loss) was achieved,
c)at what price it was last sold
d)what the result has been per year.
e)and of course also what the total result of all transactions was since recording.
Your program can give me useful information how to code this.
The file structure for my aktien2.dbf can now also be simplified.
I still have to invest a little more time to fully understand your program, but I have the time.
Thanks very much
Klaus
Peace worldwide - it starts here...
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
This is "No Country for Old Men" - hence we have to support each other.
I have been using my app for almost a week now feeding it with real world values. It showed that it had some quirks which I removed from the code (check below).
Now it works to my fullest satisfaction. As I said, it uses three tables, that are now created the first time you start it. A "Dummy" record is also created in order not to yield errors. This record is deleted asa you add a new record.
CODE -->
LOCAL go_Form go_Form = CreateObject("frmForm") go_Form.Visible = .T. go_Form.Show READ Events CLOSE ALL CLEAR ALL ***** DEFINE CLASS frmForm As Form DIMENSION gaItems[1], gaValues[1] gc_ItemCode = REPLICATE("Z", 12) gi_ActivePage = 1 Height = 444 Width = 750 MaxWidth = This.Width MinHeight = This.Height MinWidth = This.Width MaxButton = .F. MinButton = .F. Caption = "Shares" BackColor = RGB(120, 120, 120) AutoCenter = .T. ShowTips = .T. Themes = .F. ADD OBJECT imgFImage AS image WITH ; Left = 0, ; Top = 0, ; Height = ThisForm.Height, ; Width = ThisForm.Width, ; Stretch = 2, ; Name = "imgFImage", ; Visible = .T., ; Picture = "Picture 098.jpg", ; Anchor = 15 ADD OBJECT lblItems AS Label WITH ; Top = 12, ; Left = 12, ; Autosize = .T., ; BackStyle = 0, ; ForeColor = RGB(254, 254, 254), ; FontBold = .T. PROCEDURE lblItems.Refresh() This.Caption = IIF(INLIST(ThisForm.gi_ActivePage, 1, 2, 3), "Please choose Item", "Click on page 1, 2 or 3") ENDPROC ADD OBJECT cmdBuy as CommandButton WITH ; Top = 36, ; Left = 378, ; Height = 24, ; Width = 180, ; Caption = "Buy/Sell coupons from portfolio ", ; Enabled = .F. PROCEDURE cmdBuy.Refresh() IF ThisForm.gc_ItemCode = REPLICATE("Z", 12) OR ThisForm.gc_ItemCode = "XX1234567890" OR INLIST(ThisForm.gi_ActivePage, 2, 3, 4, 5, 6) This.Enabled = .F. This.ToolTipText = "Please choose Item" ELSE This.Enabled = .T. This.ToolTipText = "Click to buy/sell" ENDIF ENDPROC PROCEDURE cmdBuy.Click() LOCAL liAnswer as Integer, liQuantity as Integer, lcISIN as Character, lnValue as Number(10,2) LOCAL ARRAY laCheck[1] lcISIN = ThisForm.gc_ItemCode liQuantity = INT((RAND() - 0.5) * 25) liValue = RAND() * 200 liAnswer = MESSAGEBOX("Do you want to buy/sell " + ALLTRIM(STR(liQuantity)) + " shares " + lcISIN + " for " + TRANSFORM(liValue, "999.99") + " € ?", 4 + 32, "Buy/Sell Shares") IF liAnswer = 6 SELECT Invest_In.cISIN, Invest_In.dInDate as dINVIDate, Invest_Out.dInDate as dINOUDate ; FROM Invest_In ; JOIN Invest_Out ON Invest_In.cISIN = Invest_Out.cISIN ; WHERE TTOD(Invest_In.dInDate) = TTOD(DATETIME() - 86400) OR TTOD(Invest_Out.dInDate) = TTOD(DATETIME() - 86400) ; INTO ARRAY laCheck IF VARTYPE(laCheck[1]) = "L" *!* INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, liQuantity, liValue) *!* INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, 0, liValue) WAIT WINDOW + "Procedure suspended" TIMEOUT 3 ELSE = MESSAGEBOX("You're allowed to buy/sell " + lcISIN + " only once a day!", 64, "Buy/Sell Shares", 5000) ENDIF WITH ThisForm .pgfBuySell.ActivePage = 1 .POneClick() ENDWITH ENDIF ENDPROC ADD OBJECT cboItems as Combobox WITH ; Top = 36, ; Left = 12, ; Height = 24, ; Width = 354, ; Style = 2, ; ColumnCount = 2, ; ColumnWidths = "210, 120", ; IncrementalSearch = .T. PROCEDURE cboItems.Init() Select cShareItem, cISIN FROM INVEST_BK ; ORDER BY 1 ; INTO ARRAY ThisForm.gaItems WITH This .RowSourceType = 5 .RowSource = "ThisForm.gaItems" .Requery() ENDWITH This.Value = ThisForm.gaItems[1,1] ENDPROC PROCEDURE cboItems.Click() WITH ThisForm .gc_ItemCode = ALLTRIM(This.List[This.ListIndex,2]) .cmdBuy.Refresh() .pgfBuySell.UnBold() .pgfBuySell.Pages(ThisForm.gi_ActivePage).Click() ENDWITH ENDPROC ADD OBJECT pgfBuySell as PageFrame WITH ; Top = 84, ; Left = 12, ; Width = ThisForm.Width - 24, ; Height = ThisForm.Height - 108, ; Anchor = 15, ; PageCount = 5, ; ToolTipText = "RightClick to show Investment - Values" PROCEDURE pgfBuySell.Init() LOCAL loPage as Object FOR i = 1 TO This.pageCount loPage = This.Pages(i) loPage.ToolTipText = "RightClick to show Investment - Values" loPage.AddObject("grdBuySell","grdBase") IF i = 3 loPage.RemoveObject("grdBuySell","grdBase") loPage.AddObject("lblISIN", "Label") WITH loPage.lblISIN .Visible = .T. .Top = 24 .Left = 24 .Caption = "ISIN" ENDWITH loPage.AddObject("lblUpdate", "Label") WITH loPage.lblUpdate .Visible = .T. .Top = 24 .Left = 150 .AutoSize = .T. .Caption = "Last update" ENDWITH loPage.AddObject("lblNewValue", "Label") WITH loPage.lblNewValue .Visible = .T. .Top = 24 .Left = 306 .AutoSize = .T. .Caption = "New Value" ENDWITH loPage.AddObject("txtISIN", "TextBox") WITH loPage.txtISIN .Visible = .T. .Top = 48 .Left = 24 .Width = 120 .ReadOnly = .T. ENDWITH loPage.AddObject("txtDateTime", "TextBox") WITH loPage.txtDateTime .Visible = .T. .Top = 48 .Left = 150 .Width = 150 .ReadOnly = .T. ENDWITH loPage.AddObject("txtShareValue", "TextBox") WITH loPage.txtShareValue .Visible = .T. .Top = 48 .Left = 306 .Width = 90 .ReadOnly = .F. .Value = 0 .InputMask = "999,999.99 " ENDWITH loPage.AddObject("cmdAddValue", "CommandButton") WITH loPage.cmdAddValue .Visible = .T. .Top = 36 .Left = 480 .Width = 120 .Height = 60 .Caption = "Add Value" .BackColor = RGB(0, 250, 250) ENDWITH ENDIF IF i = 4 loPage.RemoveObject("grdBuySell","grdBase") loPage.AddObject("lblISIN", "Label") WITH loPage.lblISIN .Visible = .T. .Top = 24 .Left = 24 .Caption = "ISIN" ENDWITH loPage.AddObject("lblCompany", "Label") WITH loPage.lblCompany .Visible = .T. .Top = 24 .Left = 150 .AutoSize = .T. .Caption = "Company" ENDWITH loPage.AddObject("lblQty", "Label") WITH loPage.lblQty .Visible = .T. .Top = 24 .Left = 306 .AutoSize = .T. .Caption = "Quantity" ENDWITH loPage.AddObject("lblValue", "Label") WITH loPage.lblValue .Visible = .T. .Top = 24 .Left = 372 .AutoSize = .T. .Caption = "Value" ENDWITH loPage.AddObject("txtISIN", "TextBox") WITH loPage.txtISIN .Visible = .T. .Top = 48 .Left = 24 .Width = 120 .Format = "!" .InputMask = "AA9999999999" ENDWITH loPage.AddObject("txtCompany", "TextBox") WITH loPage.txtCompany .Visible = .T. .Top = 48 .Left = 150 .Width = 150 .InputMask = "AAAAXXXXXXXXXXXXXXXXXXXXXXXXXX" ENDWITH loPage.AddObject("txtQty", "TextBox") WITH loPage.txtQty .Visible = .T. .Top = 48 .Left = 306 .Width = 60 .ReadOnly = .F. .Value = 0 .InputMask = "999 " ENDWITH loPage.AddObject("txtShareValue", "TextBox") WITH loPage.txtShareValue .Visible = .T. .Top = 48 .Left = 372 .Width = 78 .ReadOnly = .F. .Value = 0 .InputMask = "999,999.99 " ENDWITH loPage.AddObject("cmdNewShare", "CommandButton") WITH loPage.cmdNewShare .Visible = .T. .Top = 36 .Left = 480 .Width = 120 .Height = 60 .Caption = "Add new share" .BackColor = RGB(0, 250, 250) ENDWITH ENDIF loPage.Caption = ICASE(i = 1, "Shares bought/sold", i = 2, "Value evolution", i = 3, "Update value", i = 4, "New share", "Portfolio" ) ENDFOR ENDPROC PROCEDURE pgfBuySell.UnBold() LOCAL lnI This.SetAll("FontBold", .F., "Page") FOR lnI = 1 TO This.PageCount IF INLIST(lnI, 1, 2, 5) This.Pages(lnI).grdBuySell.Visible = .F. ENDIF ENDFOR ENDPROC PROCEDURE pgfBuySell.RightClick() LOCAL ARRAY laWinLoss[1] SELECT IVO1.cISIN, IVO1.dInDate, IVO1.nValue ; FROM Invest_Out IVO1 ; WHERE IVO1.cISIN + DTOS(IVO1.dInDate) IN (SELECT IVO2.cISIN + DTOS(MAX(IVO2.dInDate)) FROM Invest_Out IVO2 GROUP BY IVO2.cISIN) ; INTO CURSOR csrValues SELECT SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nInvest, ; SUM(Invest_In.iQuantity * csrValues.nValue) as nSValue, ; SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) - SUM(Invest_In.iQuantity * csrValues.nValue) as nDValue ; FROM Invest_In ; JOIN csrValues ON INVEST_IN.cISIN = csrValues.cISIN ; INTO ARRAY laWinLoss = MESSAGEBOX("Total invest : " + TRANSFORM(laWinLoss[1], "999,999.99 ") + " €" + CHR(13) ; + "Total value : " + TRANSFORM(laWinLoss[2], "999,999.99 ") +" €" + CHR(13) ; + IIF(laWinLoss[3] < 0, "Total win : ", "Total loss :") + TRANSFORM(laWinLoss[3], "999,999.99 ") + " €" + CHR(13) ; + "Value/Invest : " + TRANSFORM(laWinLoss[2]/laWinLoss[1]) , 64, "Invest - Value - Win - Loss", 25000) ENDPROC PROCEDURE Init() BINDEVENT(This.pgfBuySell.Page1, "Click", This, "POneClick") BINDEVENT(This.pgfBuySell.Page2, "Click", This, "PTwoClick") BINDEVENT(This.pgfBuySell.Page3, "Click", This, "PThreeClick") BINDEVENT(This.pgfBuySell.Page3.cmdAddValue, "Click", This, "PThreeCmdClick") BINDEVENT(This.pgfBuySell.Page4, "Click", This, "PFourClick") BINDEVENT(This.pgfBuySell.Page4.cmdNewShare, "Click", This, "PFourNewCouponClick") BINDEVENT(This.pgfBuySell.Page5, "Click", This, "PFiveClick") ENDPROC PROCEDURE POneClick() LOCAL ARRAY laInvest[1] WITH ThisForm .gi_ActivePage = 1 .lblItems.Refresh() .cmdBuy.Refresh() .cboItems.Enabled = .T. ENDWITH IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) SELECT cISIN, dInDate, iQuantity * nValue as nPInvest, iQuantity * nValue as nTInvest ; FROM Invest_in ; WHERE Invest_In.cISIN = ThisForm.gc_ItemCode ; ORDER BY 2 ASC ; INTO ARRAY laInvest FOR i = 2 TO ALEN(laInvest, 1) laInvest[i, ALEN(laInvest, 2)] = laInvest[i, ALEN(laInvest, 2)] + laInvest[i - 1, ALEN(laInvest, 2)] ENDFOR CREATE CURSOR csrTInvest(cISIN C(12), dInDate T, nInvest N(10,2), nTInvest N(10,2)) APPEND FROM array laInvest SELECT INVEST_BK.cISIN, INVEST_BK.cShareItem, Invest_In.dInDate, Invest_In.iQuantity, Invest_In.nValue, Invest_In.iQuantity * Invest_In.nValue as nInvest, csrTInvest.nTInvest ; FROM INVEST_BK ; JOIN Invest_In ON INVEST_BK.cISIN = Invest_In.cISIN ; JOIN csrTInvest ON Invest_In.cISIN = csrTInvest.cISIN AND InVest_In.dInDate = csrTInvest.dInDate ; WHERE INVEST_BK.cISIN = ThisForm.gc_ItemCode ; ORDER BY 3 DESC ; INTO CURSOR csrTemp ENDIF IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) LOCATE WITH This.pgfBuySell .UnBold() .Page1.FontBold = .T. ENDWITH WITH This.pgfBuySell.Page1.grdBuySell .ColumnCount = -1 .RecordSource = "csrTemp" .Visible = .T. .Column1.Header1.Caption = "ISIN" .Column1.Width = 120 .Column2.Header1.Caption = "Company" .Column2.Width = 150 .Column3.Header1.Caption = "Date" .Column3.Width = 120 .Column4.Header1.Caption = "Units" .Column4.Width = 54 .Column5.Header1.Caption = "Value" .Column5.Width = 60 .Column6.Header1.Caption = "Invest" .Column6.Width = 84 .Column6.Sparse = .F. .Column6.Text1.InputMask = "999,999.99 " .Column7.Header1.Caption = "Total Invest" .Column7.Width = 84 .Column7.Sparse = .F. .Column7.Text1.InputMask = "999,999.99 " ENDWITH ELSE WITH This.pgfBuySell .UnBold() ENDWITH = MessageBox("No Item chosen", 16, "Choose Item", 2000) ENDIF This.Refresh() ENDPROC PROCEDURE PTwoClick() LOCAL ARRAY laShares[1, 1], laDailyLossWin[1] LOCAL lnDailyLossWin WITH ThisForm .gi_ActivePage = 2 .lblItems.Refresh() .cmdBuy.Refresh() .cboItems.Enabled = .T. ENDWITH IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) SELECT Invest_In.dInDate, Invest_In.iQuantity, Invest_In.iQuantity As iTotal ; FROM Invest_In ; WHERE Invest_In.cISIN = ThisForm.gc_ItemCode ; ORDER BY 1 ASC ; INTO ARRAY laShares FOR i = 2 TO ALEN(laShares, 1) laShares[i, ALEN(laShares, 2)] = laShares[i, ALEN(laShares, 2)] + laShares[i - 1, ALEN(laShares, 2)] ENDFOR FOR i = 1 TO ALEN(laShares) STEP 3 UPDATE Invest_Out SET iQuantity = laShares[i + 2] WHERE dInDate >= laShares[i] AND cISIN = ThisForm.gc_ItemCode ENDFOR SELECT Invest_Out.cISIN, COUNT(Invest_Out.cISIN), SUM(Invest_Out.iQuantity * Invest_Out.nValue) ; FROM Invest_Out ; GROUP BY Invest_Out.cIsin ; WHERE INVEST_OUT.cISIN = ThisForm.gc_ItemCode ; INTO ARRAY laDailyLossWin lnDailyLossWin = laDailyLossWin[3] / laDailyLossWin[2] SELECT INVEST_BK.cISIN, INVEST_BK.cShareItem, INVEST_OUT.dInDate, INVEST_OUT.iQuantity, INVEST_OUT.nValue, ; INVEST_OUT.iQuantity * INVEST_OUT.nValue as nInvest, (INVEST_OUT.iQuantity * INVEST_OUT.nValue) / lnDailyLossWin as nDLW ; FROM INVEST_BK ; JOIN Invest_Out ON INVEST_BK.cISIN = Invest_Out.cISIN ; WHERE INVEST_BK.cISIN = ThisForm.gc_ItemCode ; ORDER BY 3 DESC ; INTO CURSOR csrTemp ENDIF IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) LOCATE WITH This.pgfBuySell .UnBold() .Page2.FontBold = .T. ENDWITH WITH This.pgfBuySell.Page2.grdBuySell .ColumnCount = -1 .RecordSource = "csrTemp" .SetAll("DynamicBackColor", "IIF(csrTemp.nDLW < 1, RGB(225, 255, 0), RGB(0, 230, 230))","Column") .Visible = .T. .Column1.Header1.Caption = "ISIN" .Column1.Width = 120 .Column2.Header1.Caption = "Company" .Column2.Width = 150 .Column3.Header1.Caption = "Date" .Column3.Width = 120 .Column4.Header1.Caption = "Units" .Column4.Width = 54 .Column5.Header1.Caption = "Value" .Column5.Width = 60 .Column5.Sparse = .F. .Column5.Text1.InputMask = "999,999.99 " .Column6.Header1.Caption = "T-Value" .Column6.Width = 84 .Column6.Sparse = .F. .Column6.Text1.InputMask = "999,999.99 " .Column7.Header1.Caption = "DLW %" .Column7.Width = 72 .Column7.Sparse = .F. .Column7.Text1.InputMask = "999.9999 " ENDWITH ELSE WITH This.pgfBuySell .UnBold() ENDWITH = MessageBox("No Item chosen", 16, "Choose Item", 2000) ENDIF This.Refresh() ENDPROC PROCEDURE PThreeCmdClick() LOCAL lcISIN, lnValue, ldDateTime, loPage, liAnswer loPage = ThisForm.pgfBuySell.Page3 WITH loPage lcISIN = .txtISIN.Value lnValue = .txtShareValue.Value ldDateTime = .txtDateTime.Value ENDWITH IF lnValue > 0 AND DATE() - 1 > TTOD(ldDateTime) liAnswer = MESSAGEBOX("Do you want to update the value of " + CHR(13) + lcISIN + " with " + TRANSFORM(lnValue) +" € ?", 4 + 32,"Update Value") IF liAnswer = 6 INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, 0, lnValue) = MESSAGEBOX("The value of " + lcISIN + " has been updated to " + TRANSFORM(lnValue) +" €.", 64,"Update Value", 3000) ENDIF ELSE = MESSAGEBOX("Value 0 is not accepted - Update allowed only once a day !", 64, "Add Value", 5000) ENDIF WITH loPage .txtShareValue.Value = 0 .Click() ENDWITH ENDPROC PROCEDURE PThreeClick() LOCAL liAmount as Integer, lcItemCode as Character, loPage as Object loPage = ThisForm.pgfBuySell.Page3 WITH ThisForm .gi_ActivePage = 3 .lblItems.Refresh() .cmdBuy.Refresh() .cboItems.Enabled = .T. ENDWITH WITH This.pgfBuySell .UnBold() .Page3.FontBold = .T. ENDWITH IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) FOR i = 1 TO loPage.ControlCount IF UPPER(loPage.Controls(i).BaseClass) = "TEXTBOX" ; OR UPPER(loPage.Controls(i).BaseClass) = "LABEL" ; OR UPPER(loPage.Controls(i).BaseClass) = "COMMANDBUTTON" loPage.Controls(i).Visible = .T. ENDIF ENDFOR SELECT IVO1.cISIN, IVO1.dInDate ; FROM Invest_Out IVO1 ; WHERE IVO1.cISIN + DTOS(IVO1.dInDate) IN (SELECT IVO2.cISIN + DTOS(MAX(IVO2.dInDate)) FROM Invest_Out IVO2 GROUP BY IVO2.cISIN) ; AND IVO1.cISIN = ThisForm.gc_ItemCode ; ORDER BY IVO1.cISIN ; INTO ARRAY ThisForm.gaValues WITH loPage .txtISIN.Value = ThisForm.gaValues[1] .txtDateTime.Value = ThisForm.gaValues[2] ENDWITH ELSE FOR i = 1 TO loPage.ControlCount IF UPPER(loPage.Controls(i).BaseClass) = "TEXTBOX" ; OR UPPER(loPage.Controls(i).BaseClass) = "LABEL" ; OR UPPER(loPage.Controls(i).BaseClass) = "COMMANDBUTTON" loPage.Controls(i).Visible = .F. ENDIF ENDFOR WITH This.pgfBuySell .UnBold() ENDWITH = MessageBox("No Item chosen", 16, "Choose Item", 2000) ENDIF This.Refresh() ENDPROC PROCEDURE PFourClick() WITH This .gi_ActivePage = 4 .lblItems.Refresh() .cmdBuy.Refresh() .cboItems.Enabled = .F. ENDWITH WITH This.pgfBuySell .UnBold() .Page4.FontBold = .T. ENDWITH ENDPROC PROCEDURE PFourNewCouponClick() LOCAL loPage, lcIsin, lcCompany, liQty, lnValue LOCAL ARRAY laISIN[1] loPage = ThisForm.pgfBuySell.Page4 SELECT cISIN FROM INVEST_BK ORDER BY cISIN INTO ARRAY laISIN lcISIN = ALLTRIM(loPage.txtISIN.Value) lcCompany = ALLTRIM(loPage.txtCompany.Value) liQty = loPage.txtQty.Value lnValue = loPage.txtShareValue.Value IF EMPTY(lcISIN) ; OR LEN(lcISIN) != 12 ; OR EMPTY(lcCompany) ; OR LEN(lcCompany) < 4 ; OR liQty= 0 ; OR lnValue = 0 ; OR ASCAN(laISIN, lcIsin) > 0 WAIT WINDOW + "Noooooo good!" TIMEOUT 2 ELSE IF MESSAGEBOX("Do you want to buy this coupon?", 4 + 32 + 256, "Buying new coupons") = 6 LOCAL ARRAY laDummy[1] SELECT cISIN FROM INVEST_BK ; WHERE cISIN = "XX1234567890" ; INTO ARRAY laDummy IF laDummy[1] = "XX1234567890" SET SAFETY off DELETE FROM INVEST_BK WHERE cISIN = "XX1234567890" USE IN INVEST_BK PACK INVEST_BK DELETE FROM INVEST_IN WHERE cISIN = "XX1234567890" USE IN INVEST_IN PACK INVEST_IN DELETE FROM INVEST_OUT WHERE cISIN = "XX1234567890" USE IN INVEST_OUT PACK INVEST_OUT SET SAFETY ON USE INVEST_BK IN 0 USE INVEST_IN IN 0 USE INVEST_OUT IN 0 ENDIF INSERT INTO INVEST_BK VALUES (lcISIN, lcCompany, "") INSERT INTO Invest_In (cISIN, dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, liQty, lnValue) INSERT INTO Invest_Out (cISIN, dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, liQty, lnValue) = Messagebox("Congratulations! You bought " + TRANSFORM(liQty) + " coupons of " + lcISIN, 64, "Buying new coupons", 3000) Select cShareItem, cISIN ; FROM INVEST_BK ; ORDER BY 1 ; INTO ARRAY ThisForm.gaItems ThisForm.cboItems.Requery() ELSE = Messagebox("Nothing bought!", 64, "Buying new coupons", 3000) ENDIF ENDIF WITH loPage .txtISIN.Value = "" .txtCompany.Value = "" .txtQty.Value = 0 .txtShareValue.Value = 0 .Refresh() ENDWITH ENDPROC PROCEDURE PFiveClick() LOCAL ARRAY laWinLoss[1] WITH This .gi_ActivePage = 5 .lblItems.Refresh() .cmdBuy.Refresh() .cboItems.Enabled = .F. ENDWITH SELECT IVO1.cISIN, IVO1.dInDate, IVO1.nValue ; FROM Invest_Out IVO1 ; WHERE IVO1.cISIN + DTOS(IVO1.dInDate) IN (SELECT IVO2.cISIN + DTOS(MAX(IVO2.dInDate)) FROM Invest_Out IVO2 GROUP BY IVO2.cISIN) ; INTO CURSOR csrValues SELECT INVEST_BK.cISIN, INVEST_BK.cShareItem, ; SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nInvest, ; SUM(Invest_In.iQuantity * csrValues.nValue) as nSValue, ; SUM(Invest_In.iQuantity * csrValues.nValue) - SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nLossWin, ; SUM(Invest_In.iQuantity * csrValues.nValue) / SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nPcLossWin ; FROM INVEST_BK ; JOIN Invest_In ON INVEST_BK.cISIN = Invest_In.cISIN ; JOIN csrValues ON INVEST_BK.cISIN = csrValues.cISIN ; GROUP BY 1, 2 ; ORDER BY 2 ASC ; INTO CURSOR csrTemp WITH This.pgfBuySell .UnBold() .Page5.FontBold = .T. ENDWITH WITH This.pgfBuySell.Page5.grdBuySell .ColumnCount = -1 .RecordSource = "csrTemp" .SetAll("DynamicBackColor", "IIF(csrTemp.nPcLossWin < 1, RGB(225, 255, 0), RGB(0, 230, 230))","Column") .Visible = .T. .Column1.Header1.Caption = "ISIN" .Column1.Width = 120 .Column2.Header1.Caption = "Company" .Column2.Width = 150 .Column3.Header1.Caption = "Invest" .Column3.Width = 90 .Column3.Sparse = .F. .Column3.Text1.InputMask = "999,999.99 " .Column4.Header1.Caption = "Value" .Column4.Width = 90 .Column4.Sparse = .F. .Column4.Text1.InputMask = "999,999.99 " .Column5.Header1.Caption = "Value - Invest" .Column5.Width = 90 .Column5.Sparse = .F. .Column5.Text1.InputMask = "999,999.99 " .Column6.Header1.Caption = "Value / Invest" .Column6.Width = 90 ENDWITH This.Refresh() ENDPROC PROCEDURE Load() IF NOT (FILE("INVEST_BK.dbf") AND FILE("Invest_In.dbf") AND FILE("Invest_Out.dbf")) CREATE TABLE INVEST_BK (cISIN C(12), cShareItem C(30), cItemDesc c(30)) INSERT INTO INVEST_BK VALUES ("XX1234567890", "Dummy", "") CREATE TABLE INVEST_IN (iPKey I AUTOINC NEXTVALUE 1000, cISIN C(12), dInDate T, iQuantity I, nValue N(10,2)) INSERT INTO Invest_In (cISIN, dInDate, iQuantity, nValue) VALUES ("XX1234567890", DATETIME() - 86400, 1, 100) CREATE TABLE INVEST_OUT (iPKey I AUTOINC NEXTVALUE 1000, cISIN C(12), dInDate T, iQuantity I, nValue N(10,2)) INSERT INTO Invest_Out (cISIN, dInDate, iQuantity, nValue) VALUES ("XX1234567890", DATETIME() - 86400, 1, 100) ELSE USE INVEST_BK IN 0 USE INVEST_IN IN 0 USE INVEST_OUT IN 0 ENDIF ENDPROC PROCEDURE Destroy() CLEAR Events ThisForm.Release ENDPROC ENDDEFINE ***** DEFINE CLASS grdBase AS Grid Top = 12 Left = 12 Height = 444 - 108 - (4 * 12) Width = 750 - (4 * 12) BackColor = RGB(0, 230, 230) RowHeight = 21 AllowRowSizing = .F. HeaderHeight = 21 AllowHeaderSizing = .F. DeleteMark = .F. ReadOnly = .T. Anchor = 15 Visible = .F. ENDDEFINE *****Enjoy.
MarK
RE: Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form.
Please show full video
https://youtu.be/RGmrxxQNiDo?si=oNouUYfOEtVhpnW3