Hi everyone!!
This is my first post :) I tried searching to see if this question had been previously answered but didn't see anything. Since I am new to Jet Reports perhaps it is so easy it has never been asked! I am creating a count of Fixed Assets based on certain criteria, but have run out of room in the function wizard to finish my filtering! I have attempted to add the additional filters onto the end of the code in the fx line, but just get an error. Does anyone have any ideas? Thanks so much!!!
Date
Votes
12 comments
-
Jet Reports Historic Posts Hi!
If you need to use more than 10 filters, you can use Filters= and then specify a cell range containing the filter fields and filter values. So you can put your filters into Excel like this:C D 5 Field1 Filter1 6 Field2 Filter2 7 Field3 Filter3
Then in your NL function, you can do this:=NL("Rows","Table","Field","Filters=",$C$5:$D$7)
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Thank you SO much for your quick reply! This solution sounds wonderful!!!!! I have finally gotten back to playing with this report and I think I have done everything as suggested, but one of my fields FA No. requires a filter of =No. which gives me a #NAME? error no matter what I change the cell formatting to. Any ideas? :) In the mean time I will try this process on an easier list of fields and filters to make sure I am essentially doing it right and that this one filter field is my issue. TIA :D
Edit: The =No. it is now showing up properly, but I'm getting an error when I run the report that says Invalid Field 'No.'. but I don't have a field called that, I have a filter called =No. *lol* I wonder if that is still the problem. Does the fact I'm trying to do a count cause any issues? -
Jet Reports Historic Posts So Jet uses the syntax "=field" for the Link= functionality, which is why it's giving you an error. If you have "=No." as an actual value in your database, then you can surround the value with single quotes (') like this: '=No.' Then Jet should treat the equals sign as part of the filter value rather than trying to parse it as a linked field. Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Thank you again for another reply! I don't believe my =No. is an actual value in my database, I think it just lists all of my Fixed Asset Numbers that result from the filter. I could be wrong since I am just new to this, and it has been awhile since I started this report *lol* I have tried adding the single quotations but the first quotation disappears and leaves me with =No.' in the cell but shows '=No.' in the formula bar. When I run the report I get the same sort of error now stating Invalid Field 'No.''. I tried adding a second single quotation so that it appears as 'No.' in the cell and ''No.' in the formula bar - that leaves me without an error, but my counts are all 0's which I know is incorrect.
Here is the formula as well as the array I am attempting to use. Any further insight would be greatly appreciated :mrgreen: I'm sure it is likely just something simple!
=NL("Count","Fixed Asset","Field","Filters=",$B$4:$C$16)
Location Code C18
On Rent FALSE
Sold FALSE
Assigned FALSE
Product No. 20BSB
link= Rental Product Attribute
Attribute Code ALL-YARUSE
Boolean Value No
FA No. '=No.'
link= Rental Product Attribute
Attribute Code ALL-NEWUSE
Code Value New
FA No. '=No.' -
Jet Reports Historic Posts Hi Ferdinand,
Haha, okay now I see your dilemma. First of all, your C18, should probably actually be =C18 since you want the value in cell C18, not the text string I'm assuming (if you actually have a database value of 'C18' then ignore this). You are actually using Link= so the =No. is being used correctly as part of your Link=, so that is good. In this case, we actually don't want to put single quotes around the =No. value since we want Jet to treat it as a linking field.
A single quote at the beginning of a formula in Excel forces Excel to treat the formula as a text string. The interesting thing is that Excel eliminates the single quote from the actual value returned from the cell (as you pointed out). This would be an okay solution except that if you're using +Values or +Lock, Jet will end up eliminating the single quote when going back and forth between design and report modes. Just typing =No. into Excel doesn't work since Excel thinks you're trying to type a formula and then doesn't know what to do with the No. part so Excel returns #NAME. Probably the best way to fix the issue is to quote the =No. bit as a string in Excel. So that would look something like this:
="=No."
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Helloooo again my new friend Hughes :)
Thank you for your continued ideas to solve my problem!! They are SO much appreciated, and I am learning more through each attempt. That C18 was just silly!! Thank you for noticing that!! I have now attempted ="=No." and it is showing up properly as =No. in the cell, but when I run the report I am still getting the same error - it says Invalid Field 'No.'. I'm so stumped!!
Carene -
Jet Reports Historic Posts Hi Carene,
Actually I just noticed that you have "FA No.","=No." twice in your list of filters. You only need the one that comes AFTER the Link= filter. Jet is probably complaining about the one that comes before the Link=. Does getting rid of that line work for you?
Regards,
Hughes -
Jet Reports Historic Posts I have link= twice as well, maybe that is part of my problem?
link= Rental Product Attribute
Attribute Code ALL-YARUSE
Boolean Value No
FA No. '=No.'
link= Rental Product Attribute
Attribute Code ALL-NEWUSE
Code Value New
FA No. '=No.' -
Jet Reports Historic Posts Oh you do have Link= twice. Sorry, obviously I need to pay closer attention. :-) Looks like both of them are linking to the same table. Did you do that on purpose? Perhaps you trying to link to that same table with 2 totally separate sets of filters from the primary table, is that right?
When you do a 2nd Link= like this, by default it will be linking from the last link table. So from the "Fixed Asset" table, you're linking to the "Rental Product Attribute" table where the value of the "Fixed Asset" field "No." equals the value of the "Rental Product Attribute" field "FA No.". Then from the "Rental Product Attribute" table, you're creating another link back to that same "Rental Product Attribute" table where the "FA No." field equals the "No." field. Except that the "Rental Product Attribute" table has no "No." field so it is giving you an error.
I'm guessing that 2nd link= is actually meant to link from the original "Fixed Asset" table again. If this is the case, then you would want to use the filter "Link=Fixed Asset","Rental Product Attribute" in your 2nd Link= filter. This will tell Jet Reports to link from the original Fixed Asset table rather than doing a nested link from the previous link table. Does that make more sense?
Regards,
Hughes -
Jet Reports Historic Posts OH MY GOODNESS - I THINK IT WORKED!!!!!!!!!!!!!!! :lol: Thank you Thank you THANK YOU!!!!!!!!! You have been amazingly helpful!!!!!!!!!!!!!! Now it looks like my only problem is that my Location Code filter is =C18, but instead of the next Location on the report pulling C19, C20, etc each location is pulling the location code that C18 equals. We are SOOOO close *lol* I mean.. you are. He he. Hopefully that is just something easy I'm not seeing right now… This whole Filters= coding is the coolest thing ever!!!!!!!! I'm sure I will use this many more times in the years ahead! Thank you so much for leading me through it all! :mrgreen:
-
Jet Reports Historic Posts Hey I'm glad it's working! :) If you want the cell reference to update as the formula gets copied down, then you may have to move that filter back into your function. You can put both a Filter= filter and regular filters in your function, so that will probably help.
Regards,
Hughes -
Jet Reports Historic Posts My Hero!!!!!!!!!!!!!! The report is now fully functioning! I almost can't believe it!!! As excited as I am that this once impossible question has such a beautiful ending, I am somewhat sad that this means the thread has come to an end ;) I so much appreciate all of your help and kindness!!!
Please sign in to leave a comment.