Switching from memo fields to files -

[Otto]

Hello friends,

I want to get rid of memo fields in my programs.
I am convinced that data is better stored in files and the storage can be better structured.

I am now starting and will first export the memo fields into separate files.
I will create a folder for each database that has a memo field, named after the database without the extension.
For example: test.dbf and the files will then be in \test.




The program creates a copy of a DBF file (KUNDEN.dbf), adds an additional field, generates unique identifiers for the records, and saves memo field contents in separate text files.

Opens the new and the original DBF file.
Iterates through all records in the KUNDEN table.

Generates a unique Matchcode for each record based on names and customer numbers.
Saves the content of a memo field (telememo) in a separate text file if the field is not empty.
Inserts the record into the new KUNDENext file.




PowerShell script is an effective way to search files for specific content.
I then have the customer in the DBF file immediately through the filename.



I can open the memo files easily and quickly.
And PowerShell is fast!

[Otto]

Please note that with this approach I have the web or a desktop application running in a remote session in mind.
So the user does not have direct access to the file system.

[paquitohm]

,

[TimStone]

In the "old DOS days", before MEMO fields, we had a routine that allowed the storage of text in .dbf files, much as you describe.

When MEMO fields appeared, at first there were some instabilities that caused crashes. So, we stayed with the old system.

In time MEMO fields became incredibly stable. I have used them without hesitation for over 20 years, and never experience any crashes or loss of data. I also found they are much faster than the other methodology.

I use memo fields for storing text, which includes detailed descriptions. I never use them for storing data that I might want to search.

As an example, for service related invoices, I use many files:
A master workorder file to track dates, totals, and other universal data, plus keys to other unique files
A file for the clients, tied to a workorder by an account number key. The memo field contains notes about the client
A file for the serviced items, such as vehicles, with an appropriate key matching to the workorder record. The memo field contains notes about the item serviced
A file for parts, with each item having a matching key to the workorder
A file for labor which is keyed to the workorder. The memo field contains a detailed description of the work performed. There is one record for each task
A file for recommendations which is keyed to the workorder. The memo field contains detailed suggestions for needed services
A file for an inspection report

There are also other files that are linked, ie. for emails ( and the memo holds the text ).

One thing we found with the use of character fields for extended text is that records could get lost, losing data, or resulting in a description making no sense. It also requires two field in the record, one for tying it to the master item ( reservation, invoice, etc ) and another for the sequencing. This required counters which had to be updated. It could easily become problematic in multi user applications. A full record would have to lock the counters until its processing was complete.

I'm curious if people are still actually having issues with memo fields on current computers or is this just a "potential problem" for which you are seeking a better methodology.

Tim

[Otto]

Hello Tim,
thank you very much.
As you said, since I switched my installations to RDP, I haven't had any more problems with the memo fields.
But we don't store as much in the memo fields anymore. We have a DMS running in parallel where we can store any type of document in a separate folder for each customer.
For example, incoming emails are simply copied into the folder with drag & drop, as are outgoing letters.
Since I programmed an adapter for PHP to access DBF, I have dealt with the memo fields again. I think it is possible to switch to files without any problems. The NTFS file system has virtually no limits on how many files you can store per folder.
Now with the Powershell search - see these posts - you can quickly search all the files and then access the documents directly.
Whether memo field or files, the access, as far as I have tested, is equally fast.
Best regards,
Otto

viewtopic.php?f=3&t=44345&p=268773&hilit=powershell&sid=95c3369bfa491acdf1359f556fa4c85f#p268773

viewtopic.php?f=3&t=44596&p=270763&hilit=powershell&sid=95c3369bfa491acdf1359f556fa4c85f#p270763

[TimStone]

The difference is I don't store documents in their total firm. By using keys, I get very fast results for all the ways I may want to search. For example, lets look at parts:

I can look at a workorder and all parts are automatically linked.

I can do a report on parts sales, and they are all summed easily for each part number

I can ask for a history ( trend ) on sales for a specific part, and it's right there.

I want to know when a certain part was last sold, and I have it.

I want to know the time between when a part was acquired, and then sold and I have it instantly.

I want to group parts by type ( or use ), and the report is easy to obtain.

I do not use any dragging of a document to a folder. For example, an email record has the fields for From, To, CC, BCC, Subject, Date, Time, and Text. Thus, I can have an immediate view of all emails to a specific person, or from them. I can look at all of them for a specific date, or range, with no problem. I can also be looking at a customer record and immediately see all communications to and from them. The power of keys, multiple indexes, etc. is awesome.

Tim

[Otto]

Hello Tim,
We have been doing something similar until now.
But I am working on a new approach.
I see that we have different requirements on the web.
Everything that is additionally a "table of contents" must be synchronized with the original documents.
And that can be a challenge.
That's why I also try to remove the indexes and make the queries ad hoc.

The new SSD hard drives enable great speeds. As I said, it is a test and a vision.

Best regards,
Otto

[Euclides]

Hi Otto.
I am going in the other way... I use memo fields (CDX/FPT) wether I can...
I store complex arrays, store images, stor PDFs and lately I store those uncommon fields that some user have.
In this case , you have to define this fields in the DBF file, even when it have only one or two fields with data.
I developed the "poor man´s JSON". I define only the field name and te variable value.
Is very useful when your customer have two or more addresses to store or when his product has one name in the production and other in the invoice. (Anybody who works with industries can name more of them).
I have some old clients who are using FW 2.0 and they don't want to change the system (16 bits). (the owner is an old friend of me)
I even using this "poor man´s JSON" in that systems.
Just my 2K cents.
Regards, Euclides

[Otto]

Hello friends,
Conversion of memo fields to files - for each memo entry, we use a single file.
You can then access it directly.
Until now, you had to calculate the starting point via the offset and blocks in the memo field.
This also offers great advantages for full-text search; you can immediately find the guest in the document.
This is much more flexible and stable than memo fields!
You have to imagine it like a table of contents - memo fields are like a book, and files, as we are now doing, are like a loose-leaf collection.
It is so expandable. I create a folder for each guest. We can then create subdirectories and drag & drop documents into the folders.
If it is shown in the DBF that there is a memo field for the address, you first have to open the FPT, calculate the offset and block size to find the start of the text for the address, and then read it into a variable.
With the files, you are better positioned. In the DBF, it will show that there is a document, and you can open it directly.
For example, I can build in directory browsing, and all the documents in the guest's folder would be displayed there.
Best regards,
Otto