Creating one table from two, one of them with no flat data

[jockycamposeco]

Hi there community, may you can help me please, i'm struggling trying to create a new table based on other two, the thing is that in one table we have the main user info and the other has custom form fields so ther only field_id and Value.

User: ID Name Surname

User_data: id form_field_id user_id value

Form_field: id field_name (phone, address, colour, etc)

Im trying to aim something like this

FullUserData

ID
Name

Surname

Phone

Address

Colour

...

In a Mysql query it could be something like:

SELECT id, name, surname,
(SELECT value FROM user_data where user_data.user_id = users.id and form_field_id = 3805) as Phone,
(SELECT value FROM user_data where user_data.user_id = users.id and form_field_id = 3809) as Address,
(SELECT value FROM user_data where user_data.user_id = users.id and form_field_id = 3881) as Colour

FROM users

Thank you for advance for you help!

[v-lionel-msft]

Hi @jockycamposeco ,

First, copy the 'user' table in 'Edit Queries', then you have three tables:

Then, create columns in "User (2) table" :

Phone = 
LOOKUPVALUE(
    User_data[value],
    User_data[user_id], User[ID],
    User_data[form_field_id], 3805
     ) 
Address = 
LOOKUPVALUE(
    User_data[value],
    User_data[user_id], User[ID],
    User_data[form_field_id], 3809
     ) 
Color = 
LOOKUPVALUE(
    User_data[value],
    User_data[user_id], User[ID],
    User_data[form_field_id], 3881
     ) 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.