Hello,

Honest I have tried to keep this brief. Winxp fm8.
My client has an online event registration database (Mysql and PHP).
The client wanted and got the interface for dynamically creating their own
fields through a user interface and this
likely has dictated this strange structure that is giving me headaches.
How did I get into this? Right now the online system emails my client a
registration and then they manually enter the data into filemaker.
For obvious reasons the client wants to pull all his online data directly
into Filemaker so I made an odbc (fm8) connection and through importing
re-created the 25 tables that
make up his online system (80,000 records in 4 seconds!!!! odbc rocks in 8).

Here is the problem.
I found a data structure I am having difficulty with. It may be old hat to
others here.
The creator of the online system opted to create a table called
available_fields, I assume the extra separation to make it possible for my
client to dynamically create "fields" were every time a "field" is created,
a record is created with a field Id and a label.
So now when someone registers, every input field (first name, last name,
address etc) becomes a separate record in the
registrant_session table that only lists a registration_id an inputed_value
and a field_id.

So now instead of the structure I am used to seeing of having one clean
record in a registration table with obvious relations between the data
collected and the field names, I end up with 12 or more related records in a
registration_session table and the correlation of how the data in these
records is related to an actual meaningful field name is a field id
related back to the available_fields table were there you can go view the
value in field_label.

So in Filemaker to view a complete "Registration" I had to create a layout
based on registration
and includ a portal from registrant_session that shows any number of
related records depending on how many available fields
they assigned to this event.

How in gods name would I export one clean instance of a registration when
one registration consists
of a dozen related records and were each of those records does not obviously
relate to a field name like first_name, but instead to a field_id that
relates to another table for available fields were you have to lookup
field_label.

Hope this makes sense, it has given me a headache.

My Questions:
1. Setting up a view of a complete registration is no problem but how would
I pull these related values into one clean record as the client wants to
perform some exports into excel etc.

2. Was the fellow who created this online system having moments of genius
or just drinking too much? Is this a common tactic? He sure handled the
dynamic creation of things but it at first glance is very problematic for
me. When I asked him to provide me with a sql query that would provide one
complete record of a registration his reply was "I may be able to pull that
together, but its very complicated". Well we agree on that!

your insights greatly appreciated. Maybe I am missing something obvious.
My client is willing to re-work the MySql data structure in his online
system but perhaps it is I who needs to be educated.

regards,

Pierre Lessard

My initial reaction was pure genius but now I'm second guessing myself.
I can't comment on this being a common tactic.

The following comes to mind to get the data into a single record but I
can't say that it's as dynamic as the original SQL database.
Create table A with fields;
ID Registration
ID Field_g (global number field)
text / number / date / time fields as required to put the data into 1
record

Create a link from "ID Registration" & "ID Field" in table A to the
data tables' same fields.

For each registration number, import 1 record into the "ID
Registration" field in table A.

Replace the data in your text / number / date / time fields using the
above mentioned link by putting the appropriate value into the global
then doing your replace.

If all the field values can be stored in a text field then you could
make your side of the program dynamic also. All you need is a global
"All ID Fields_g" that has the "ID Field" of each field carriage return
separated.
Your script could then loop, setting the "ID Field_g" and replacing
into a text field.
You might even be able to let the user select the field order.
There should be a better way to do this but this is the best that comes
to mind right now.