Filtering a view
Here's the thing. I have a table of vehicles, some 'Cars' and some 'Motorcycles', differentiated by the 'type' column. I want a view of just cars and another of just motorcycles, but I cannot find a sensible way to do that.
I can e.g. set a filter on the 'type' column as cars, but every row then shows 'Car' in the 'type' column. Likewise for motorcycles. Trouble is, I absolutely do NOT want every row displaying the 'type' column value since all rows are exactly the same, as indicated by the name of the view (Cars view and a Motorcycles view). Simple, so I hide that column - which DELETES THE FILTER.
I'm struggling to understand the logic of forcing the user to display the column which is being used to filter the view. I can understnad why you might want this with a more complex filter which results in multiple values of that column, but with such a simple filter that means every row will simply have that exact same value? This is so dumb I think I must be missing something.
How can I apply an overall filter to the view that uses a colum not displayed. Let's be clear here, SQL requires no such nonsense, you SELECT based on whatever criteria you desire and then display whatever column(s) you want from the returned rows. There is NO need WHATSOEVER to have to include the filtered column in the final output. There is conceptually no need for there to be any connection between SELECTed columns and those displayed.
I think Ninox is pretty good in many ways, but this sort of missing functionality really lets it down.
8 replies
-
I know you will tell me it should be simpler than that, but I just want to show that it can be done...
Pick a column you know will you will want to display, eg Reg and create a formula for each type you want to see and add this type of code
if Type = 1 then Reg else null end
This will give you a table looking like this
Now create new views for Cars and Bikes, rename the formula field column header to Cars or Bikes and set the filter to !=null. You end up with views like this
Regards John
-
Thanks John. Yes it should be simpler than that, but it is a way to do what I'm wanting so I'll give it a go. Not yet though as I've reaslised that having created different views as we're discussing, if I continue to modify the list view, I have to repeat the same mods on each filtered view. So best to settle on how I want the list view to look and only then create the filtered versions.
Neat trick though, thanks for pointing that out. Ninox alternately delights and frustrates.
-
Have you considered "dashboards"? You would create another Table with only 1 record that shows only in full form view and pulls data from another table so you can look at summary data? You can create View elements that will only show Cars vs Motorcycles?
Until I learned about them, my thinking was to enteract with the raw data table, but now I know that sometimes using another table to view the data is easier.
-
Yes, I was thinking about using a View on a Form, but I have to say, it is such a kludge having to create another table just to do that. Well, you 'can' do it from inside the table with which you are working, but Ninox throws ALL other fields onto the form as well and if you hide them (display only if null - another kludge) from that form and set up the View element how you want, you then discover those fields have been hidden from EVERY other form based on that table, which renders this workaround useless (ask me how I know - on the other hand, don't :-) It really should be possible to hide fields from individual forms and not affect any others.
However I can think of a way to utilise the above, so I would create 'Motorcycles' and 'Cars' tables and use those to add the appropriate View which I can then control using other fields I can place there, without upsetting the Vehicles table that actually holds the data for both. It will still be limited in that the View element on a form doesn't seem to resize with the main window as does a Table view and adding/editing records looks compromisd, if at all possible, but need to check that further.
I can also make use of John's suggestion using Formula fields to filter on and that works, although it means anyone using the system could modify that filter and so end up with the list populated by the wrong records. Ok, it's only me using it, but this method is using data manipulation to create a pseudo structure when structure should be fixed and independent of data. As a database developer, it makes me cringe as this is the WRONG way to do it. However, I will have to do what I have to do in order to achieve what I want.
It's a shame as Ninox has some great features, but an actual database developmnet envornment, it is not. In this case, it should be possible to filter the records of a Table view. All other functions operate on the table, but with only those records as defined by the filter. That would be a fixed configuration of the structure and any subsequent data manipulation would not affect that. This is how it should be. Ninox also should really be able to sort on multiple columns. Any spreadsheet can do this and in any case, it's standard SQL stuff so Ninox should be able to do that. I tried an 'order by' on multiple columns in the filter for a View element, but any ordering is completely ignored and sorting can ONLY be done by clicking on the column header.
Ninox's school report would be "must try harder".
-
Hi
For each form I create a tab called Hidden, and set it as 'Hidden'. Arrange it as the last tab on the form and place all the fields I don't see to see in that tab. They too will be hidden although they are still set as visible.
Regards John
-
Ah, good idea. I've not got around to tabs yet. Only been playing with Ninox for a few days, so lots to learn.
-
You can create two tables or just one dashboard table and create tabs that show just motorcycle and cars. Dashboards are meant as a way to look into your data. I have a sports league db so dashboards are the main way I interact with my data as I need to see the stats.
You can easily edit any record in the View just by clicking on a record and it will take you to the record in the corresponding table. To add records you can create a button that adds a record with Motorcyle or Car already filled in, or just go to the vehicle table and enter data there. Back to my workflow, I use to think I had to enter data into my results table. I have a related table, location, I realized if I go to my location table I can enter in records in my results table and it will already populate the location for me. One less step, and a couple of clicks saved. When I have to enter in 32 records each day that saves me a bunch of clicking.
-
I agree - cos you have having to use 2 views when ideally one view would do.
Wouldn't it be great if Ninox let you drag/put a combo next to the search box to act an alternative filter?
I also have to filter based on a job type (ie "NewJob" "Proofed") etc and have to have multiple views for each
However if you didn't want to see the list of all records with the same 'type' - you could (stupid suggestion but...) simply drag the column way over to the right so it's out of sight!
It's Ninox and it's good but we all have to find workarounds :-)
Content aside
- 2 yrs agoLast active
- 8Replies
- 1135Views