Using a custom query within a Uni Selector

Hi,

Out of the box the Uni-selector needs known objects... So you would need to extend or create it by building your own form control:

docs.xperience.io/.../developing-form-controls/example-developing-custom-form-controls

What you could also do:

If the number of returned items isn't to much you could look into using drop-down or multiple-choice lists. Which allow you to use custom queries and allows you to use multiple fields for display.

Quite old but I wrote a blog about it few years back: devdavid.nl/...september-2017/did-you-know-1-multi-value-display-in-form

So no custom development is needed...

Thank you for your reply.

The reason why I would like to use a Uni Selector is due to the fact the results are paged and the user has the ability to search.

The object I am using for the uni selector holds an ID and I literally just need to go off to another table to get the Name for Display purposes only within the uni selector.

Can I use Macros within the Display Name Field to achieve this? If so how?

Well it triggered my inner dev and spend some testing.

Guess you can, but it's not very efficient... And the only way I managed to use a column property in a macro was after displaying it.

for example when using cms.User object I could uppercase the username, but only when displaying it

{%UserName%} {%UserName.ToUpper()%}

So not sure where your reference is coming from (custom table, specific page, or custom class)

But as a POC: I'm getting UserName based on UserID through a Macro using a where on the Users from GlobalObjects

ID: {%UserID%} - Name: {%GlobalObjects.Users.Where("UserID = '"+UserID+"'").FirstItem.Username#%}

Make sure to click Advanced in the "editing control settings" section

and it works ^_^

another tip if you want to test out macros is using the Console or Benchmark module you can find at System > Macros > Bechmark

That's great thank you. I am wanting to pull information from COM_SKU. So I have the SkuID and need the SkuNumber returning. Obviously {%ecommerce.sku.Where("SKUID= '"+SkuId+"'").FirstItem.SKUNumber#%} doesnt work but not sure how I could use your example to go after the COM_SKU table.

Can you try:

{% GlobalObjects.SKUs.Where("SKUID= '"+SkuId+"'").FirstItem.SKUNumber#%}

That doesnt work unfortunately :(

how does the complete control config look like?

https://ibb.co/0VMkw3M the image doesnt seem to be loading

Is the field in your custom object called SkuID?

if so can you add that value to the display name format. So make the complete field something like: ({%SkuID%}) - {% GlobalObjects.SKUs.Where("SKUID= '"+SkuId+"'").FirstItem.SKUNumber#%}

So the custom object has a field called SkuId. And I am trying to use that to go off to the COM_SKU table to then retrieve the SkuNumber to display it in the Uni Selector.

I have made the change and an example row returned is: (8802) -

So in that case the column value is correct. As you get (8802). if you run it directly in the macro debugger module does it return anything for the skunumber or any other field?

eg if you place the following directly in Benchmark module at System > Macros > Bechmark:

{% GlobalObjects.SKUs.Where("SKUID= '8802'").FirstItem.SKUNumber#%}
{% GlobalObjects.SKUs.Where("SKUID= '8802'").FirstItem.SKUName#%}
{% GlobalObjects.SKUs.Where("SKUID= '8802'").FirstItem.DisplayName#%}

does it return any value?

I have just run those in the benchmark tool and I didnt get any results in the output screen?

Well in that case it's most likely that the 8802 isn't the SkuID but some other number... (perhaps document ID?) can you confirm it's correct.

I get an output in the benchmark tool if I use your user example above (ID: {%UserID%} - Name: {%GlobalObjects.Users.Where("UserID = '"+UserID+"'").FirstItem.Username#%})

But for some reason I am not getting an output when trying to go after the SKU Data

I'd say play around the benchmark a bit to see if you get any data...

e.g. is there even a SKUId, and does it match with what you think it should be?

{% GlobalObjects.SKUs.FirstItem.SKUId#%}

I think you have the wrong ID...do you have over 8800 products in your site? As the SKUID is auto incrementally created.

Ok thanks I will see if I can get it to work. Could I create a custom macro and use that instead?

Running {% GlobalObjects.SKUs.FirstItem.SKUId#%} didnt return anything either.

Yes we have over 13,000 products in the sku table.

So using a custom Macro has worked and I am now getting the Sku Number back. But it has now broken the search within the Uni Selector....d'oh!

Do you get an error? Or doesn't it return results when looking for a skunumber? I think it will only allow you to search through the values available through the original "Object type" you selected

The issue is its building the macro into the sql that is being used to search the Uni Selector data.

WITH AllData AS ( SELECT [Review], [ReviewId], [SkuId], ROW_NUMBER() OVER (ORDER BY [SkuId]) AS [CMS_RN] FROM TABLE WHERE ((Review IS NOT NULL AND ShowInReviewsWidget = 1) AND ((String.GetSkuNumber(SkuId)@ LIKE N'%bar%') OR (Review LIKE N'%bar%'))) ) SELECT , (SELECT COUNT() FROM AllData) AS [CMS_TOT] FROM AllData WHERE CMS_RN BETWEEN 1 AND 10 ORDER BY CMS_RN

I added the @ to {%String.GetSkuNumber(SkuId)@%} to prevent it from outputting the signature but it still breaks the uni selector search

I have just ran this and I get a result of 0?

Could it be you're not using Global SKU objects? so they are site specific?

Can you change the initial GlobalObjects to SiteObjects?

so: ({%SkuID%}) - {% SiteObjects.SKUs.Where("SKUID= '"+SkuID+"'").FirstItem.SKUNumber#%}

Perfect that does now work. I still have the issue though of the search not working

Any thoughts?