Jump to content

Creating relationships with an EAV table


Kelly O.
 Share

This topic is 4382 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

B)
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!

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

This topic is 4382 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.