Multiple Fields to Single Repeating Field

[Jaren Meier]

Simple and maybe easy for most of you. I am trying to get a databse that has 16 multiple fields and need to combine them into a field with 16 repeating fields. Please send help.

[LaRetta]

No, it wouldn't be simple or easy for any of us. And that's why we would avoid using 16 fields OR 16 repetitions. It would be moving from the frying pan to the fire.

We would (probably) use relational, ie, records instead. But it depends upon what you are trying to accomplish. Can you explain what you need a bit more?

LaRetta :wink2:

[Jaren Meier]

its a database already created years ago with over 825 records. These 825 records account for specific stores. Each store (record) has sets of fields designed to give information on 16 differnt clients enrolled at each store. Whomever created it used 16 individual fields for each of the field sets. I would like to get the sets of 16 individual down to just the main fields with 16 repeating fields for each.

[Søren Dyhr]

Well the structure is utterly inconvenient to say at least, should have been normalized years ago....

I would like to get the sets of 16 individual down to just the main fields with 16 repeating fields for each.

For what, searches?? Is it to estblish if a client is enrolled in more than one store not knowing which of the 16 fields he/she is in each particular record, or to get the store where both NN and MM are enrolled?

The data ought ot be organized this way:

http://previews.filemakermagazine.com/videos/513/DataTagging_full.mov

There is a problem even if you structure your solution by the book, to make AND seaches ...datamining sort of. By normalizing your data into a portal, should you in your seach request be aware of the records you get is bound to be made by successive omitted requests, one for each criteria - The rule applied here is DeMorgans rule on how to convert the OR'ish behaviour of stacked requests into behaving AND'ish.

I have made an example where my name as the video suggests are split into two portalrows:

!! So please don't do this:

Go to Layout [ “Layout #3” (thePortal) ] 

Enter Find Mode [  ] 

Set Field [ thePortal::Value; "Søren" ] 

Omit Record 

New Record/Request 

Set Field [ thePortal::Value; "Dyhr" ] 

Omit Record Perform Find [  ] 

Go to Related Record [ From table: “test”; Using layout: “test” (test) ] [ Show only related records; Match found set ] 

Show Omitted Only 

...it takes some wrapping your head around it, to grasp that all these Omit's and the new "Match found set" feature actually get you there, where you thought a repeating calcfield stuffing all the records values into was the solution to a datamining quest.



By the way, isn't what you wish to do not difficult - just plain and simple WRONG!

Evaluate(Quote(GetValue( 

Extend ( FieldA ) & "¶" & 

Extend ( FieldB ) & "¶" & 

Extend ( FieldC ) & "¶" & 

Extend ( FieldD ) & "¶" & 

Extend ( FieldE ) ;Get ( CalculationRepetitionNumber )));[FieldA;FieldB;FieldC;FieldD;FieldE])

...here only taking care of 5 field from a flatfile structure, it could have been gathered over a relation via GetNth... But it doesn't make it more a more noble way of handling such matters.

Again ...don't use this last concept, when DeMorgan has the answers!!!

http://hyperphysics.phy-astr.gsu.edu/HBASE/electronic/demorgan.html

--sd

[coconutt2000]

Any chance of exporting the records in a format that can be more easily reimported into a new database with a decent structure?

[Søren Dyhr]

Good point Coconuft2000 you could actually do a bit of both, make the calcfield for the export purpose only ...then when it's done delete the calcfieldas well as getting rid of the 16 undesired fields.

--sd

[comment]

If you're gonna do the crime, at least do it elegantly:

Choose (

Get ( CalculationRepetitionNumber ) - 1 ;

Extend ( f1 ) ; Extend ( f2 ) ; Extend ( f3 ) ; Extend ( f4 ) ; ... Extend ( f16 )

)

(I join the recommendation to export the calculated repeating field, with the option to split into separate records.)

[Søren Dyhr]

Nice one!

But if the field lables stems from an excel file conversion, could it be done this way:

Evaluate ( "Extend(f" & Get ( CalculationRepetitionNumber ) & ")" )

--sd

[comment]

Indeed.