displaying memo field contents

[OldtTimerDon]

I have a table with six memo fields. Two of the memo fields require parsing and translation. Example: When reading a record, I want to display the contents of that field in a text box. The memo field requires translation after being parsed with alines. I have a routine that reads the record into a variable "myText."
Sample of myText: Certified Public Accountant, New York and New Jersey, Expires 12/31/2015. (Substitute chr(10+chr(13) for each comma so that each is on a separate line.)
Note: The memo field that contains this data looks like this: |CPA|NY NJ|12/31/2015|

My objective is that as the user moves through the database, a text box will display the contents of the variable named mytext. Retired for 10 years, I have been away from Foxpro too many years to resolve mt problem.

Seems to me that as I move the pointer in the database, I need a do command (set focus?) that activates the parsing routine and displays the record in a textbox.

Any ideas on how to accomplish my goal?

Don
Sun City, AZ, USA

 

[GriffMG]

Hi Don

How are you displaying this table?
If you are using a Grid, you could use the grids AfterRowColChange event to display the field...

	THISFORM.MyEditBox.Value = MyFunction(MyTable.MyMemoField)

Regards

Griff
Keep [Smile]ing

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

 

[Olaf Doschke]

You didn't specify yet, what version of FoxPro you are now using. Are you at VFP6,7 perhaps? Or did you upgrade to 9, or are you using Fox2.6?

First of all you can display multiline text in a textbox, when binding to a char field or variable, but even though you do that, you should rather use an editbox.

Instead of binding to a variable "mytext", I consider to bind to a form property, just because the variable scope would need to be public or private, and form properties are better suited for your case.

To create the form property use the "New Property" menu item from the Form menu.

To bind to the form property you simply set the editbox.controlsource = "thisform.mytext"

In the grid, there is the BeforeRowColChange and AfterRowColChange event. The latter would be the ideal place to trigger translation of the memo into the mytext property, eg translate |CPA|NY NJ|12/31/2015| into the multiline text Certified Public Accountant, New York and New Jersey, Expires 12/31/2015. However you do that, you should have your routine for it already. Something like Thisform.mytext = translate(memofield), where translate is a function returning the translation result.

Bye, Olaf.

 

[Mike Lewis]

Don, just to add to the advice you've been given ...

In general, if you are using a form to display the contents of a record, and if the form includes a way to navigate from one record to another, then you should ideally call a routine each time the record pointer changes. That routine would normally be a custom method of the form, although it might also be a function or a procedure.

It is in that routine where you handle any adjustments or translations you need to perform on the data being displayed. In this case, one of those adjustments is to change the pipe character to a CHR(13) + CHR(10). The easiest way to do that is to call VFP's STRTRAN() function.

So, the routine I referred to will contain this line of code (assuming you are diplaying the data in question in an edit box, which seems the most obvious choice):

THISFORM.Editbox1.Value = STRTRAN(THISFORM.Editbox1.Value, "|", CHR(13) + CHR(10))

I hope this makes sense.

Mike
P.S. To answer your question from the other thread, I am from Edinburgh, which is the capital of Scotland. I know southern Arizona a bit, having been to the Phoenix / Tempe area a few times. I wonder if our paths have ever crossed.

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[OldtTimerDon]

Olaf, Griff, Mike
Thank you so much for your assistance in rejuvenating old brain cells...they haven't all died!

I am a beginner at using VFP 9. 10 years ago, my experience was with VFP 5 or 6. So, I have things to remember and things to learn...

Mike, as a matter of info, I moved to Arizona from Montana two years ago. For the first 67 years, I was a New York/New Jersey resident. I got into computers by buying a Texas Instruments 99/4 about a year before IBM introduced the PC. Most of what I learned was by reading manuals and making mistakes. I was 57 when I was first paid as a developer and 67 when last paid for my skills. Enough history...

I have added an editbox to my main form. I have not been able to add the property "Thisform.editbox1.mytext" to the editbox form. I did get "Thisform.mytext" as a property of my main form. Am I doing something wrong?

OK, I think I comprehend how to read and display the data stored in thisform.mytext. Seems to me I need to write a procedure that activates the translation process when the record pointer is moved. Would that be stored in "thisform.transfer".

Also, I am considering a new form for adding/editing data in memo fields. There are eight essential questions to be answered. Four of them are check boxes. Based on responses, the data is then translated (coded) and replaces either empty field or prior contents. Am I moving in the right direction?

It's good that I don't have to bill someone for my time. While I'd love to supplement my pension, my chances of winning the lottery are better, especially if I buy a ticket.
Don

 

[JRB-Bldr]

If the Memo field content for each record is as simple as you indicate
The memo field that contains this data looks like this: |CPA|NY NJ|12/31/2015|
I wonder why its in a Memo field at all instead of a Character field

Regardless, depending on how much data you needed to parse, you could create an 'already parsed' cursor in the Form's Load Method and then display that Cursor in a Form Grid.

NOTE - If there were a LOT of data to parse, then it might not be worth while waiting for the routine to complete prior to showing the Form, but if it weren't too much maybe it would be worth considering.

While the following example is pretty simplistic, something like the following:

SELECT SPACE(30) AS Type,;
   SPACE(30) AS Location,;
   CTOD('  /  /  ') AS Expires,;
   LEFT(MemoData,60) AS MemoData;
   FROM MyData;
   INTO CURSOR GridData READWRITE

SELECT GridData
REPLACE ALL Type WITH 'Certified Public Accountant';
   FOR 'CPA' $ LEFT(MemoData,5)
REPLACE ALL Location WITH 'New York and New Jersey';
   FOR 'NY NJ' $ MemoData
REPLACE ALL Expires WITH CTOD(SUBSTR(MemoData,RAT('|',MemoData)+1

Then the Form's Grid would use:
RecordSourceType 1-Alias
RecordSource GridData

And each column would have the appropriate Header Caption.

Alternatively if you want to do the parsing as you originally indicated, you could use the existing AfterRowColChange method of the Grid to parse the data and put the results in your 'results' Textbox.

Each time the record pointer moves in the Grid, the method would execute.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

>Thisform.editbox1.mytext

I never talked about adding a property to the editbox. In fact both Mike and Griff skipped that property, as you can also directly write to the editbox.value property to display the translated text. I was just picking up your idea to read "the record into a variable "myText."", a variable typically is bad because it get's out of scope after a method, meaning it will be released and vanish. A form property stays as long as the form runs. The editbox.value property as a native property of course also exists while the editbox exists. There is no real need to make an additional step via a property. Using the indirect way via Controlsource to the Value property just makes it similar to binding to a table field. And instead of a simple text property you could also store an object at a form property, having properties itself, which you can generate by SCATTER NAME loRecordobject, in this case with MEMO clause to also scatter MEMO fields to properties. Back in VFP5/6 you only had SCATTER MEMVAR instead, so this is new. This will give you something like a buffered record, to which you write from AfterRowColChange by scattering to it, can then translate within the property corresponding to the memo field and display that in the editbox, also doing the reverse step before gathering the data back into a record, which could be done in the grids BeforeRowColChange.

>I did get "Thisform.mytext" as a property of my main form. Am I doing something wrong?
No, as already answered Thisform.mytext is fine, you can always only add properties and methods to the central upper level object you work on, sub object can only be extended by first designing a class, extending that previous to putting it on the form, but in this case this isn't needed at all, a form property is fine and you could even skip that and directly work on value.

I wonder why you would want another property form.transfer.

Bye, Olaf.

 

[Mike Lewis]

Seems to me I need to write a procedure that activates the translation process when the record pointer is moved.

Yes, that's exactly right. Ideally, the "procedure" should be a custom method of the form, although that's not vital.

Going one step further, the usual approach is to assign the ControlSource property of each of the controls to the corresponding field in the underlying table. Immediately after you move the record pointer, you call the form's Refresh method. That way, the controls will always reflect the current value of the fields. Plus, if the user edits the controls, those edits will make their way back to the fields (if that's not what you want, make the controls read-only).

If you do that, the only reason you need the custom method is to handle the exceptional cases, where the control displays something other than the contents of the field. That is in fact the case here, as you need to parse the memo field and split the various components into separate lines. So, within the custom method, you would call the STRTRAN() function, which I showed you earlier in this thread.

Once you have the custom method in place, you would call it each time you move the record pointer, immediately after the call to the Refresh method.

We haven't discussed what happens if you want to allow the user to update the memo field. But I suggest you get the basic stuff working first, and we can come back to that later.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[OldtTimerDon]

I confess to providing an inadequate description of my problem. I am new at using Tek-tips and tried to simplify my efforts.

Don
Sun City, AZ

 

[OldtTimerDon]

I fear that my attachment to my previous response did not get posted because of my inexperience with this site. This is what I was trying to convey:

*Movie database
I maintain a database for a church that shows movies several times a week to the public without regard to whether or not they are parishoners. The fields in the database include: title, year released, run time, and genre.There are memo fields for storyline, cast and facts.
The memo fields contain a capsule description of the storyline, Cast (three or more actors), and facts. Facts include one or more directors, significant notes, etc. . Also included are memo fields titled AFI and Oscars

The AFI memo field is structured as |list code|rank| where list code represents the list the movie appears on (ex: Best Thriller, Best Comedy, All-Tme Best, etc. and the ranking on that list.

The Oscars field for the for the movie "All the King's Men" is coded as "|bp|am|Broderick Crawford|sf|Mercedes McCambridge| whwich is translated and stored to a variable "mytext" as follows:
Best Picture
Best Male Actor
Broderick Crawford
Best Supporting Female Actor
Mercedes McCambridge

The procedural code to create "mytext" is:
mynloop=ALINES(myarray,oscar,"|")
myText=""
FOR I=2 TO mynloop
IF LEN(MYARRAY(I))=2
DO CASE
CASE myarray(I)="bp"
myarray(I)="Best Picture"
CASE myarray(I)="bd"
myarray(I)="Best Director"
CASE myarray(I)="am"
myarray(I)="Best Male Actor"
CASE myarray(I)="af"
myarray(I)="Best Female Actor"
CASE myarray(I)="sm"
myarray(I)="Best Supporting Male Actor"
CASE myarray(I)="sf"
myarray(I)="Best Supporting Female Actor"
CASE myarray(I)="bm"
myarray(I)="Best Musical"
CASE myarray(I)="fa"
myarray(I)="Best Animated Film"
ENDCASE
ELSE
myarray(I)=" "+myarray(I)
ENDIF

myText=myText+myarray(I)+CHR(10)+CHR(13)
NEXT I

Naturally, some films have never won an Oscar in the primary categories or made an AFI list.

The database has been in use for several years. Associated with the database are tables that relate to viewing info such as when rented from Netflix or Blockbuster, date viewed and number in attendance as well as freewill donations received. Note: Some viewers actually place a contribution in the bucket at the entrance to the activities room.

The database is increased by people's suggestions and the suggestions of both Netflix and Blockbuster. Some of the films added end up listed on a "Wanted" list since they they may be on a long waiting list.

At this point in time, the movie table has approximately 1000 entries.

The info is also used to print a monthly schedule of the films to be viewed with data about the film (title, cast,awards, etc.)

 

[Olaf Doschke]

OK, and the techniical consequence still is what we discussed so far. All you need to change in your routine is to put this code into a function body and in the last line return the mytext value, to be able to call it. eg as follows:

Function Translate()
   Lparameters tcOscar
   mynloop  =Alines(myarray,tcOscar,"|")
   myText=""
   For I=2 To mynloop
      If Len(myarray(I))=2
         Do Case
            Case myarray(I)="bp"
               myarray(I)="Best Picture"
            Case myarray(I)="bd"
               myarray(I)="Best Director"
            Case myarray(I)="am"
               myarray(I)="Best Male Actor"
            Case myarray(I)="af"
               myarray(I)="Best Female Actor"
            Case myarray(I)="sm"
               myarray(I)="Best Supporting Male Actor"
            Case myarray(I)="sf"
               myarray(I)="Best Supporting Female Actor"
            Case myarray(I)="bm"
               myarray(I)="Best Musical"
            Case myarray(I)="fa"
               myarray(I)="Best Animated Film"
         Endcase
      Else
         myarray(I)=" "+myarray(I)
      Endif

      myText=myText+myarray(I)+Chr(10)+Chr(13)
   Next I
   Return myText
Endfunc

You may also add a form method named Translate and call it at the place you want to display the translated Oscar Text from the table.

And that means instead of bindinfg the editbox to Oscar or mytext you do

Thisform.Edit1.Value = Translate(Oscar)

Or if you make Translate a form method

Thisform.Edit1.Value = Thisform.Translate(Oscar)

The place to do this depends, one place is the AfterRowColChange of the grid control listing all the movies. Another place to call Translate would be a skip forward or backward button click.

Bye, Olaf.

 

[Olaf Doschke]

..in short that's making use of the very simple EVA principle "Eingabe, Verabreitung, Ausgabe" (a german acronym), which means Input, Processing, Output. Input is the oscar field, processing means translating, output is the resulting text.

And I'm spotting a slight flaw, to create text lines from the array you should add CHR(13)+CHR(10), not the other way around. It's CRLF, and CR is CHR(13), LF is CHR(10). It might also work the other way around. The editbox might even only need CHR(10) or CHR(13), but in a text file you generate in a text editor like VFPs PRG editor or notepad, you get CRLF.

Bye, Olaf.

 

[Mike Lewis]

Don, I know this probably won't be of much use at the moment, but there is a very strong case here for normalising your data design.

Instead of the memo field structure that you described, it would be better to have a separate Oscars table. This would contain one record for each award. Each record would contain three fields: a pointer back to the movie; the award code (e.g. "bp", "sf"); and the name of the person who won the award (if any).

This would not only avoid the need to parse the Oscars data, it would also allow for future expansion (such as new award types), and would open the way to many querying and reporting possibilities that would otherwise be very difficult.

Going further, you could generalise the table to cater for all awards, not just Oscars. You could also have a third table, which would act as a lookup to the award type (to tell you, for example, that "bp" means "best picture", so that you wouldn't have to hard-code that text).

I appreciate that this is an existing table, and you probably have no control over its design. But it's something you should keep in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

That's a good idea, Mike.

As as I understand the daata comes in that form from eg Netflix or Blockbuster. If I understand this correct, there is nothing Don can do about the incoming data. He could normalize it first and then just display the data, but if it's just these few translations I'd say a translation on the fly also is no big problem. Especially if the intention is not to add to it. Even if, at some point theree also would be an inverse step to put the information back into that format.

A more general idea in regard to getting more information about any movie is, you could use the webbrowser control in Foxpro and display pages of the IMDB (internet movie database). On the page

http://www.imdb.com/interfaces

they describe how you can get their data for noncommercial usage, as your case is.

Bye, Olaf.

 

[Mike Lewis]

Interesting. I have been using IMDB for years, but never knew they made their data available for off-line non-commercial use. I'll have to think up some way of taking advantage of that.

Talking about non-commercial use, I'm slightly surprised that Netflix and Blockbuster allow movies to be shown to an audience in this way. As far as I know, all DVDs (and VHS tapes) that you buy or rent are only licensed for showing in your own home to your immediate family and friends. Is it possible for, say, a church to get a special licence that allows them to be shown to members of the public, I wonder.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Most probably not the latest block busters. There are a few Revival house cinemas in Hamburg that Show old movies scheduled for single days, this is somewhat cheaper. Today they even also Show more recent movies, limited to eg art house. A program for churches sounds plausible.

Bye, Olaf.

 

[OldtTimerDon]

You gentlemen are really refreshing my brain cells. I am involved in a few things other than this project but VFP has stimulated my rejuvenation. It is challenging because I don't have copies of projects I worked on when gainfully employed nor the textbooks I had in my library. I miss no longer having my Tamor Granor books and I think it was Foxpro Advisor magazine. If I recall correctly, I had an excellent book by Miriam Liskin(?), I beieve it was on VFP3.

I very much like the idea proposed by Mike Lewis of "normalizing" the data into a separate "Oscars" file that can be maintained easier and used for other reports (such as a listing of best supporting male actors by year). I began construction of such a file today and discovered that there were a number of films that were not in my primary file of films that were either previously viewed or being sought. I am thinking of building a form that would display the Oscar year, the film that won Best Picture, the winner of Best Director, and winners of Best Male and Female Actors, Best Supporting Male and Female Actors, Best Music, and Best Animated Film. Note: I have queried IMDB for abstracts of data many time but did not know I might be able to download the info. All my data has been input by my typing or copy and paste selected text.

So, by following Mike's suggestion, I am creating an "Awards file" with fields catlist(c8),gyear(I4), gid_code(c4), glib_id(I4), myoutput(c20).
Ex: 'OSCARS,1947,bsm,81051,Edmund Gwenn' Oscars is the listname, gyear is the award year, 81051 is the id assigned to the movie, and Edmund Gwenn won the award for best supporting male. This structure makes it easy to add new awards like "Best Cinematography" and other lists like the AFI lists and rankings of the Top 100 Movies, Thrillers, Comedies, etc. (A code file the translates 'bp' 'bd', bsm' has also been constructed.)

The editbox display of this data will be based on the movie table being related to the awards table. The routine for calling it will be a Do While lib_id=gid_code then the dat will be added to the variable mytest with CHR(13)+CHR(10) added to each line. (Thanks for the correction, Olaf.)

Should I have a field in the movie table called awards (replacing the Oscars memofield) that contains only a true/false response to determine whether Olaf's translate procedure is to be called?

It's after 10 pm in AZ right now. As an ancient, I gotta go to bed. When I work late, the brain does not want to go to sleep. Ta Ta!
Don Lawrence

 

[Olaf Doschke]

OK, I thought you had to live with data in that form. In this case I'd convert all data, it's just a one time conversion, and todays disc space doesn't need storage in such a compacted form.

>Should I have a field in the movie table called awards (replacing the Oscars memofield) that contains only a true/false response to determine whether Olaf's translate procedure is to be called?
So I answer this question with no. While creating the data for your detail table(s), the translation function could help you, but you can also sort in awards via their abbreviated notation as code.

Mike's idea was to keep the award code as a field of your oscars detail table, I suggest you make the award types a separate table and make a relation to that list, too.

Your table layout would then be "gyear I, oscartypeid I, glib_id I, name C(30)", I wouldn't add the catlist field to store different infos about a film you would rather have different tables, this one is only to store names (of actors or director or musical score composer) for oscars (if it's not an oscar for the whole film). The name field could also be a foreign key to an actor table with a list of actors, which prevents mixed cases of writing the name.

The idea of having a category in catlist isn't wrong, myoutput then makes it easy to put any line of text there, not only an actor name- You might make that a memo field and put in a short story line for catlist = "STORY" and later can filter for this or "OSCAR" anyway. That's also fine, but it's not a pure dedicated structure for normalised data, then. Anyway it's your decision how far and how strict you go about the normalisation rules.

Bye, Olaf.

 

[Mike Lewis]

Should I have a field in the movie table called awards (replacing the Oscars memofield) that contains only a true/false response to determine whether Olaf's translate procedure is to be called?

I would vote against that. It's not really needed. It's easier just to call your translate routine regardless. If it doesn't find any child records in the awards table, it simply won't do anything.

Also, adding the true/false field to the movies table introduces an unnecessary dependency, and means that you have the extra step of updating that field whenever the child records are updated.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

> While creating the data for your detail table(s), the translation function could help you

To make the point more clear. It should beeasy to make the transition fom your current data to normalized data in one go. After that you'll never need that translation again, unless you get data this way as your starting point.

Bye, Olaf.