Hi All,
I want to filter data in table by using site property. The situation is- I am using sql to fetch data. I want to fetch records containing certain prefix. eg. there are 100 records in db and 50 records have prefix 'OS', so i want only those 50 records to be fetched.
Steps i followed-
1. created site property with default value- "OS"
2. created server action and assigned the site property to the output of the action.
3. used server action in logic flow of sql. created query parameter in sql as 'OSRoles' and wrote query-
WHERE ( {Role}.[Name] LIKE '%' + @OSRoles + '%')
4. passed server action output to query parameter.
Now, the query runs but returns only 10 records out of 50
what could possibly go wrong here or am i doing something wrong?
Hi @Simran Vaswani
Is there other filter or any join table in your SQL? Its also the reason that other data didnt show as what we expected
There was problem with the join hence data was not being returned as expected.
HI!
Please review you code , verify if you had set "Max Records" to 10.
just leave that parameter blank.
And verify also if you had left "Top(10)" in the beginning of the statement you must erase the "Top(10)" from your SQL .
Example "Select Top(10) ID ... "
Hi @Maria da Graça Peixoto,
Max records is empty and no Top 10 query is added.
On removing where clause i am getting right number of record. On adding where clause, it is returning 10 instead of 50.
As per your use case you need roles with prefix "OS".
For the above requirement, you will need to amend the where clause to below -
WHERE {Role}.[Name] LIKE @OSRoles + '%'
Although I will also suggest to follow best security practices for substituting any site property into your query.
Thanks
Rahul Yadav
Can you share the full query please, So everybody knows what exactly is happening.
Sometimes is a little difference.
Please compare one of the records that is shown with some records that are omitted, look for spaces in Name field from the records omitted that are not present in those that are shown, look for small letters , example "os" instead of OS.
just some ideas