How to populate grid rows with array 2

[SitesMasstec]

Hello colleagues!

How can I populate rows in a grid with an array of values, like in the picture bellow:

Thank you,
SitesMasstec

 

[Olaf Doschke]

Recordsourcetype tells you there is no way to populate a grid with an array, or even many.

You can define a cursor, though, and set controlsources to (Item(RECNO("alias"))) or (med(RECNO("alias"))).

Bye, Olaf.

 

[Mike Lewis]

And, having created the cursor, you can fill it with the array values as follows:

[tt]CREATE CURSOR csrGrid (Item I, Med I)
APPEND FROM ARRAY laMyArray
THISFORM.Grid1.RecordSource = "csrGrid"[/tt]

then as per Olaf's code for setting the ControlSources.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well,yes. Actually by using the controlsources the way I descibed you just use the line number to address array elements.

It's okay to simply append the array into the cursor and then disregard the array, it's harder to merge two array this way.

But you might actually like to display how arrays changes without rewriting code acting on the arrays to act on a cursor instead. There is a difference in displaying array elements vs copying them into a cursor.

Bye, Olaf.

 

[Mike Lewis]

That's not a problem if the grid is read-only. But if the grid is meant to update the array, you would have to copy the cursor back to the array (which you can do using [tt]SELECT * FROM csrGrid INTO MyArray[/tt]).

And in that case, you wouldn't need to explicitly set the control sources (assuming that the columns of the grid map one-to-one with the colunmns of the array). It would be enough to set the RecordSource.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You're misunderstanding. The way I proposed an update of the array would reflect in the grid, not the grid would update the array. You still don't see what I did, The controlsource I propose fill the grid cells with array items from the array itself, not with cursor record fields. And changing the array would change the grid (with refresh). Also: The screenshot shows two arrays are used to populate columns. That's not solvable with a single APPEND FROM ARRAY.

Bye, Olaf.

 

[Mike Lewis]

OK, I see what you mean.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SitesMasstec]

Hello colleagues!

I followed Olaf's advice in the first answer to this post and I got the Grid populated witrh data from a table, so at first my problem was solved.

But as I need to use array as I deepen in the whole problem, I searched in the Kilofox, and found a whole chapter dedicated to it, and as Tamar named the chapter "Grids: The Misunderstood Controls", and "What's New in Nine" book provided no further info about Grid, I've decided to abandon Grid and use Text controls instead:

Thanks for all your help.
SitesMasstec

 

[Olaf Doschke]

You most likely only got to the level of the grid, you have to set individual controlsources of the columns to get the arrays displayed:
Maybe you also fail on arrays scope, if your arrays are local, they only exist during the method you create them.

Here's a demo:

#Define cnSize  3
PUBLIC ARRAY item[cnSize]
PUBLIC ARRAY med[cnSize]

CREATE CURSOR crsGrid (c1 c(1),c2 c(1))
FOR lnCnt = 1 TO cnSize
    APPEND BLANK
ENDFOR
GO TOP

item[1] = "item1"
item[2] = "item2"
item[3] = "item3"

med[1] = "med1"
med[2] = "med2"
med[3] = "med3"

_screen.AddObject("Grid1","Grid")
_screen.Grid1.recordsource = "crsGrid"
_screen.Grid1.column1.width=100
_screen.Grid1.column1.controlsource = '(item(RECNO("crsGrid")))'
_screen.Grid1.column2.width=100
_screen.Grid1.column2.controlsource = '(med(RECNO("crsGrid")))'
_screen.Grid1.Visible = .t.

What's true is, this is using very much trickery to get this to work.
It's 10 times easier to populate a grid with a cursor, why don't you put your data into a cursor? It's the natural way to use VFP, not arrays.

Bye, Olaf.

 

[Olaf Doschke]

The most important question I have is: Where are your arrays coming from? If you create them manually as I did, it's even shorter to define a cursor and populate it's two columns with the data you want to show, as I already said and as my sample code shows you need a cursor anyway, with the number of records you want to show. There is no other way to get three grid rows but having three records in a cursor the grid is bound to.

I would, for example, use this, if I need to show data from an OLE Object array property that's controlled by the OLE control and would likely change during its usage to not need a cursor I would need to sync with all actions, that happen in the OLE array. As said it is quite some trickery, you do need a cursor with the right amount of records and need to override what the grid puts as column controlsources automatically when you assign the cursor as recordsource. Then you need to know about a glitch of the controlsource with expressions, that are not simply field names.

Bye, Olaf.

 

[SitesMasstec]

Hi, Olaf!

Where are your arrays coming from?

My array will come from a table, with just one record:

This table will be used to populate PART of a Grid in a Form.

The the user clicks on Med field, he/she will be expecting to type a value.
After the value is typed, a calculation will be performed and:
The contents of COLUMN 1 (Item(1)...,Item(2)...) of record (unique record) in the table INFONUT.DBF above PLUS the value entered by the user PLUS the value from the calculation performed must be copied in a entirely new record, for later visualization.


Thank you,
SitesMasstec

 

[Olaf Doschke]

Can you show a browse of that one record in infonut.dbf?
What you show looks as 8 records, obviously.
How do you create the item and med arrays from this one record? If you're using a query INTO ARRAY, please just make it INTO CURSOR. Also create both "arrays" as the two columns of that cursor and you have your datasource for the grid.

Bye, Olaf.

 

[SitesMasstec]

Olaf:

I am still creating the table, and it will be finished by tomorrow. Then I will post it here.


Thank you,
SitesMasstec

 

[Olaf Doschke]

You already have a structure in mind, don't you? From the screenshot of planned data it looks like 3 columns and 8 records. I wonder how you plan to put this into 1 record. Sounds like you are burdening yourself with problems you don't need to have.

I'll await tomorrow. Let's see, if we can find a simpler solution.

Bye, Olaf.

 

[SitesMasstec]

This is what I want to do:

In the Table INFONUT1.DBF there will be other more fields (not repeated like shown in the picture - INU_DESC01, INU_DESC02...INU_DESC10), in the record.

Similar to ONE customer record that has the fields:
[pre]- CUSTOMER CODE Field CUSTCODE
- CUSTOMER NAME Field CUSTNAME
- CUSTOMER PHONE Field CUSTPHON

- BUYS IN JANUARY Field CUSTBU01
- BUYS IN FEBRUARY Field CUSTBU02
- BUYS IN MARCH Field CUSTBU03
... ...
- BUYS IN DECEMBER Field CUSTBU12[/pre]

Thank you,
SitesMasstec

 

[TamarGranor]

Just want to point out that I'm not an author of KiloFox. Most likely, Marcia Akins wrote that chapter.

Tamar

 

[SitesMasstec]

Oh Tamar, I apologize for my mistake.

Anyway, I did not want to criticize anyone, but just to point out the complexity of the Grid control (according to the title of that Kilofox chapter...) and my difficulty to learn using it, of course.


Thank you,
SitesMasstec

 

[Olaf Doschke]

What you need is a table with fields (desc, qtde, medi, perc), and you need 8 records per "record", that's all. You don't design tables with repeated fields, repetition always is done by storing multiple tuples of data, named records, in a separate table with that substructure.

You should know the principle by now. If you want to store an order you don't have a table order with (ordernumber, customer, orderdate, product1, qty1, price1, product2, qty2, price2, product3, qty3, price3,...), you have two tables, an order table and a table for the different orderitems:

orders: ordernumber, customer, orderdate
orderitems: ordernumber, product, qty, price

Now you can a) have as many order items as you like, b) display all orderitems in a grid simply by setting its recordsource to the orderitems dbf and filter for ordernumbre=x.
You don't need any array for that, you don't need to turn fields of one record into multiple records or array elements.

If you recognize an order as a "record" and think this must go into one table record, then you haven't understood how databases work. If you design your data the usual way, you also can cope with how controls work much easier. I think you're haviong a wrong concept about how data must be stored free of redundancy. The way order and orderitems are designed, the same ordernumber appears multiple times in all data. That's not, what's called redundance because in the order tables it's the primary key value uniquely identifying one order and in the orderitems it's the foreign key, telling to which order the item belongs.

That's how you design such data, and in that way without any further ado items become their own records, easily listed in a grid.
And that#s not all, which is getting simpler. If you want to compute the average percentages of a record of your table structure you need [tt](Inu_perc01+Inu_perc02+Inu_perc03+Inu_perc04+Inu_perc05+Inu_perc06+Inu_perc07+Inu_perc08)/8[/tt] and it even get's complcated if not all 8 fields have a value and the average should be clculated of only 7,6,5,... values. Instead now you simply query [tt]SELECT Avg(perc) as avergaepercentage FROM yourtable2 WHERE key=x[/tt]. There is no SQL command or function calculating an average on a series of fields 1,2,3,4,5,6,7,8. Anytime you write a number in your field names, think about this, you actually want to store repeating things, and that's simplest in an extra table.

Everything is so much easier if you play by the rules. If you design data the way it's intended and supported by controls and query language. Then you also don't struggle and have no hard time with the SQL queries you need. All these database design rules are not there to make it hard on non-professional developers, all the programming tools languages are designed for that design of data, everything "falls into place", if you do it right.

Bye, Olaf.

 

[SitesMasstec]

My table with the repeated fields - 01 to 10 (desc, qtde, medi, perc) - is a table with just one record. No more records will be appended later.

It is a standard table about Basic Nutrition Facts, which will be used to save additional information typed by the user in the same fields in other tables.

This approach is for the sake of clarity and simplicity, even against the best VFP rules.

Thank you,
SitesMasstec

 

[GriffMG]

SitesMasstec,

Olaf doesn't mean it is against VFP rules, he means it might be poor normalisation, like storing a long company name in ten places
instead of having a table of companies and a ref code stored in ten places (not including the table of companies).

In practice you do see situations approaching the level of normalisation you have from time to time - it's not a crime, just needs
careful handling - and a structural upgrade if you suddenly MUST HAVE 14 months - the VFP secret police will not be hunting for you.

In some ways your approach makes report design easier - if you had, say, up to 100 items which you wanted as detail items, you could
find it difficult to control the page layout (it might drift on to two or more pages).

You could use functions to pull the right 01-02 items into the right places. So you might replace your text fields control text with

GetCustBy(2)

To get the value of CUSTBU02
If the code below is visible to your report.

Function GetCustBuy
Parameters m.Month
Private m.Month,m.Temp
m.Temp = "CUSTBY"+RIGHT("00"+ALLTRIM(STR(m.MONTH,3,0)),3)
return(Evaluate(m.Temp))

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​