Can someone please let me know what I'm doing wrong? I am trying to create a snippet that has a formtext field that I can input text/search and pull from a data blaze table any/all rows where that text/search phrase is found.
I have read multiple posts and tried to implement their solutions in my code but so far I simply can't get mine to work correctly.
**Enter lookup term(s):** {formtext: name=words; formatter=words -> lower(words)}
{repeat: for (word, i) in split(trim(words), " "); locals=word}
{endrepeat: trim=yes}
{note: trim=yes}
{counter=count(split(trim(words)," "))}
{word_1=trim(word[1].word)}
{if: counter>1}{word_2=trim(word[2].word)}{else}{word_2=""}{endif}
{if: counter>2}{word_3=trim(word[3].word)}{else}{word_3=""}{endif}
{if: counter>3}{word_4=trim(word[4].word)}{else}{word_4=""}{endif}
{if: counter>4}{word_5=trim(word[5].word)}{else}{word_5=""}{endif}
{if: counter>5}{word_6=trim(word[6].word)}{else}{word_6=""}{endif}
{if: words=""}{code=""}{else}{code=code}{endif}
{if: words=""}{violation=""}{else}{violation=violation}{endif}
{endnote: trim=yes}
{dbselect: SELECT Violation, Code FROM Table1 WHERE contains(violation, lower(@word_1)) AND contains(violation, lower(@word_2)) AND contains(violation, lower(@word_3)) AND contains(violation, lower(@word_5)) AND contains(violation, lower(@word_4)) AND contains(violation, lower(@word_6)) ORDER BY code ASC; space=id; multiple=yes; menu=yes; cols=40; trim=left}
Violation: {=violation}
Code: {=code}
You are very close, but your WHERE clause is using the AND operator. This will look for a field that matches all the words, rather than one that matches "any/all" as you need.
I updated your code to use OR instead. Please also note that the check is case-sensitive. I also couldn't verify if it works as I don't have access to your space, but it should work.
**Enter lookup term(s):** {formtext: name=words; formatter=words -> lower(words)}
{repeat: for (word, i) in split(trim(words), " "); locals=word}
{endrepeat: trim=yes}
{note: trim=yes}
{counter=count(split(trim(words)," "))}
{word_1=trim(word[1].word)}
{if: counter>1}{word_2=trim(word[2].word)}{else}{word_2=""}{endif}
{if: counter>2}{word_3=trim(word[3].word)}{else}{word_3=""}{endif}
{if: counter>3}{word_4=trim(word[4].word)}{else}{word_4=""}{endif}
{if: counter>4}{word_5=trim(word[5].word)}{else}{word_5=""}{endif}
{if: counter>5}{word_6=trim(word[6].word)}{else}{word_6=""}{endif}
{if: words=""}{code=""}{else}{code=code}{endif}
{if: words=""}{violation=""}{else}{violation=violation}{endif}
{endnote: trim=yes}
{dbselect: SELECT Violation, Code FROM Table1 WHERE contains(violation, lower(@word_1)) OR contains(violation, lower(@word_2)) OR contains(violation, lower(@word_3)) OR contains(violation, lower(@word_5)) OR contains(violation, lower(@word_4)) OR contains(violation, lower(@word_6)) ORDER BY code ASC; space=16N3zHPlTTbiPyMirnqoMN; multiple=yes; menu=yes; cols=40; trim=left}
Violation: {=violation}
Code: {=code}
Thanks @Obed_Parlapiano, I implemented those changes and it seems to be working better BUT I am still having issues with the search actually pulling all the matching fields. It pulls some but not all the matching rows.
For example,
SEARCH TERM: "speeding" OR "speed"
ROWS THAT SHOULD BE PULLED:
Speeding in 35 mph Zone
Speeding (25 mph School Zone)
Speedometer; Defective
ROWS THAT ACTUALLY ARE RETURNED:
Speedometer; Defective
I have backed up/dup the snippet, would it be useful if I make it public/share it?
I was able to figure it out!!! This is what I came up with:
Enter lookup term(s):
{note}{formtext: name=SEARCH; default=; cols=22; formatter=SEARCH -> LOWER(SEARCH)}{endnote}{note: preview=no}
{dbselect: SELECT Violation, Code, Section, URL, rowid() AS `Row ID` FROM `Table1` WHERE contains(Violation, `@search`) ORDER BY Violation DESC LIMIT 1000; space=id; multiple=yes; menu=yes; cols=25; default=; name=task}
{run: tasks={dbselect: SELECT Violation, Code, Section, URL, rowid() AS `Row ID` FROM `Table1` WHERE contains(Violation, `@search`) ORDER BY violation ASC LIMIT 1000; space=id; menu=no; multiple=yes}
} {endnote}
{if: search=""}{else}{repeat: for (task, i) in tasks; trim=right}
{if: contains(lower(task.violation), lower(search))}
{=task.violation} - {=task.code} - {=task.section} - {=task.url}
{else: trim=right}{endif: trim=yes}{endrepeat: trim=left}{endif}
1 Like