1 to many grid Child data not dislpaying

[jimmygw]

I've been rewriting a program that my company uses ,and that I have been maintaining for the last 20 years, written in VFP7. I am rewriting this in VFP9. One of my forms is to create and track Purchase Orders. It uses 2 tables (header and detail). I join them with a common field "ordnum". I have a form with "PREV" and "NEXT" buttons, textboxes (for header data) and a grid to display details(child data). As I move thru the header table the associated detail should fill the grid. But it does not. the grid stays empty.

After days of pulling out what is left of my hair, I copied the project to a network drive and opened it with my VFP7 machine (windows XP). It worked just as it is supposed to. however on my VFP9 machine(Win 10) the child data DOES NOT display in the grid.

Just for reference, I have been programming databases since the FoxBase days but have spent most of my time in VFP7. I am NOT a professional programmer I just do this 'cause it is fun (most of the time).

Is this a VFP9 issue or a Win10 issue or a combination of the two? ( or maybe me?)

Thank you for any help that you all can give me on this.

Jimmy

 

[Olaf Doschke]

You mainly have three options to establish a 1:n relation and when you use two of them at the same time you can shoot yourself into the foot just as much as when you use none.

Look into the old project data environment and form. Either you'll find the two tables related and the fine line in the DE between the two tables defines a 1:n relationship OR you'll find some grid properties set in both the header and details grid. I've seen done both and that doesn't work well.

A third option obviously is SQL joins, but keeping two separate grids you could also fill in the details in a cursor within the header grid rowcolchnage events. There you could SET FILTER or SET KEY.

So actually four options. You better pick one of them, and only one. Find out what was used in the old project in DE including the lines between the tables (right click to see details about them) and then look into grid properties or grid code.

Oh, and I forgot the 5th option: There could be code at any place in the DE, load/init of form or grid that does what the visual lines of relationships also do: SET RELATION and SET SKIP to make a relation a 1:n relation.

When multiple things are done this can become contradictory and the result could be what you see: No records display in the detail grid. As there are so many reasons and ways it works, it's quite impossible to tell you what's your case.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TheProgrammerGuy]

You need to tell us how the relation is being set between the two tables.

 

[Olaf Doschke]

Yes, either tell is, or ask about any of the proposed solutions.

Just notice, when your database defines a foreign key and you add tables into the data environment of a form and you drag these tables onto the form to define grids, they already work as detail/parent.

Ti see that just create a new form, open the dataenvironment in the form designer (right click and pick data environment) then pick two tables from the VFP directory Home within Samples->Northwind directory. For example categories and products are related. The data environment will automatically establish a relation between the tables, a line going from the categoryid field of the categories dbf to the index on categoryid in the products table.

Now when you drag the two tables onto the form you get two grids. And they are already prepared to show all the tables columns. If you examine them you'll just see their properties for master/&child grid are not set, though.

And yet, start the form and when you pick a category the product list is showing the products of the category.

If you look into the data session window while the form runs, you'll also see on the right hand side the relation between the two tables is established. The order of the products workarea is set to the ctegoryid, and if you browse both tables you have the same navigation/filtering effect, simply be the relation. If you set the grid properties (watch out which properties for which grid) childorder, relationalexpr and linkmaster the same way as they are already set by the relation nothing changes, if you set this wrong you could get a blank grid.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[jimmygw]

Thank you, Olaf, for your reply.

As usual your response was educational and in this case got me on the right track. It seemed that the original detail table's index (ordnum) was actually the expression ALLTRIM(ordnum). After reading your post, and learning some things that I didn't know, I determined that the properties were all set correctly therefore it must be something else. So I looked at the tables. And there it was.

Thank you to ALL on this forum, it has taught me a lot over the years.

Jimmy

 

[TheProgrammerGuy]

Be careful indexing on ALLTRIM() as it could lead to pointing to the wrong records. If the key field is 4 characters (for example) the index should be 4 characters as well. And, of course, the RELATION, too. In other words, all characters in the key field should be used in the index and the SET RELATION command. Otherwise '123' would relate to '1234', but by using the full field size, '123 ' would not relate to '1234'.

 

[Olaf Doschke]

You can also see it from the other side, ALLTRIM() does not guard you from finding the non-trimmed values, too:

Create Cursor crsTest (cField c(4))
Insert into crsTest values("abcd") && recno 1
Insert into crsTest values("abc")
Insert into crsTest values("ab")
Insert into crsTest values("a")  && recno 4
Index on Alltrim(cField) tag cField
? Indexseek("abcd",.t.), cField
? Indexseek("abc",.t.), cField
? Indexseek("ab",.t.), cField
? Indexseek("a",.t.), cField
? Indexseek("abc ",.t.), cField
? Indexseek("ab  ",.t.), cField
? Indexseek("a   ",.t.), cField

It's recommended the index expression has a fixed length. That is, for character type expressions. It doesn't make sense to talk of the length of other types, you'll always think of a string representation of something having a length, but a date length does not differ whether you set century on or off, the field contains a full date value anyway and visual human readable representation has nothing to do with it.

If you have tables with common fields as primary/foreign key pair, then they should not just be similar but equal, char fields with same length.

Index on varchar will take the full width for each index node, by the way. And this from the help describes how ALLTRIM() has no real meaning in an index expression:

Visual FoxPro does not support variable length index keys. If you attempt to build an index with a key that varies in length, the key is padded with spaces.

That explains why seeking the values with trailing spaces also finds them in an ALLTRIM() index. ALLTRIM(field) would only change behavior for values with leading spaces.

And so that actually does not explain why an index with ALLTRIM(ordnum) would not work no matter whether or whether you not ALLTRIM() the relationalexpr value that is seeked. But it's strongly recommended to only use fixed-length index expressions. If you only have almost similar data because of fields with differing length or sometimes leading spaces or differing leading spaces due to different length, well, that shows how important it is those fields are really equal designed - same type and length for types with length. And you better ALLTRIM() text values before their storage to remove leading spaces instead of only doing so for the index. Unless you know why you want and need leading spaces in the data you store.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[jimmygw]

Excellent explanation as to why this was causing trouble and a lesson to all. Removed the ALLTRIM() and all works well now. Back to regrowing my hair. As to why it worked in VFP7 and not in VFP9... Well, I'm sure that's well above my level and I am not going to ponder it.

Thanks to all

Jimmy

 

[Mike Lewis]

Jimmy,

Now that you have a solution, could I ask a question. Do you have any particular reason to rewrite the application for VFP9? I would have thought that, if the app worked OK under VFP7, it would continue to work more-or-less the same under 9? It's true that there would be a few issues to think about (I'm thinking particularly of some of the SQL syntax), but nothing that would call for a complete rewrite.

This is not meant to be a criticism. I am only asking out of curiosity.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[jimmygw]

Mike,

I understand your curiosity.

The original program was written by a local software company over 20 years ago. When we purchased it, we purchased the program, source code, copy of VFP7 and of course all rights to it. I have been modifying it over the decades as my company's needs changed. Unfortunately, it was written in VFP7 ON TOP OF a "IAS visual Advanced (Objecttalk)" framework. I don't know much about these types of frameworks except that the VFP program runs directly within it. The all controls and objects in the framework are custom and (to my knowledge) only their libraries can be used. That is, a form looks and acts like a VFP form but it's their form and standard VFP form will not work within their framework. I assume that those of you who do this professionally know what I am talking about. Anyway...

The framework is licensed and recently something happened to our license (maybe a 20 year time bomb?) so we can only run the IAS part in "DEMO" mode. This gives us full functionality but is a pain for my users and I'm concerned it might stop working altogether. The original company that wrote our program is long out of business and I think probably the IAS company ( as I cannot find anything on the internet about them.) So, correcting the license problem doesn't seem possible.

Hence the rewrite.

I hope this makes sense to all of you. It's difficult to talk intelligently about something that one knows little about (the "framework" part).

Jimmy

 

[Mike Lewis]

Jimmy, that all makes perfect sense. So, you are not doing the re-write simply because you want to move from VFP7 to VFP9. You are doing it in order to free yourself from components (the framework) over which you have little or no control. And, given that you are rewriting it anyway, you might as well work with the latest version of VFP. That is all very sensible.

At least you have the source code and the rights to modify it. I've seen too many cases of developers declining to give the the source code to the client - or even failing to mention to the client that there is such a thing as source code - on the dubious grounds that this somehow protects their intellectual property - and then going out of business or disappearing from sight. And then, years later, people like me are called in to modify an application, and we have to explain to the client why it is so difficult to do that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

Is this the framework you're referring to:

https://softwareconnect.com/accounting/internet-accounting-ias-visual-advance/

That seems like a lot more than a framework. I have vague memories of hearing about this package back when, but nothing useful.

Tamar

 

[jimmygw]

Tamar,

That is it.

Yea, I'm not sure what it is. That's above my knowledge level. Regardless, I need to get free of it and stick with VFP on it's own.

Thanks

Jimmy

 

[TamarGranor]

If that's what you're using, I don't think getting rid of it is going to be easy at all. I think it's providing not just a framework, but virtually all of the business logic. As I understand it, that product is an accounting application that can be customized, not a framework over which you write your own code.

I think you need to find someone who can take a look at what you have and understand what's your custom code and what came with IAS Visual Advance.

Tamar

 

[jimmygw]

I have to remake all of the forms, most of the VFP code is cut and paste with very little change. Again, I am not sure what the IAS part did but feel very confident that I don't need it. So far all is working well. Thanks to all of you.