How to select row from dictionary process by tag (string) from other field value

0

Hi Damian,

welcome to the community. :)

Before I throw a SQL statement at you, couldn't you simply use a "choice picker" in the item list?

1) Select the type, and use your datasource
2) add a new row a
3) select the "tags" field and activate "searching"
4) most important, switch the search mode to contians.

By default it's always "starts with" and I change it in 99% of all cases to "contains" :)

Best regards,
Daniel

Daniel, thank you for your answer!
But as far as I understand you, it doesn't cover all of my needs (or I'm missing something :) ). I'll try to precise my use case...
You're right, that searching function in choice field works fantastic, and exactly how I was dreaming about ;) but the input contains a few words, for example:
We have product, described by tags "black" and "bike". On the Order form, in Description field user enter their needs:
1. "bike" - it works properly,
2. "I need a black bike with white elements" - doesn't work. Is it possible to manage that scenario using your idea (or some variation of it)?

Hi Damian,

this should work, too. Does it find
"I need a black bike with white elements"
when you search for "I need" ?

Did you check the search mode whether, it's currently configured as "start's with" instead of "contains" ?
4) most important, switch the search mode to contains.

Best regards,
Daniel

I checked twice and still doesn't work...I sent screenshots in attchment.

Hi Damian,

what would be more interesting would be the configuration of the field. :)

I just tested it to make sure and it did work as it should. At least after configuring the search mode in the 'Picker' tab of the advanced configuration.

Best regards,
Daniel

Daniel,
you're right, in case you presented it works properly, but I need check if every single word of "Description" value can be matched on any 'Keyword'.
Following your example, I want to make the business rule, which return a correct product, when my input is for example "line lorem ipsum" (same as in your screenshot "line").

Hi Damian,

I'm sorry, I really did not understand your request correctly and I have to admit I still aren't sure whether I understood your use case correctly this time.

If the users enters 5 words you want to return any product which matches at least one word?
If this is the case you could this approach below to initialize an item list. For me this makes no sense. If I want a black bike, I would want only these products where I have a match for each word. Than again, if the user would really write a sentence, We won't get any match as there will be words, which are not a tag. So we would need to work around this and test the tags of each products against the string which. This could return any number of products with a varying number of "tag" matches.


While a description field could be easier, I would opt to store the tags in a separate dictionary and use a multi choice field for the order as well as the products. In this case you won't need to handle any spelling errors or such things. In case the users need to add freely tags, you could also use this approach. This would allow the user to stay on the form. Although I have to admit, that the new dictionary entry probably cant be added automatically with the current options. It would probably replace all existing entries.
https://daniels-notes.de/posts/2022/modal-dialog#adding-a-new-entry-to-a-dictionary

Best regards,
Daniel

declare @description varchar(max) = 'I need a, black bike with white. elements'

DECLARE @Products TABLE (ProdID INT,ProductName VARCHAR(20),Tags varchar(1000))
insert into @Products (ProdID,ProductName,Tags)
values
(1,'Child bike 12"', 'bike, black, small'), (2,'Child bike 24"', 'bike, black, small,boy'),
(3,'Hemlet boy"', 'boy, white, helm, security'), (4,'Cards"', 'game, white'),
(5,'Cards"', 'game, purple'), (6,'Cards"', 'xyz')

select *
from @Products join
dbo.SplitToTable(
Replace(
-- removing any non word characters
Replace(Replace(@description,',',''),'.','')
-- split to table seems to have a problem when using ' ' as a delimiter -> replace ' ', by ','
,' ',',')
-- split by ,'
,',')
as wordTokens
-- SplitTotable returns a column named item
on Tags like '%'+item+'%'



select distinct ProdID, ProductName, Tags
from @Products join
dbo.SplitToTable(Replace(Replace(Replace(@description,',',''),'.',''),' ',','),',') as wordTokens
on Tags like '%'+item+'%'