Hi,
I've built some Jet Express reports, but I'm struggling to filter blank entries from our database. I've tried setting a filter in the Table Builder for the field that's equal to all the possible entries using the pipe "|", but the report still returns blank entries.
Is this even possible with Jet?
Date
Votes
10 comments
-
Jet Reports Historic Posts Hello Jaycen,
When filtering out blanks you can use the syntax <>@@
When filtering for blanks you can use the syntax @@
Best regards,
MWilson
-
Jet Reports Historic Posts Hi, MWilson.
That didn't work for me:Tables and Fields Filters Item Ledger Entry Posting Date 01/01/2015..06/15/2015 Sales Shipment Header Salesperson Code <>@@ Sales Shipment Line Price Per LB 0.01..100000 Total Weight 0.1..1000000
Could the problem be that I'm trying to filter a subtable of my main one? -
Jet Reports Historic Posts Hello Jaycen,
Can you attach a copy of your workbook to take a look at how it is constructed?
Best regards,
MWilson -
Jet Reports Historic Posts I can't. It's full of confidential customer information.
I can tell you I've got the Sales Shipment Header linked to the Item Ledger Entry. I'm trying to filter on Salesperson Code from the Sales Shipment Header by using your method <>@@, but it still passes blank entries from the Salesperson Code field. -
Jet Reports Historic Posts Hello Jaycen,
Take a look at the attached screenshots for an example of how you would construct this.
If you can send me the report in Design Mode it should not have any confidential information within it, only in Report Mode would it have the results of the report run.
If you cannot send me that then if you can take some screenshots of your functions and post them that would at least give me an idea of how you have set up the report.
Best regards,
MWilson
-
Jet Reports Historic Posts Hah! I'm a noob, so I had no idea about Design Mode. That's fantastic.
Uploaded per your request. Thanks for the help so far. -
Jet Reports Historic Posts Hello Jaycen,
This is a difference in using Link= vs. InclusiveLink=.
On E15 change to:
=NL("Table","Item Ledger Entry",$F$14:$V$14,"Headers=",$F$13:$V$13,"TableName=","Item Ledger Entry","Filters=",$D$6:$E$7,"InclusiveLink=Item Ledger Entry",$F$12,"Link=Item Ledger Entry",$G$12,"InclusiveLink=Item Ledger Entry",$I$12,"IncludeDuplicates=","True")
On G12 change to:
=NL("Link","Sales Shipment Header",,"No.","=Document No.","Filters=",$D$9:$E$10,"Link=",$H$12)
When building a table with the Table Builder if you look at the bottom of the screen you will see an option that says "Only when Sales Shipment Header found".
You would need to choose this option so that it limits the results to only bring back Item Ledger Entry rows only when a matching record is found in the Sales Shipment Header.
Best regards,
MWilson -
Jet Reports Historic Posts Okay. The option is grayed out for me, and when I hover it tells me Link Filtering is only available in Jet Essentials. So, I can try to manually edit the links in the spreadsheet to "fix" the issue?
-
Jet Reports Historic Posts Hello Jaycen,
My apologies, this is only a Jet Essentials feature.
You may need to link from the Sales Shipment Header to the Item Ledger Entry and apply your filter for Salesperson Code <>@@ rather than linking from Item Ledger Entry to Sales Shipment Header.
Other than that, you can use the report the way you have it constructed now and just use the table filters to uncheck "Blanks" after the report runs.
Best regards,
MWilson -
Jet Reports Historic Posts Yeah, that's what I figured, and so have created some slicers and filters to handle the situation. It's less elegant, but functional. I'm quickly becoming a Jet champion, though, and I'll be pushing to go to Enterprise.
Thanks for all your help!
Please sign in to leave a comment.