Summarize on a Memo field

[Farren]

I've created a Summarize operation. One of the fields that's being grouped on is a Memo field. The function works and does the grouping just fine. However, the resultant table has the grouped field of a Character type of length 1023. That leads me to two questions:

1) I need the Memo field preserved at the output of the Summarize. Can that be done? It ends up truncating the field.

2) I thought the maximum size for a Character field was 255. How'd it make a 1023 character field and how can I make one when I need more than 255 but less than a memo?

-Farren

[Stan Mathews]

Re: Summarize on a Memo field

1) I need the Memo field preserved at the output of the Summarize. Can that be done? It ends up truncating the field.

Can't be done with a summarize operation. You could write a script that processes the records in a table and concatentate the memo field contents.

[Stan Mathews]

Re: Summarize on a Memo field

2) I thought the maximum size for a Character field was 255. How'd it make a 1023 character field and

Some quirk of the operation process. A character expression can be 1023 characters and you are correct that a character field is supposed to be limited to 255.

how can I make one when I need more than 255 but less than a memo?

You've already discovered that.

[Stan Mathews]

Re: Summarize on a Memo field

One of the fields that's being grouped on is a Memo field.

That doesn't really make any sense. Grouping involves selecting some field or value that some records have in common such as lastname. Then you total/average/whatever some value across that group of records.

Grouping on a memo field would mean that several records had the same value in the memo field. If that's true there's no need to combine the memo contents.

Sorry, should say doesn't make any sense to me.

[Farren]

Re: Summarize on a Memo field

The table being grouped has, among other fields, a memo field and a number field. I group all of the identical memo fields and total the number field. Same thing you'd do for a character field, but I needed more than 255 characters. I'll even need more than the 1023 characters that it's outputting. I need the full contents of the memo field in the output table....

As for using this as a technique to create a 1023 character field in a table: pretty convoluted!

-Farren

[Tom Cone Jr]

Re: Summarize on a Memo field

Farren,

Sounds like you've got an unusual situation there. Neither my users nor myself could enter multiple records with identical memo field text entered by hand, at least not of the length you're talking about. If you're pasting memo field text in from other sources be careful to exclude non-printable control codes prior to pasting otherwise you're headed for trouble.

[Farren]

Re: Summarize on a Memo field

I agree it's unusual. You could think of it as a *really* long part number. It's consistent, even though it's long. And I'm going to have to write tons of code if I can't use the built-in summarize function.

Is there any sample A5 code in the public domain that does the same thing as the summarize function so I don't have to write my own?

-Farren

[G Gabriel]

Re: Summarize on a Memo field

It seems that you have a fundamental problem.
What is your understanding of a "memo field"?
Or to be clear, what is your understanding of a memo "Field"?

[Farren]

Re: Summarize on a Memo field

A memo field is what I resort to when I need to store more than the paltry (pathetic) 255 characters that A5 allows for a character field. I can't quote the details on how they're implemented in A5, since there's no satisfactory documentation for the product. Please provide a link to existing documentation that fully details how A5 implements memo fields and I'll gladly read it. Please also suggest how I can store and process data when I must store and process more than 255 characters, without using a memo field.

Here's what I think my real fundamental problems are: I picked a platform to develop in before I realized that it's very inconsistent in it's implementation, there's virtually no good documentation (and I've bought all the 3rd party books and read most of the wiki), and the "support forums" provide virtually no help but instead are full of unhelpful and extremely snarky comments from folks who want to let you know that they know what they're doing but don't want to tell anyone else how to do something.

My next fundamental problem will be selecting another product to develop this app and recouping the thousands of dollars I've spent on A5 licenses and hundreds of hours of development time.

FWIW, I've been developing database apps since the late 1980's. I'm not new to this game. I just hoped A5 would be a superior tool in my toolbox and chose it for this new project I'm on. I was wrong. Not the first time, won't be the last time.

-Farren

[G Gabriel]

Re: Summarize on a Memo field

Farren:
A memo "Field" does not hold the memo data.
A memo field holds a different type of data. It holds a link (reference) to the memo file.
The memo data itself is stored in a separate file, an fpt file.
FPT (Foxpro memo field tables) is a separate file in the same database and has the extension .fpt
An fpt file could hold any type of alphanumeric characters including null values and could hold an unlimited number of characters limited only by the space available in your hard drive.
Given all of the above now you could understand why it wont make any sense to group on a memo "field" since the said field only hold the link references to the actual memo files.
The docs might be sketchy... but if it is any consolation to you I used to think that the docs in alpha were sparse that is until I started working with other languages..count your blessings!
But to be responsive to your question of the thread I suggest you create a calc field that reads the values in the memo files and group using this calc field.

[Farren]

Re: Summarize on a Memo field

I appreciate your calm reply to my not-so-calm post. My feelings were not directed at you, but at the situation in general. They've been festering for months and I finally popped. Been feeling very much between a rock and a hard place on this project. I picked A5 as my tool of choice and I'm months into development and feeling like I've chosen a dead-end road. The lack of documentation is my biggest frustration and will certainly prevent me from choosing A5 for any future projects. As a case-in-point, I see that you didn't provide a link to any documentation but instead explained memo fields in your own words. I'm betting a cold Mt. Dew that there's nothing in any official A5 documentation that explains them in that level of detail, if at all. I've searched and found nothing.

As for memo fields -- that's pretty much how I assumed they worked. Basically like most other products I've worked with.

The strange thing is that you talk as if what I'm doing is utter nonsense. In fact it works exactly how I want it to with one exception. The tbl.summarize() function groups on my memo field just like I want it to. (And, BTW, there's nothing in the docs about NOT using a memo field...) The ONLY problem is in the output table that the function generates: the memo field gets grouped on the first 1023 characters and the field in the output table comes out as a character field of 1023 characters. (Which according to the docs, is an impossibility. One of the many inconsistencies I've run across and was complaining about.) It seems to me that the difficult part of the summarize function is working just fine -- it summarizes on a memo field. It just doesn't output it as a memo field.

I'll try your trick of using a calc field tomorrow, but I'm betting it'll output a character field that'll truncate the contents of the memo field.

Ok, I'm back.... I was about to type out that I'd write my own summarize function. However, as I recall from somewhere, A5's character variables only hold 1023 characters. If that's true, there's no way for me to read in the value of a memo field and process it as a string. So, I just spent the last 5 minutes searching the wiki for variable types to find out if that's true, and found nothing. Something as basic and fundamental as defining every variable type and what it's max/min capacities are isn't documented! If it's documented in the wiki, then it's buried deep or I'm using all the wrong search terms. If there is a variable type that can hold a memo's value and let me process it as a string, then I'll write my own summarize function. If there's not, then I'm in trouble. I hope my memory is wrong!

(just as an aside to make a point -- assume you're learning xBasic for the first time. You assume there has to be a function that converts a number into a string. You guess it might be str() like other languages you've used. You search the wiki for "str" and find nothing. No matches in the "page title" search and no matches in the "full text" search. So, you assume they've called it something else and start searching... about 4 hours later you find out that it is str() after all. I challenge you to search the wiki for "str" and find that function! It must be a reserved word for the web site's search engine or something..... Just one of the many frustrations I've been through trying to learn A5 and xBasic! BTW, it's at http://wiki.alphasoftware.com/STR+Function Makes me wonder what else is in the wiki that my searches aren't finding...)

Sorry for venting my frustrations. They're not directed at you personally and I appreciate your attempt to help. I'm just feeling very much like I'm backed into a corner...

-Farren

[Ted Giles]

Re: Summarize on a Memo field

Farren, couple of comments.

1) you have decided to use a memo field to hold effectively transactional data.
I don't believe that this was ever the intended use of a memo field. Just because you think you "can" use it doesn't necessarily mean you should.
There are quite clear restrictions on the sizes of calculated fields and variables.

2) using your logic that the memo field holds a *really* long part number and is consistent, then it is possible to have a field attached - also unique - which is linked to the memo field.

As in.
Part_no_id. [1-254 char/num]
..> Memo [unlimited]

But you must have explored this avenue.

3) not sure I agree with the snarky comments bit. A lot of us post examples of how to do something, and others help a bodies learning by suggesting pointers.

4) what "processing" are you actually planning to carry out on the content of the memo field? If it is simply summarisation, then I doubt that you will succeed even if you write your own function. You will need to break the memo into smaller chunks, but you could do this anyway and have char fields in the table which are populated by, say, chars 1 to 200, 201-400 etc. from the memo field.

5) I doubt that you will be able to shoehorn your design into Alpha, and if you know of another platform which will enable you to implement your unusual design, please share it.

6) str or str() in the Google box on the wiki brings up the desription. Have you taken advantage of the new Help Text?

[G Gabriel]

Re: Summarize on a Memo field

Farren:
I think we are (you are) going down the hard road.
First, yes you will see the first 1023 characters because alpha is using the referenced fpt files but only the first 1023 characters of each.
To output the full file, I suggested a calc field meaning assign a number to the contents of each memo, summarize by these numbers then use these numbers to recall the memo that matches this number.
No, a character variable could hold an unlimited number of characters (it will automatically turn into a blob variable). Limited only by your cache/RAM, Disk Space.
True, I didn't insert any links to the docs. The info I provided is scattered here and there which is typical of any docs. To have the info all encapsulated in one place, that's what you would find in books perhaps. I don't have any, but others might be able to suggest some for you.
I use the old fashion help files, not the Wiki (they are identical but I am used to the old one). I found str on first click. But rather than searching for what you assume the name of the function might be, instead search for "Functions" or "String" or "Convert"..a more broader search at first until you become more familiar with the lingo.
But for the big picture, what exactly are you entering into these memo fields? Can you provide sample data?

[Farren]

Re: Summarize on a Memo field

It's difficult for me to supply sample data as I'm under a very strict NDA. Just explaining what I'm doing in detail would require getting permission from my client.... I can say this: the stuff I'm storing in the memo fields could be considered a formula or recipe. It is consistent and thus summarizeable.

Is there any good/easy way that I could compute a hash of the contents of the memo field and then summarize on the hash value? Looking at this:

C Result = hmac_hash(C data ,C key [,C algorithm ])

I'm wondering if the "C data" input can be a blob variable -- i.e., greater than 1023 characters. I'll test it in a bit -- was hoping someone knew off-hand. Of course, the other trick seems to be reading the value of a memo field into a variable to then pass to this function. I've seen elsewhere in the forums that some folks are using the memo-to-file and then file-to-variable trick. Is that the only way to get a memo into a blob variable?

[Ted Giles]

Re: Summarize on a Memo field

Farren,
Ok, no real data, but you will be able to provide a scenario which sidentifies the issues.
I am at a loss to understand why you need to summarise the memo field. You can summarise the linking field to the memo field instead.
What purpose will summarising the data in a memo field serve?
Build an example of what you want to happen using freeform data and post it here, or give us a pseudo code decription of what you need to do.