Memo Field - ALINES() ??

[erp84]

In my table I have a memo field that looks something like this:

"1234",3.0,F,T (I have 400 lines of this in each memo field)

I only need to see lines from the memo field that contain T,T or T,F or F,T. What would be the best way to handle this, I would send to excel however I still need to maintain a relation to the main record for each line.

 

[Dan Freeman]

Since the subject mentions Alines() I'll assume that isn't what you're after. It only handles one record at a time, after all.

I'll assume you want to do this for the entire table at once. It can be done, but it's going to be some of the slowest code in your app. This assumes the formatting is consistent and the values you seek are always after the 3rd and 4th comma:

Select PrimaryKey, ;
    Padr(GetWordNum(theMemo,3),1) as Field1, ;
    Padr(GetWordNum(theMemo,4),1) as Field2

The PrimaryKey would let you link this recordset back to the original easily. I'll leave it to you to finish the query, particularly any filtering conditions you might be after.

If this is something you need to do regularly, particularly if it's a table of any size, you should really break those fields out into separately queryable values.

 

[Olaf Doschke]

This constitutes 1:n (in this case about 1:400) related records in memo fields instead of a 1:n related table. What's the reason? Of course this makes such queries unbearable harder. There's no reason to not normalize this data into two tables just because there are so many detail records.

Assume you had a separate table with a char, numeric and two logical fields (logic1, logic2) beside a primary and foreign key, then you could simply query SELECT * FROM Maintable Main LEFT JOIN Detailtable Det ON Det.foreignkey=Main.primarykey AND (logic1 OR logic2)

And this is optimizable with indexes on the key fields and the logical fields.

Bye, Olaf.

 

[Mike Lewis]

When you say you want the see lines that contain T,T or T,F or F,T, I assume we can summarise that to mean you want to see lines that contain at least one T. If that's right, the following code should do the trick. I haven't tested this, but I think it will work.

SELECT TheTable
SCAN
  ALINES(laMemo, TheMemo)
  FOR lnI = 1 TO ALEN(laLines)
    IF AT("T", laLines(lnI)) > 0
      * This is one of the lines that you want to see
    ENDIF 
  ENDFOR 
ENDSCAN

If the above assumption isn't correct, change the condition in the IF statement accordingly.

Where I have said "This is one of the lines that you want to see", you need to put own code to perform whatever processing you want on the relevant lines (display the record, insert it into a cursor, or whatever).

Now, having said all this, I have to agree with the Dan and Olaf. Ideally, you would redesign your database so that the data in question is properly normalised. However, I realise you might not have that choice.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[erp84]

Thanks all for the replies!

What I'm trying to do is extract the relevant memo lines and insert it into it's own table, so this is a one time thing. Here's what I hope to see in the new table.

NewTable
---------------------------
Part# (From parent table) - CustPN (From Memo) - QTY (From Memo) - T/F? (From Memo) - T/F? (From Memo)

Mike: Yes, I only want lines that contain a "T". I'll try tweaking your code, I just tried it and it didn't like the ALEN, so maybe I keyed something in wrong.

 

[Mike Lewis]

I just tried it and it didn't like the ALEN, so maybe I keyed something in wrong.

No, it was me who made the mistake.

Instead of this:

ALINES(laMemo, TheMemo)

it should be this:

ALINES(laLines, TheMemo)

Try that and see what happens.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Yes, Mike has already corrected his code,

So when you apply that fix and you arrive at the line "* This is one of the lines that you want to see"
Then you are in the scan loop at the record from which the memo is, so you have access to Part# and all the data from the memo line is in laLines(lnI). You can split this sting line again into single values, either using GETWORDNUM(), STREXTRACT() or again use ALINES with "," as the "line"-seperator. So ALINES cannot only split a multiline memo/text/string into lines, you can split at any seperator you specify as parameter. The single elements you get are also strings, but you can easily translate "T" and "F" to .T. and .F. of course, EVAL("."+Flagvalue+".") would be one way to do that, when Flagvalue is the "T" or "F" you extract from the line string.

So overall, you are in the process of normalising data already, then. I would perhaps just simply cut this into the esier steps to first simply add any line to a detail table and afterwards remove unwanted detail records by a simple DELETE-SQL query, if that makes any sense at all. It's of course easy enough to skip all rows not cointaining "T", but it would of course be fatal, if your first string column (in your example line the "1234" would also contain a T. Therefore I'd prefer to filter after the data is translated into the single field values you need.

 

[erp84]

Well I did get it working, sort of.

Olaf, I do like your suggestion of removing the records with a simple delete query. How would you suggest moving all my lines to a detail table if I go this route?