Getting all user profiles tied to an address

Question

I am new to using Microsoft Commerce, so this is probably a newb type of question.

Anyway...

On the Addresses table, we use the u_country_name field to store a special piece of information to uniquely identify customers.  Ideally, what I would like to do is to create a web screen that allows a user to type in that information, find that address, and then get ALL user profiles who have that address tagged them. 

I have tried multiple things, but none have been successful.  I tried to get the u_address_id by searching on the u_country_name in the Addresses table.  Once I got that, I tried to construct an OPER CLAUSE XML string to do a search on the u_addresses field on the UserObject table where the u_addresses contains the u_address_id from the Addresses table.  This last search on the UserObject resulted in commerce telling me that the u_addresses field is not searchable.

Any ideas or suggestions would really be appreciated!

 

Answer 1

To search on the property of a profile you should add an index on corresponding table column (u_addresses in your case). Then in CS Manager mark corresponding data member as indexed and profile property as searchable.

Answer 2

Thanks Alexander for the response, but u_addresses is a text field, and from what I have been to find through research, text fields cannot be indexed.

Answer 3

Why not? Most of the fields in CS profiles are text fields (nvarchar), even profile Ids which are indexed and searchable.

Answer 4

Most of the fields on the table are nvarchar, but u_addresses is not.

When I go to try and create an index on u_addresses, it does not even allow me to check u_addresses as the field to create an index on.

I very well could be trying to create the index wrong.

I went into the 'UserObject' Database, right clicked on 'Indexes', and selected 'New Index...'   Is this correct?

Answer 5

This is strange, why do you have u_addresses as TEXT column? Default CS profile schema has u_addresses as nvarchar(255).

As for indexing, MS SQL doesn't allow indexing on TEXT columns, only full text indexes are allowed as far as I remember.

I would suggest to change it to nvarchar if there are no specific requirements forcing you to have it like TEXT.

Answer 6

Its not that strange to have converted it to a text type in the DB. If you consider the length of the GUID, you can figure out that 255 characters puts a limit on how many addresses the user can have... add one to many and it errors out.

You can use NVARCHAR(MAX) or something that is the exact size you need if you limit the number of addresses a user can have.

To avoid the multi-value field, you can add a user_id/owner property to the address so that you have a proper foreign key, then use the profile search API to retrieve them

Answer 7

In the system, multiple profiles can have the same address tied to them.  If I create a owner field on the address table, it will result in a multi-value field.

Answer 8

You could create a UserToAddress profile to hold the many to many relationship that has just the userID and addressId field in it.

We once did something similar to link a couple cutome profile objects that had a many to many relationship.