WHERE statement to display contacts on sidebar

Question

I have multiple product pages (same page template), and each page is supposed to have different sales rep contacts on the sidebar. I set up the sidebar contact box as a repeater to pull data/filter data from a SalesRep page type. The SalesRep page type has a field called ContactForPage; and my plan is to put in a string from the product page URL, so that if the string match the page URL, the contact will be displayed on the sidebar for that page.

Example: Current page url is: www.domain.com/products/product-123

John Doe has ContactForPage = product-abc

Jane Doe has ContactForPage = product-123

Jane Doe will be displayed as contact because the field value matched part of the URL.

I've been trying to write the WHERE statement but don't really know the syntax. Could you help! Something like: WHERE: SalesRep.ContactForPage LIKE '%CurrentDocument.NodeAliasPath%'

Answer 1

Fist of all using URL as in SQL query is not a good idea. You have to define relation between sales reps and products correctly. Probably one sales reps might be selling TV, the other - cellphones. But if you want to directly connect product to sales man. You can use Related Pages and relationship structure.

• Create new Relationship name, i.e. "is Salesman of Product" so Page A (saleman page) will be related to Page B (product page) via the relationship above
• Add related pages, i.e. for all salesmen pages add corresponding products via API or via properties > related pages
• Use web RelatedPages web part to shows salesmen list on the right

Answer 2

The Relationship is working both ways. You can have multiple documents related one and vice versa, i.e. one product can be sold by many salesmen and one salesman can sell different products. You just need to define one relationship. If you have a relationship between "Product A" Page and "Salesman John" Page, you will see this relation on both pages in properties > related page.

All I am saying is this: you do not need to add extra fields to your page types (either salesman or product), you should use relationship instead. Read here Displaying related documents.

Answer 3

I see a problem in your design. I assume a sales rep may mange multiple products, correct? If that's the case, you should add a field to the Product pagetype which allows you to select the sales rep instead of the add a field to the SalesRep pagetype, because how will you enter multiple products?

The field (e.g. SalesRepID) you add to the Product PageType can be a drop down list for select the sale rep. You can use Query to auto generate the list from the SaleRep page type. e.g. "SELECT RepID, RepName FROM custom_SalesRep". The RepID would be stored in the SalesRepID field.

For display, you can use the repeater set for SalesRep pagetype and use WHERE condition like this: RepID = {% CurrentDocument.GetValue("SalesRepID") %}