I’ve got an unusual one here that is a head banger (for me anyway). I have an array stored in a VARCHAR field (UsersCats) in a users table. The array contains Primary Key IDs for another DB table - WorkCats.
I have a repeat that uses Server Connect to list the WorkCats table as the Expression, then a show/hide to only show the rows that match the Primary Key in the users UserCats field.
It seems to display correctly, however, giving it a closer look its adding another 2 rows to the query.
Heres a view of what Im seeing:
You can see from the blue bits that the following have been added, yet they don’t exist in the UsersCats array. Its added a ‘2’ and a ‘4’.
Ive tried a lot of stuff, including converting UserCats to a string, a number etc but it all shows the same result.
Are you storing an actual array or a comma separated list of values in this database field?
Also - are you filtering this on the client side and if yes, why not filter a query using the value(s) and sending the results filtered to the front end?
This usage of show/hide and a repeat region seems like a weird implementation to me.
Its a comma separated list of values in a DB VARCHAR field. I was planning on using the WorkCats serverconnect elsewhere on the page. TBH, I didn’t even think about filtering server side, which makes a lot more sense.
Repeat your first query, add the second query inside the repeat. Use the field with the comma separated values as a filter for the nested query with the flatten formatter applied to It and IN condition.
Or if you don’t need to repeat the first query, skip this step and the second one directly after it.
Why not ditch the comma separated list and use a subtable of single elements instead?
I always find it a better solution in the long term and much more flexible
I agree entirely with you Brian, however I’m working with a very large legacy database so have no choice. Its lasted well over the years but this particular part of it is proving to be a pain the backside.
Wowza, that was a tough one for me. I got the solution, so here goes in case anyone else has similar issues:
As per @Teodor suggestion, I did the work on the server side (DUH!) which makes total sense, I set a value with the comma separated list in it, then used that as a query to pull out the details from the categories table:
I had to use a custom SQL query so I could use FIND_IN_SET - this was the only way I could find that would get me the actual amount of rows.
Once done, I just had to create a repeat on the page to pull out the records:
end result was perfect, it now displays the correct amount of records for the amount of IDs in the comma separated list:
PHEW…! That was a bit of a pain but the end result was way better than my original.
Thanks again to @Teodor and @Hyperbytes for pitching in, as we all know, it’s nice to have another set of eyes on something when you’re struggling - much appreciated…
