Strange List Behavior for Droplist Element

[bascherz]

Not sure how long this has been happening, but it might be a new thing? One of my lists uses an element I defined as a droplist. The element's Advanced PHP code is pretty much verbatim from the example there. It does a JOIN query to produce an option list. This works perfectly in the form for editing a row. But in the list view, there is a very strange behavior.

Full disclosure: Some of the options returned by the droplist element query have no text, only a numeric value, which I suspect may be a clue somehow. This is a normal condition for this list.

Behavior: If the list is sorted in descending order on the droplist element column, all is well. If it is sorted ascending, then the entire first page of rows that should have text showing in that column show the option list's numeric ID instead. What's really strange is that it's always exactly one page worth of numbers. I proved this simply by changing the page size. No matter what the page size is, all rows on every page other than page 1 show text.

Very bizarre.

 

[pastvne]

There can be strange behaviors if the value stored in the column for that element is not represented as a value in the joined table. I think I would start by cranking up Fabrik debug to examine the query being produced, and then run that query directly in PhpMyAdmin or MySQL workbench to see what happens.

 

[bascherz]

I was directed to "get rid of the numbers", and to do that I tried making that element a databasejoin element. This worked great on my test site (a clone of the live site), but it would not work on my live site. I got the infamous MariaDB SQL error about there being no such column #__users.id...right. So I had to get creative and add an actual storage column in the Community Builder profile called cb_fullname and create Auto Actions to populate it. I would have used the built-in formatname or fullname field, but these are calculated fields and do not have corresponding columns in #__comprofiler. I could do this because Fabrik had no issue joining to #__comprofiler.id. All I was after was the person's full name as a single field. So now, just to get that column to not display numbers, I have Fabrik permanently dependent on a CB profile field that gets set by Auto Actions. Seems a bit fragile to me, but it works.