How to search for older records with prefilters applied?

[mirceat]

Hello,

I have a list with over 150k rows visible for a specific group of users. In order to limit the amount of rows loaded each time the list is accessed, I have added a prefilter to show only the records for the last year:

field: date_time
condition: greater than or equals
value: DATE_SUB(NOW(), INTERVAL 12 MONTH)
type: no quotes
apply to: viewlevel that include this specific group

The problem is that sometimes this group need to view/edit information older than a year, which now is not possible because of the prefilter.

Can I do something to prefilter the information for the last year but also when is necesary, allow users to search for older records?

Thank you

 

[troester]

In such a case I usually add the prefilter to a list menu item + have a submenu-item without prefilter

 

[mirceat]

Thank you, but it doesn't help me because of the loading time for 150k rows for the submenu. When i said above "sometimes" I meant 3-4 times per day for several users..

 

[troester]

I don't understand your problem.

A list is loading only the "rows per page" (which you can set in list settings or in the menu item). So as long as you don't set it to "All" (which is not recommended) it doesn't matter how many rows are in the list in total.

If you want to have different filters or access levels for the "old records" you can copy your list, add e.g. a date range filter, set the filter to "Required" (element or in list settings) and use this list copy for a menu item for handling old records.

 

[mirceat]

The list shows 10 rows per page (the "all" option is disabled, same for "displaying num"), but there are over 15k "pages" loaded every time, so the loading time is big (+6 seconds). Loading time is fine for few hundreds of pages (~ 2 seconds) and almost a second for few pages. So I tried to limit the number of "pages" loaded every time by adding a prefilter, the loading time is down to almost 3 seconds, but now I can't see older results.

I will try your suggestion to copy the list, I just thought it might be another solution that not involve copying the list. Thank you.

 

[troester]

I still don't understand: Which "over 15k pages" are loaded?

 

[mirceat]

I have over 150k records in database and the list display 10 rows per "page", so the list will have 15k pages

 

[troester]

Yes, but they won't be loaded.
Fabrik is loading only the first "x rows" you set.

 

[mirceat]

..I was convinced that this is the reason. Thank you for help, I will further investigate.

 

[troester]

It may be an issue if you have joins with "merge rows" or if you have an "Order by".

But with a "Required" filter Fabrik will load nothing at all before the user adds filter data.

 

[mirceat]

Yes, the list have multiple joins, I'm using "merge_rows", there is Order by date_time desc and group by a specific field

 

[troester]

Ok, then I think you need a required filter.