Data dissapearing in Grid after set order command issued

[Steve-vfp9user]

Hello all

I have a form with multiple tables, the master table being CASMASTER.

The other tables on the forms Data Environment are linked to the master by the field MASTLINK in CASMASTER.

The form itself has a pageframe with grids on several pages displaying the relevant records linked to CASMASTER.

This works fine.

All the tables have a field called ACTDATE (D) which has an index tag called ACTDATE.

When the form is run, each respective pageframe page shows the linked records to CASMASTER so one page on the pageframe has expenses, another fuel etc.

As mentioned, each table shown on the pageframe has the field ACTDATE (D) but some of the records are not shown in date order for example (and these are UK dates):

18/06/2023
21/06/2023
14/04/2023
05/06/2023

What I am trying to achieve is to add a command line either on the pageframe1.page1 click event or the grids pageframe1.page1.grdCaactivity.column7.header1 so when I view the expenses page on the pageframe and click the ACTDATE will be in order, I have tried:

thisform.pageframe1.page1.grdCaactivity.SetFocus
SET ORDER TO ACTDATE
GO TOP
thisform.pageframe1.page1.grdCaactivity.Refresh

When I click the page in the pageframe or try the header1 option with the above command lines, the data shown in the grid disappears (no error messages are shown).

If I restart the form, the data is displayed so it’s definitely there and there is an index tag on ACTDATE

What am I missing or doing wrong here?

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Trento777]

Hi, Steve.

Have you ever tried using cursors as the grid's RecordSource, instead of the underlying tables?

As far as I can see, basically you used the DataEnvironment to do the following:

USE CASTMASTER IN 0 ORDER MASTLINK && It might even be ordered by another key/index field... or no field at all.
USE CASTMASTER_DETAIL1 IN 0 ORDER MASTLINK
USE CASTMASTER_DETAIL2 IN 0 ORDER MASTLINK

SELECT CASTMASTER
SET RELATION TO MASTLINK INTO CASTMASTER_DETAIL1, ;
[indent]MASTLINK INTO CASTMASTER_DETAIL2[/indent]

So, now we have the header/master table and the detail tables. And they are all related via the MASTLINK field.
So far, so good!

Detail tables will show the records which match to the header table. And the index field takes care of it.

Now, if you change the index field for the detail tables...

Let's put it this way: You have a table which contains full names of people, since you have fields for FirstName, SecondName and Surname.
But also, a table of Surnames (the header/master table).



So we have these records:

* Surnames table

Surname
JOHNSON
ANDERSON
WILLIAMS

* Names table
FirstName    SecondName    Surname

STEVE                      WILLIAMS
ALFRED                     WILLIAMS
JOHN         PAUL          WILLIAMS

And that's the way it shows when in the header/master table you look for the surname "WILLIAMS". The ChildOrder is "WILLIAMS" for the detail tables. And they are order by that field (Surname) to the header/master table.

So, you change the SET('ORDER') to FirstName (wich must have an index) for the detail table because you want them to show like this:

FirstName    SecondName    Surname

ALFRED                     WILLIAMS
JOHN         PAUL          WILLIAMS 
STEVE                      WILLIAMS

FirstName field in the detail table has no relation to Surname in the header/master table.

I hope I undertood well. And it's almost 10:00pm down here


Greetings from Guadalajara, Mx.

P.S. If wrong, PLEASE correct me

 

[Steve-vfp9user]

Trento777

Thank you for the very comprehensive reply. The form was made using tables so it will be difficult to go back and try cursors but with my limited knowledge, I understand what you mean.

I appreciate your post.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Chris Miller]

In very short: Using linkmaster feature you depend on the detail tables to have order et to the mastlink index. If you change that to order by ACTDATE you lose the relation and no records match, so none are shown.

You can't set two indexes, one for linking records and one for ordering. But instead you could also use SET FILTER and SET ORDER to achieve both objectives at the same time. Each detail table should have a SET FILTER TO mastlink = casmaster.mastlink (if I uderstand your field construction right, it's bad, by the way, you use the primary and foreign key fields for linking, not special fields).

Then anytime you pick a record in casmaster the filter is limiting the grids of detail tables to only show records with the sme mastlink field value as the main record picked. And you still can use an index to order these records by date or anything else, becuase the filter doesn't depend on an index.

Notice the linking is done just as an SQL join links data by using primary and foreign key, normally. So in the usually used example of orders and orderdetails all order records have an id (primary key field id or orderid) and all orderdetail records have their own primary key (doesn't matter for the purpose of linking/joining) but also point out to which order they belong by having their orderid field set to the same id as the main order record. And by that concept relations are also always based on foreign keys that relate records to primary keys of their parent table. And the linkmaster feature of grids also can link by the same fields and indexes used, not on separate extra fields for linking. If you have such fields it points out to me your database design doesn't feature the usual key fields, as they would be present anyway and can be used for the linking feature, too. Indeed you can define which fields are primary and foreign and setting a relation in the

It doesn't help here, this is a situation of wanting to both have the cake and eat it, too. Which foreigners usually don't get, but perhaps you already came across this saying.

Notice: Using the primary and foreign key fields that are part of a normal table design doesn't allow usage of two indexes for sorting and linking, too, it just spares you to have an extra mastlink field for linking.

Chriss

 

[Steve-vfp9user]

Chriss

It doesn't help here, this is a situation of wanting to both have the cake and eat it, too. Which foreigners usually don't get, but perhaps you already came across this saying.

I just hope one day I'm as knowledgeable as you then perhaps I can have my cake and eat it too. I am quite partial to a slice or two of Madeira cake.

Have a nice day

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[Chris Miller]

Well, nobody can have the cake and eat it, I also only can have the cake and use an index for sorting, when I eat a salad and use a filter for showing related/linked records. Because nobody can use set two indexes for sorting and relating at the same time.

There's one way out, in index that can serve for both linking and sorting, like a saldcake. And some people argue for it. In a way a pizza is a salad cake, also quite popular, indeed.

If you would want to sort by any order and still relate to the master table record this way, you'd need all combinations of indexes, though, and that's not a good data design.

Chriss

 

[Chris Miller]

Steve, is your problem solved at all? Could you work without the relation and instead use the filter Or does that not cut the mustard?

If you still don't know what Trento and I refer to just start your form, then go to the command window and type in

ACTIVATE SCREEN
SET

The first commmand is so further ? commands print to the screen rather than the form The second just opens the datasession window.

Look at the righthand side, you'll see there is are relations from the master table to all the detail tables. That makes the "filtering" feature work. You only need to pick one master record and all details are shown in the different pageframes.

Well, now look into how this is working: In the datsession window pick the master table, CASMASTER and then in the commmand window do:

? SET('RELATION')

That should print all the relations set from casmaster with its field name and then INTO the other tables. What is not mentioned directly is that this link depends on how the order is set in the detail tables. And that must be an index on the master id, the mastlink field in your case, or what normally would be each details tables foreign key relating it to the master table.

Also select any detail table and do

? SET('ORDER')

You'll see they are ordered by the index tag on their mastlink field. Because that's all necessary for the linking feature to work. It doesn't matter if you set this relation visually in the DE or use code, it effectively uses the same mechanism as code would do.

If you change the order to a datefield index, that means the link doesn't work anymore. The relation is still active and tries to find the CASMASTER.MASLINK value in the date field of the detail tables you sort by using the datefield index, and of course it doesn't find one record, you actually even have a datatype mismatch, if mastlink is n integer field, because that would mean seeking an integer in a date field. And that results in empty grids.

You can't do that, it's impossible.

If you change to using SET FILTER, there are some further things to do to make it work. Because clicking on the master doesn't refresh all other grids. It might not matter in your case, as activating other pageframes will do that, otherwise you'll need to address this in the master table grids afterrowolchange event.

Feel free to continue your thread, if you didn't yet got it working to limit the detail grids to show only details of the main record.

Chriss

 

[Steve-vfp9user]

Chriss

I resolved my issue in the following way by creating a "MASTER" table, then the following code (abbreviated):

* mmastlink=525 (as an example)

tempfile=SYS(2015)
tempfilet1=SYS(2015)
tempfilet2=SYS(2015)
tempfilet3=SYS(2015)

USE COSTMASTER SHARED
COPY STRU TO tempfilet+'.dbf'
COPY STRU TO tempfilet1+'.dbf'
COPY STRU TO tempfilet1+'.dbf'
COPY STRU TO tempfilet1+'.dbf'

* NOW DRAW THE RELEVANT DATA FROM THEIR RESPECTIVE TABLE

SELECT * FROM TABLE1 FOR MASTLINK=mmastlink ORDER BY ACTDATE INTO TABLE tempfilet1
SELECT * FROM TABLE2 FOR MASTLINK=mmastlink ORDER BY ACTDATE INTO TABLE tempfilet2
SELECT * FROM TABLE3 FOR MASTLINK=mmastlink ORDER BY ACTDATE INTO TABLE tempfilet3

etc, etc

* Now append the tables into the COSTMASTER table

USE tempfile+'.dbf' EXCL
APPEND FROM tempfilet1
APPEND FROM tempfilet2
APPEND FROM tempfilet3

etc, etc

The records in the table tempfile+'dbf' (COSTMASTER structure) are now in date order for their respective headings.

I created a report and this now works.

Matter has been resolved, thanks again to those who posted on this thread.

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[mjcmkrsr]

Hi Steve,

You may want to try a simpler solution (not tested) and forget the heavy workaround

m.MastLink = "525"

Select * from COSTMASTER ;
[indent]LEFT JOIN yourTable1 on COSTMASTER.MASTLINK = yourTable1.MASTLINK ; [/indent]
[indent]LEFT JOIN yourTable2 on COSTMASTER.MASTLINK = yourTable2.MASTLINK ; [/indent]
[indent]LEFT JOIN yourTable3 on COSTMASTER.MASTLINK = yourTable3.MASTLINK ; [/indent]
[indent]WHERE COSTMASTER.MASTLINK = m.MastLink ; [/indent]
[indent]ORDER BY ACTDATE ; [/indent]
[indent]INTO TABLE tempfile[/indent]

hth

MarK