Only filled fields

[jorfasan]

I have a medical record database with a table of +200 fields for different input data. I would like to collect the filled fields and their content, in tabular form (one result per record). Such as:

patient_id w45680989

name John

surname Donne

visit date 01/01/2005

The empty fields should be ignored.

Is it done??

Thanks in advance,

Edited November 24, 2010 by Guest

[comment]

Can you explain in more detail what this is about? I cannot see why this would be needed; it sounds like your database is not normalized.

I am also not sure what you mean by "tabular form".

[jorfasan]

First, thank for replying.

Let's say you have to audit a given database and thus in a precise moment, you should establish (extract) which data is in each record.

The table has +200 fields, and in one record are filled with data 26 fields, in next record 37 other ones. I have to create a Field (in which I will implement an AuditLog) with the -let me say this way- original data. In such manner that I shall have the original data and after all the changes thanks to the AuditLog mechanism. But now I am concerned to create this original info.

Excuse me with the language -)

Tabular data means separated by tabs

Field: Value:

---------------------------

UserText Transaminasa

UserDate 13:10:06

StartTIme 17:10:06

EndTime 17:10:06

HTH

[TheTominator]

How do you want to handle fields with content that spans multiple lines?

Do any of your field names contain spaces?

[jorfasan]

Hi,

Yep, there are spaces and a few % caracters I am substituting with "porcent" string. The structure was done by the doctor himself and toke advantage of the flexibility that was in FM 4.1 and 5 famiily. I have to sustain the fp5 version a few months and I will convert it to v.11 and make a substantial rebuild of the system.

There are returns inside 8 or 9 fields (in the diagnostic field the writing is quite "textual" and quite few paragraphs are writen). I was simply thinking in getting the data (as there is) and start the new "life". On the other hand I don't see any problem in storing the ¶ caracter instead the real one.

Thanks for your interest,

[comment]

Let's say you have to audit a given database and thus in a precise moment, you should establish (extract) which data is in each record.

I am still not sure why I would want to do this, but to capture the state at a given moment I would probably do an export.

Otherwise you would need to create a layout with all the fields you wish to include (excluding, for example, the field where you will store the resulting "snapshot") and have a script or a custom function loop through the fields.

[jorfasan]

Hi comment,

Well, at the end I have hardcoded it. First, I analized the stuff with MetadataMagic in order to get all the inner info, with the extra of exporting the field names. And construct a small database -what else?- in order to construct the each line:

Case(not IsEmpty(Fecha de nacimiento) , "Fecha de nacimiento" &" "& DateToText( Fecha de nacimiento ) & "¶","") &

and compose the calcs that will audite (correct spelled??) each record of the two tables. The Quote function was very helpful to hardcode all the syntax.

For half hundred for one file and the 240 for the other file.

As you may imagine with the DateToText it is in the fp5 version but I was trying to play around in order to save some time.

Thanks guys.