Hi,
today i wanted to set a filter in the table 'item' for the field 'item category code' with the table builder.
in this field there are about 20 selection options in the database but the table builder shows only the first 7 options as you can see in the attachment.
how could that be? thanks for your answers.
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Hi,
So because some tables are very large, Jet puts limits on the number of records it scans when doing lookups. For example, if a table contained 100,000 records and you did a lookup on a field in that table and Jet were to scan every record looking for all the possible field values, you might have to wait for 20 minutes in order to see the values. Most people would assume Jet had crashed and force quit Excel before the lookup completed. Therefore, Jet has limits on both the number of records it scans and the number of values it returns for lookups.
You can find these limits in the Jet Application Settings on the General page. There is a lookup sample value limit (which defaults to 100). This is probably not your problem since you are only seeing 7 values. Then there is the Lookup record scan limit (which defaults to 1000). This is probably your problem. You probably have more than 1000 entries in the Item table and Jet is only scanning the first 1000 items and giving you the values for the Item Category Code in those 1000 records. You can increase the number of records scanned, which may cause your lookups to be slower but to show more of the values in the table. If you set this to 0, Jet will scan all the values in the table, but this is not recommended since it may take a very long time to do lookups on larger tables.
One more thing. For this type of lookup, Jet will only show you values that exist in the table you are performing the lookup on. For example, the Item Category table may have 20 different codes, but if you are only using 7 of them in the Item table, you will only ever see the 7 you are using in this lookup. This lookup is going to query the table on which you are performing the lookup, not related tables. The exceptions to this are Option fields and Advanced Dimensions. For these field types you should see all the possible values.
Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Then there is the Lookup record scan limit (which defaults to 1000). This is probably your problem. You probably have more than 1000 entries in the Item table and Jet is only scanning the first 1000 items and giving you the values for the Item Category Code in those 1000 records.
thank you very much, this was the problem. now it works. greets david. -
Jet Reports Historic Posts Hi,
I have a strange thing happening with the lookup. If run on my computer, the lookups work just fine. Once the report file gets uploaded on the server for end users to be able to run, the lookups still open in alookup window but none shows any values to pick from. Why would that be happening?
Thank you.
Milena -
Jet Reports Historic Posts Hi Milena,
Please don't start something new on a conversation thread that has already been solved like this. It is much easier if you just create a new thread, especially since your problem is not related to the problem discussed previously in this thread. In your case, I think you should actually just create a ticket on the Jet Reports support site instead.
Regards,
Hughes
Please sign in to leave a comment.