Grid Order Problem

[SteveMacPSU]

I have a grid on a form that shows child records from another table. The child table has an index on a numeric, 4 length field that relates back to the parent table. I have them both set up in the data environment and things work fine. Now I realize that I want to show the child records in date order. If I change my child table index to str(appno,4) + dtos(date) descending of course they don't list in the grid any more. Any suggestions?

Steve

 

[JRB-Bldr]

Before you configure the tables and their relationship to appear in a Grid, have you set things up through the Command Window?

By working with setting up the Indicies and the Relationships in the Command Window and observing the behavior of the records in Browse windows, you can determine what is needed before attempting to get it to work in your Form's Grid.

Once you introduce the Date parameter into the Child table's index expression, it is most likely no longer an EXACT match to the Parent's Relation expression.

In circumstances like this that I sometimes have to use a SET EXACT OFF in order to get my non-EXACT Relations to work as expected.

* --- In Form's Load Method ---
ThisForm.SetExact = SET("EXACT")
SET EXACT OFF

<Do Variety of Form Stuff, like setting up table relations, etc>

* --- In Form's UnLoad Method ---
mcSetExact = ThisForm.SetExact
SET EXACT &mcSetExact

By testing things interactively via the Command Window you can determine if this will work for you.

Good Luck,
JRB-Bldr

 

[white605]

I used:

inde on case_no+DTOC(date,1) to ...

where case_no is a 6 character field, and date contains the transaction date to correctly order the transactions by date.
The parent table contains case_no and i set the relation in the data enviornment.
Although this may present problems in the future it has worked so far in a grid for me.
wjwjr

 

[SteveMacPSU]

Thanks for the input. I think that White you are a little closer to my specific situation. The one difference is my main realationship between the tables is a numeric field. I think that if I changed the appno numeric field to a character field in both the parent and child table and then indexed the child table on the appno (chr) + dtos(date) it would be fine. If that is the only way to get that to work I can do that.

 

[JRB-Bldr]

You say that the Parent table contains the Transaction Number which I assume you are using in your Relation expression into the Child table.

I guess that you are not using a DATE in the Relation expression, merely the Transaction Number.

If that is so, then you will not have an EXACT match with the Child's Index Expression. It is not an issue of the field type being different since you address that by using the STR() command in the Child table index expression - that will handle the differences.

If your expressions (Relation & Index) are not Exactly the same, that is why I suggested setting the SET EXACT OFF and attempting to set up the same relation. Then view the results.

Another approach would be to create a new 'Child' table/cursor from a SQL Query in which the records were ordered in a manner consistent with str(appno,4) + dtos(date) descending.

Then you would build an str(appno,4) (no added Date required) Index on that new 'Child' image and view those records in your Form.

Good Luck,
JRB-Bldr

 

[ramani]

Hi

You can use a view as your child table with the view itself being a filtered one for the filter value equal to the master table field value. The view can be indexed on your date field.

This can solve the problem. You can update based on view.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[SteveMacPSU]

Thank you one and all for your help. I decided to make the common field a character field and then I can index all the child tables with that field and whatever other field I want to order by in the grid. It might not be the best method, but it is the one that I understand the best. I certainly appreciate the responses and will be back soon. I am a converted FoxPro 2.6 DOS programmer and things are just a little different. This forum always seems to find an answer for me.