Goodday all,
This is my first post in this forum. Although i have been using Jet reports for a couple of years now I received a request for a special report, which i cannot get right. I hope someone can help me out. Because it give me a headache. ;)
I have two tables, one table contains 'Product information', like name, vendor, type and ID.(PRODUCTID)
The other table cointains records which i want to count based on the 'STATUS'. (=ACTIVE)
At this moment i have :
- NL Rows on the product table, with a filter on PROVIDERID (34). This gives me 20+ PRODUCTID's.
=NL("Rows";"Product";"PRODUCTID";"PROVIDERID";"34")
- Based on the PRODUCTID I want to count the records in the second table based on STATUS (active) and PRODUCTID.
=NL("Count";"Voucher";;"PRODUCTID";$B10;"STATUS";"Active")
All this is just basic Jet reports and not a problem.
The next level is that I only want to see the PRODUCTID's from the PRODUCT table which have less then 500 active records in VOUCHER table.
Which should give 2-5 PRODUCTID's.
I can't get it right. Could someone help me out?
Date
Votes
2 comments
-
Jet Reports Historic Posts At first I thought how can that be difficult ;) , because you can easily include an NL(Count) as a field parameter.
And then I tried to write that formula…
Somehow the NL(Count) returns a text and not a number.
No matter what I try, it stays text and refuses to act correct when using "<" or ">".
So I decided to treat it as text on comparison as well.
And finally Jet gave me the correct result.NL("Rows";"Product";"PRODUCTID";"PROVIDERID";"34";"=RIGHT(""00000000""&NL(""Count"";""Voucher"";;""PRODUCTID"";""@@""&NF(;""PRODUCTID"");""STATUS"";""Active"");8)";"<00000500")
Hope this helps.
rmw
PS. I tried to use the 'TeresaRoberts' method of converting things to number by including 'Number&' but I don't know what version she is using, mine refuses to accept it. -
Jet Reports Historic Posts Thanks for your reply.
I tried the formula you wrote, but unfortunate it gives me an Invalid field error while running it. Error is on : '=NL("COUNT";…..)
Which version do you use. I use essentials version : 2012 R2 12.5.13162.0
Please sign in to leave a comment.