"Transpose" Fields into records

[panchristo]

I am currently facing this issue:

I have some tables that contain an awful lot of fields (~150 each), that when I need to view as spreadsheet it takes forever to scroll sideways.

So, I thought I could separate each field value as a separate record (somewhat similar to data tagging) in a separate table where each record gets a "tag" to identify its source field, the value of the field and the name of the record it has come from.

I guess I have to script the process, but I am afraid that due to my limited experience I will produce a too lengthy script or mess it up with loops.

Can anyone help me on how to do this?

Thanks in advance!

[comment]

I don't quite see how this will help the scrolling issue. But even supposing that it would, you should never subordinate your data structure to user interface requirements.

[panchristo]

I know, you 're absolutely right in every word. As I said, I would do this in a separate table and leave the data intact.

As for the result I would "convert" horizontal scrolling to vertical, which would help me hugely in some tasks I have to do later (no need to refer to).

Do you have any ideas on the transpose process?

[comment]

And how will you keep the two tables synchronized?

I am probably still missing the point here, because I don't see why you cannot simply use a list layout, with the fields arranged vertically. And of course, you could eliminate scrolling altogether by grouping your fields in a tab control object.

---

This is assuming that all these fields should actually be in a single table. It is quite rare to have an entity that requires ~150 attributes to describe.

Edited August 20, 2010 by Guest

[panchristo]

Ok, let me a be more detailed (hope doesn't get you bored)

One of my tables for example is a "Bone inventory form" where there are about 150 fields for each human bone found in each human skeleton (every record of the table) and the user selects a "state of preservation" value.

Transposing these data would help to export them in "readable form" (single A4 pages - not too wide as in layout) to produce primitive data tables for a PhD of a friend that will be attached to the appendix.

In a case where the fields would be fewer I could have used the following structure:

Table1: BoneInventory

pkSkeletonID

GFBoneName1

GFBoneName2

GFBoneName3

GFBoneName4

GFBoneName5

Table2: BonePreservation

pkBonePreservationID

fkSkeletonID

fkBoneName

BonePreservationState

and then call Table2 through Table1 by a relationship like the following for every BoneName:

pkSkeletonID < fkSkeletonID

BoneNamexx = BoneName

But that would mean that in my case I would have to manage 150 relationships, only for one table!

As for the Synchronization issue, the transposed table would be a temporary one just to export the data to excel

[comment]

let me a be more detailed

I wish you would, because I am still not sure I get it. Scrolling/exporting issues aside, I think you have a serious flaw in your data model. It looks like you should have a table of Skeletons, a table of Bones (or BoneTypes) and a join table where each actual bone is a record (this is where the preservation state would be recorded).

[panchristo]

a join table where each actual bone is a record (this is where the preservation state would be recorded)

I haven't followed this kind of structure because I want to make sure every bone state is reviewed (do not allow empty values). How would I make this possible in this case?

[comment]

Are you asking how to create it, or how to convert what you have to it? I cannot answer the second one, because I am not sure what exactly you have now.

[panchristo]

I currently have a structure like this:

Table1

pkSkeletonID

SkeletonName

Table2

pkInventoryID

fkSkeletonID

Bone1

Bone2

Bone3

...

Bone150

as for your question,

Are you asking how to create it, or how to convert what you have to it?

I am asking if you know any way to keep the restrictions needed (make sure every human bone is reviewed for its state) if I implement it the way you suggested (via a M2M JOIN table)

[comment]

Does every skeleton have all 150 bones?

[panchristo]

Let's say normally every human skeleton has 150 bones. What I need to be sure of is that the person who completes the inventory form checks whether each of the bones exists or not, and not just be able to complete some of them and leave the rest unreviewed.

[Vaughan]

That can be done. In fact doing it is EASIER if the bones are related records and not fields: just count the number of records (or count the records for a field that says "yes").

[comment]

How do you do it now, with those 150 fields?

[panchristo]

How do you do it now, with those 150 fields?

Some comments ago:

20-08-10 01:03 PM - Post#363585

In response to comment

I currently have a structure like this:

Table1

pkSkeletonID

SkeletonName

Table2

pkInventoryID

fkSkeletonID

Bone1

Bone2

Bone3

...

Bone150

as for your question,

* Quote:

Are you asking how to create it, or how to convert what you have to it?

I am asking if you know any way to keep the restrictions needed (make sure every human bone is reviewed for its state) if I implement it the way you suggested (via a M2M JOIN table)

[Vaughan]

Here is a thought: do you want to record which bones exist or which bones are missing?

I'm thinking to myself that checking 150 items off would get tedious really fast, and the poor user will do everything in their power to minimise their pain (users can be extremely inventive).

Perhaps the data structure could be changed to record either which bones were missing, or which existed. So if there were 149 bones, record which 1 was missing. If there was 1 bone found, record that fact and not that there were 149 missing.

There would have to be a table that recorded all the bones in the skeleton that would be set up ONCE. As Comment implied, if there were multiple skeleton types this could also be accommodated.

The user interface would allow for the type of skeleton to be selected, then which bones were found or missing.

With the related record structure you could incorporate photos of the skeleton and individual bones into the database. Imagine doing that with the flat-file structure you now have there would be over 300 fields and no way to associate the photo with the bone.

[comment]

Please assume I read what you wrote. You didn't say what measures you took to make sure "that the person who completes the inventory form checks whether each of the bones exists or not". Are you validating the fields for not empty, or what?

[panchristo]

@COMMENT:

Maybe I didn't make myself clear before:

I haven't followed this kind of structure because I want to make sure every bone state is reviewed (do not allow empty values)

- indeed I use a "not allow empty" policy.

@VAUGHAN

I know it sounds too tedious, but this task is performed by filling paper forms of the same style. It is obligatory to note not only if it is present or missing but also to what extent.(therefore no shortcuts). Besides, this is a task performed by archaeologists who study almost each bone individually, noting also observations for each of them in another table (I'm using the structure you suggest there).

So, I guess we 're back where we started from? Any ideas

[bruceR]

"- indeed I use a "not allow empty" policy."

Stated as if there is some contradiction with Comment's and Vaughan's suggestions. Not at all clear what you imagine that contradiction to be.

[panchristo]

Stated as if there is some contradiction with Comment's and Vaughan's suggestions. Not at all clear what you imagine that contradiction to be.

Yup, there was some troubled communication there...

No contradiction if you ask me, just explaining misunderstood points.

Would you mind sharing your opinion on the subject?

[comment]

Have a look at the attached demo. If you start editing any parent record (incl. its children in the portal), it will nag until you fill the Status field for all children.

However, with 150 related records I'd find it extremely annoying not being able to save my work in the middle.

ValidatePortalFill.zip

Edited August 20, 2010 by Guest
Forgot the file

[Vaughan]

The option to save work in the middle is an excellent point Michael.

It might be better to have a "status" for each skeleton which calculates whether all bones have been entered.

[comment]

... and it could be done by conditional formatting only.

[Vaughan]

Yes, but the ability to search for "completed" skeletons would require a calculation field.

[comment]

Indeed.

[panchristo]

Thanks comment, and a sorry to all for not responding promptly.

I've had a look at all your suggestions and it really looks like a nice solution to the problem,but:

How would you reproduce the 150 "labels" (fields in my work so far) that need to be reviewed in every record?

Should I have then a "status" field to indicate everything has been reviewed or not?

If yes, maybe the value list used for the "labels" could also be self-limited by the values remaining to be reviewed? (Would need a bit of help for this)

After all that do you guys think it would be a well-designed structure?

Unfortunately my friend still thinks it is confusing to not know what is next to enter/review as she is used to filling out preprinted paper forms for the job. She would definately prefer something that is not quite unfamiliar to what she is used to until now.

Finally, if I don't adopt your suggestions, does anyone know of a scripted way for transposing the fields into records as asked in the beginning?

[panchristo]

Have a look at the attached demo. If you start editing any parent record (incl. its children in the portal), it will nag until you fill the Status field for all children.

However, with 150 related records I'd find it extremely annoying not being able to save my work in the middle.

Can you please tell me something?

In your example, if you have a table for the definition of categories to be used in the parent's portal, how would you setup the relatioships in order to show only those values that haven't been entered yet? I know how to doit in general but not 2 tables away...

Do I have to use a calc field in the parent table summarizing all the children values in order to exclude those from the list?

[Fenton]

Create a table for "bones reference". It would have 150 records (only). It would have 2 fields; BoneID (auto-enter serial ID), Name. This table does not change afterwards.

When a new Skeleton (your exisiting table) came in, you'd run a script to Import the "bones reference" table, into a Skeleton_Bones table (which is what they've been suggesting). You would then do a Replace to put the SkeletonID into each of these 150 records. So they'd be tied to that particular Skeleton.

[Alternatively, you can set and Import a global field from the Skeleton table, set to its ID. That's what I'd do; it eliminates the need for a Replace. But not a big deal either way.]

So it would get a set of 150 records, for that particular Skeleton. You could either Import the bone name, or just refer back to the "bones reference" table, via the BoneID.

It would have another field, a kind of status field. In this case, there is only [x] Reviewed. So I'd just make a number field with that name. It would use a Value List with only 1 value, 1 (number 1; it's a Boolean).

From Skeleton its 150 bones records can be shown in a portal, showing the bone name and the [ ] Reviewed checkbox. The portal can be broken up however you want on the layout, by using the Initial Row [ ] option (usually it's just 1, but it can be changed).

It can be arranged and grouped however you want, so it can resemble what they use now.

[comment]

Earlier in the thread, I asked if every skeleton has all 150 bones, and you said yes, every skeleton must show all 150 bones, whether present or not.

This means that the creation of a new skeleton record must be scripted, and the script must also create 150 child records in the Bones table (this can be done by importing them from a "bank" table containing 150 records with the bone names).

From the point of view of the user, they will be presented with a "form" that shows 150 named items for each skeleton.

does anyone know of a scripted way for transposing the fields into records as asked in the beginning?

This was discussed (rather reluctantly, I should say) in a recent thread:

http://www.fmforums.com/forum/showtopic.php?tid/216332/

But I really wouldn't go there.

---

EDIT:

I wrote this before reading Fenton's reply, so there's some duplication.

Edited August 25, 2010 by Guest

[panchristo]

GREAT!

I think finally we 've reached a solution that's structurally correct and adaptable to many limitations.

I'll try all of your recommendationss and come back in case I have any questions.

Thanks to all of you!

[panchristo]

By the way, here is a screenshot from the current configuration which I will have to convert to your suggested structure.

My biggest concern is how I will manage to stuff all of these fields in portals not exceeding one screen size. Each colored box is a separate field.

As you may notice, I was obliged to use the ID of each value since the description text(in the colored boxes) in all cases exceeds 20 characters.

Notice also that fields come usually in pairs with one heaading to save space (left-right bone).

Do you get the picture now?

[bruceR]

Isn't it cranial not crannial?

[panchristo]

Ooops, got me.

@all:

After some tries, it seems that only with my current setup I can accomodate all the fields in one screen, mainly because some of the fields would require splitting into many tables from which I couldn't get reports easily.

THANKS AGAIN!!! :thankyou:

[comment]

only with my current setup I can accomodate all the fields in one screen

Loop:

http://fmforums.com/forum/showtopic.php?tid/216292/post/363569/#363569