Creating relationships with an EAV table

[Kelly O.]

Hi everyone! Okay, here's the FMP challenge that I'm trying to work through:

- We collect 57 fields' worth of information from every user who registers on our website.

- That data is stored in our MySQL database in an EAV (Entity-Attribute-Value) table. (If you're not familiar with the format, it means there is a separate row for each piece of information (57 fields x 2500 users and counting) and the table has only three columns: User ID, Field ID, Value.) This table is called profile_values.

- I currently have profile_vales established as an External Data Source in FMP, and I can create relationships with it, interact with it, and update it all in real time.

- What I'm stuck on is how to create a new table that displays all of this information in a different format:

- I want every discrete value for User ID to have its own row.

- I want every discrete value for Field ID to have its own column.

- I want the data from the Values column in profile_fields to display in the cell that matches the corresponding User ID and Field ID.

Is there a way to create a new table that will

a) display the information in that format

allow me to create new relationships, layouts, etc. using the fields and values in that table

and c)(

and this might might be the tricky part

) retain the live 2-way connection with the data in the original EAV table?

Please let me know if you have any questions or if I can provide any other information that might be useful.

Thanks so much!

[comment]

Does every user have exactly 57 records in the profile_values table?

[Kelly O.]

Does every user have exactly 57 records in the profile_values table?

Nope. They have up to 57 fields, but if they only enter 10 of those 57 pieces of information, the EAV table only has 10 rows for that user.

[comment]

Well, IMHO a table of 2500 rows (and counting) and 57 columns is not very useful. It can be done, but it will take some work - and I am not sure how fast it will perform.

Anyway, you need to start with a list view of the Users table. Place the user's name on the left, then place 57 one-row portals into the profile_values table, each containing the Value field. Filter each portal to show only records where Field ID = n (with n increasing to cover all 57 fields).