Move data from column to row

[tucsondave]

Newbie here. I have been given several tab separated text files that I have imported into FM.

The records are set up with each person being repeated 4 times but some of the fields have different data. I need to get this data all in one row.

Current look:

Joe Smith address1 code1 validation1

Joe Smith address1 code2 validation2

Joe Smith address1 code3 validation3

Joe Smith address1 code4 validation4

What I need:

Joe Smith address1 code1 validation1 code2 validation2 code3 validation3 code4 validation4

Hope this is clear enough.

Thanks

Dave

[Søren Dyhr]

This tool is not a spreadsheet, so what might pass as conveninet layout doesn't nessersarily have to be the proper way of dealing with it.

The data needs to go to two tables, at least - not just one as you wish, because both address1 and the name occures 4 times as combo. This means two imports, one to each table.

Having fields named code1 code2 etc. is giving yourself away, since this is denormalization, the pairs code_x and validation_x should go as pairs to a related record, which by means of a cut up portal might be shown the way you wish them to be.

This means that the tab separated file need to be imported to a table, in which the data is sorted in name and address1. In an extra field do you then need to "stripe" via a scripted replace this calc'

Div ( Get ( RecordNumber ) ; 4 )+1

This fields value needs to be keyvalues in both the present table as well as the one you internally transfere detail records to, primary and foreign respectively.

A way to appcomplish this almost effordless is something in the vicinity of:

Import Records [  ] 

Sort Records [ Specified Sort Order: Gather::Name; ascending Gather::Address1; ascending ] [ Restore ] 

Replace Field Contents [ Gather::Stripe; Replace with calculation: Div ( Get ( RecordNumber ) ; 4 )+1 ] 

Go to Layout [ “theRelated” (theRelated) ] 

Import Records [ Source: “file:Gather.fp7”; Target: “theRelated”; Method: Add; Character Set: “Mac Roman”; Field Mapping:  Source field 3 import to theRelated::code Source field 4 import to theRelated::Validation Source field 5 import to theRelated::Key ] 

Go to Layout [ original layout ] 

Go to Record/Request/Page [ First ] 

Loop 

    Go to Record/Request/Page [ Next ] 

    Omit Multiple Records [ 3 ] [ No dialog ] 

    Exit Loop If [ Get ( FoundCount ) = Get ( RecordNumber ) ] 

End Loop 

Show Omitted Only 

Delete All Records [ No dialog ] 

Show Omitted Only 

The related records are then shown in 4 cut up portal lines arranged next to each other so the data will show up in listmode.

--sd

[tucsondave]

SD,

Thanks for your response. With my limited experience I'll see what I can do with it.

You infer FM is probably not the best tool for what I am looking to accomplish. Maybe so but it looked like it may do what I need.

If you know a better way to deal with a file like this I'm all ears.

My problem is the next process I use this data in must have all the data for one record in one row.

Thanks again

Dave

[Søren Dyhr]

I'm not saying this is the wrong tool for your task, but instead that presentation and the organization of data follows different lines of reasoning if the tool is a relational database as such.

So the denormalization you had in mind with your set of data, should be cut differently. Just the word of moving data from one location in the threads name, is where it goes wrong. The major goal with relational databases is to store each data to one and a single location, not scattered over several places to monitor if it's in sync.

Isn't the gathering you do a way of storing in one location, no it isn't becasue you wish to have data of a certain type repeated in the same record.

Nevermind, do you wish a template to show what I'm after?? Or do you get the point in cutting up portalrows?

--sd

[comment]

Can you elaborate on this "next process I use this data in"? Søren is right about your request going against Filemaker's grain, so to speak. Perhaps there's a better way to accomplish what you need.

[tucsondave]

My "next Process" is variable data printing and the program, while quite powerful, doesn't have a way to see down in the columns so all data needs to be in one row.

[comment]

I am sorry but I have no idea what 'variable data printing' means. Is 'the program' Filemaker, or some other application you will be be exporting data to?

[tucsondave]

Variable Data Printing in it's simplest form would be a simple mail merge, however the VDP progams have added the use of rules and scripting to make it quite powerful.

Personalized 1 to 1 marketing is the goal of user. Direct Mail on steroids.

Getting data into a form I can use as well as getting good data are what I need from my clients.

I'm still new at this and any program that can help me "sort" and manage the data is a tool hopefully I can use.

Thanks

Dave

[comment]

I am afraid this is not going anywhere, because you keep answering in generalities where specifics are required. I am guessing you want to export your data in some format, but I am in the dark regarding the details of the required format, and also how your data is organized within your Filemaker file.

Assuming you have a table of contacts, and each contact has several pairs of code/validation values in a related table, I think you could export in the form indicated in your original post by using XML export with a XSLT stylesheet.

The stylesheet needs to be custom-tailored to your file, and for this you will need to get yourself familiar with XSL/XSLT basics, at least. Browse the Importing & Exporting section of the forums for some examples that can get you started.

Of course, Filemaker is capable of mail-merging too (perhaps with some limitations on formatting), and certainly can incorporate rules and scripting.

Edited August 14, 2007 by Guest