Developer forum

Forum » Development » Get all options in the column EcomFieldOptionTranslationName in the table EcomFieldOptionTranslation

Get all options in the column EcomFieldOptionTranslationName in the table EcomFieldOptionTranslation

E-mail notifications
Marie Louise Veigert
Reply
Posted on 13/09/2024 10:26:23

Hi,

Dynamicweb version 9.17.6

I need to retrieve all the options from a specific EcomCategoryFieldList field.

But it seems like they are not all stored in the database as single rows? 

I can see it in the EcomProductCategoryFieldTranslation table in the column FieldTranslationFieldOptions. But its stored as html format, but it doesn't work for the purpose. I need to retrieve them to an item for the editor to select :) 

I try to retrieve them like this as well:
SELECT 
  EcomFieldOptionTranslationName
 FROM EcomFieldOptionTranslation where EcomFieldOptionTranslationOptionID like 'ImportedNAVItemAttributes_5%'

It retrieves almost all in the EcomFieldOptionTranslationOptionID which starts with  'ImportedNAVItemAttributes_5'. But not the whole list, which is the problem

Anyone who have an idea how to get the database to have all options stored??

BR
Marie Louise
 


Replies

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Posted on 13/09/2024 15:16:28
Reply

Hi Marie Louise

Can you use the FieldOptionService or are you in another context?

BR Nicolai

 
Marie Louise Veigert
Posted on 16/09/2024 08:42:11
Reply

Hi Nicolai,

I need to render the options in a selector in an item.

Is it possible in a way with the service?

Fx i have this today for other Product Category Fields:

 

 
Stanislav Smetanin Dynamicweb Employee
Stanislav Smetanin
Posted on 18/09/2024 08:29:12
Reply
This post has been marked as an answer

Hi Marie Louise Veigert,

Unfortunately, our code converts sql to upper case, and to work with Xml we need to carefully work with the same register as before. So you need to have an access to database to make it work.

I suggest this...

Please create the sql stored procedure.

CREATE PROCEDURE GetFieldOptions 
@CategoryId NVARCHAR(50), --- [EcomProductCategoryFieldTranslation].[FieldTranslationFieldCategoryId]
    @FieldId NVARCHAR(50),  --- [EcomProductCategoryFieldTranslation].[FieldTranslationFieldId]
    @LanguageId NVARCHAR(50) --- [EcomProductCategoryFieldTranslation].[FieldTranslationLanguageId]
AS
BEGIN
    SELECT 
        optionElement.value('@Name', 'NVARCHAR(50)') AS Name, 
        optionElement.value('@Value', 'NVARCHAR(50)') AS Value
    FROM (
        SELECT CAST(FieldTranslationFieldOptions AS XML) AS xmlData
        FROM [EcomProductCategoryFieldTranslation]
        WHERE FieldTranslationFieldCategoryId = @CategoryId AND FieldTranslationFieldId = @FieldId AND [FieldTranslationLanguageId] = @LanguageId
    ) AS xmlResult
    CROSS APPLY xmlResult.xmlData.nodes('//Option') optionElement(optionElement);
END;
 
Then insert the command to the SQL of "Edit field options" from your screen. 
Like this:
 
EXEC GetFieldOptions 'brand_information', 'BrandList', 'LANG1';
 
 
Hope it helps.
 
Kind regards.
Votes for this answer: 1
 
Marie Louise Veigert
Posted on 27/09/2024 08:19:47
Reply

Thanks for the idea Sanislav!

I ended up making a view to be able to have it automatic update the new "table" if they import more new 'Types'.

For anyone else needing something similar:

USE [MyMainDatabase];

GO

CREATE VIEW [dbo].[vw_GetFieldOptions]
AS
 SELECT
   optionElement.value('@Name', 'NVARCHAR(50)') AS Name,
   optionElement.value('@Value', 'NVARCHAR(50)') AS Value,
    FieldTranslationFieldCategoryId,
    FieldTranslationFieldId,
    FieldTranslationLanguageId
FROM
( SELECT
       CAST(FieldTranslationFieldOptions AS XML) AS xmlData,
       FieldTranslationFieldCategoryId,
       FieldTranslationFieldId,
       FieldTranslationLanguageId

  FROM [MyOtherDatabase].[dbo].[EcomProductCategoryFieldTranslation] 
) AS xmlResult
CROSS APPLY xmlResult.xmlData.nodes('//Option') optionElement(optionElement);

GO

 

You must be logged in to post in the forum