Hi all, I ran into the above mentioned problem and solved it with a script but have the nagging feeling the solution could be much simpler and faster. The enclosed file is a mock up of a much larger database I'm currently developing and before I apply the solution to the bigger project I would appreciate your comments on improvements.
My system is Win 64 bit, FM 15.0.2. The enclosed mock-up file has two related tables, ID related to attributes (events). In this mock-up, each ID has attended (visited) different number of events (cities).
- To transpose ID's related records, each with an attribute (event) to columns and save as csv file, with one ID pr row and each attribute in a separate column.
- The techniques to transpose FM data to export to a regular row/column file all seem to rely on a fixed number, of either records or fields. If the number of records pr ID to be transposed are unequal and/or there is different overlap of attributes (events), then the values pr attribute will not go to the correct attribute column.
Example: ID A has 3 records, with attritbutes x1, x3 and x4 and corresponding values.
ID B has 4 records, with attributes x2, x3, x4 and x5 and corresponding values.
ID C has 2 records with attributes x1 and x4 and corresponding values.
If this data is gathered with a global variable and virtual list tech, then the exported row/column file will be mixed up as the number of attributes pr ID is not equal.
- This solution presented here is probably low tech but it only uses one global field and one script with loops and variables, but it works. The script has 5 steps and what I seek are suggestions for simplification (and perhaps more elegance), before applying it to a larger project.
- See below, after running the script, all IDs have the same number of columns and for those attributes that have not been registered for the ID, the script returns "[null]" (can be any value the user decides).
I'll be thankful for any and all comments and suggested improvements. I'm a intermediate user and look forward to learn more advanced techniques.