1 (edited by papafrankc 2021-09-27 23:12:36)

Topic: Filtering in FastReport (still working on a couple of issues)

Hello,
I'm trying to do some filtering in the Data Band of a FastReport.
I have a field called SPARE. I would like to see only those records where SPARE = No
.
I've been reading where you can filter records in the Data Band but so far nothing I've tried is working. 
.
Any ideas will be appreciated.
.
Thanks
Frank

Re: Filtering in FastReport (still working on a couple of issues)

Frank,
I typically do all my record selections through MVD rather than FastReport. I really never even tried to do any kind of filtering in FastReport and not sure exactly how to do it. Is there a reason why you need to do it in FastReport? If not, I would just do it in MVD. Attached are two ways to do it. One is through ReportSQL and the other is just a plain Report with a hidden search checkbox to indicate the SPARE field. There are actually four records in the table, but only two have the SPARE boolean field set to 0 (No). Therefore, when you click on the respective report buttons only two of the records will print. Hope it helps.

Post's attachments

Attachment icon Report Filter.zip 337.19 kb, 134 downloads since 2021-09-13 

Re: Filtering in FastReport (still working on a couple of issues)

EHW,
Your suggestions do help as I'm learning about more things that I can do.  I've attached a printout showing where I am so far.
.
Your example program does work and I've been trying to incorporate the Boolean check box into my program.  However I'm having some trouble.  Here's what I believe is affecting me.
.
- I originally created a lookup table with SPARE or NO.  You can see that on my example. It's basically a Boolean expression but I'm doing it the hard way.  Only because I've never been too crazy about the Boolean checkbox on a form.  When it first appears it has a little blue box in the center.  To me - does that mean that it is already checked? I learned that it does not and you have to actually check it to enable it.  My opinion is that's not too user friendly.  If it first appeared as an empty box that would make more sense to me, as it would tell me to check it if I want to enable it.
- I did add a Boolean spare checkbox to my form as you can see.
- I then tried to use it as a filter in the report. I kept getting weird results in the report.
.
I think part of my issues are because I'm using data from 2 tables - Epuipment AND Service.
.
Right now I have the Next Service Date in RED because I wanted it to stand out.  However eventually I would like to have ONLY the overdue records to be RED and the service stuff coming up to be GREEN.  I'm thinking that the whole color thing might have to be done in FastReport??  Anyway that's not priority right now but it's on my wish list smile
.
To answer your question, since FastReport appears to have filtering options I thought it might be easiest to do it on the report.  However I've tried to apply the filtering techniques I found on Mr. Google and haven't had any luck so far.
.
Thanks for your help, always appreciated, as I wouldn't be where I am now without the excellent help from the forum.
.
Thanks, Frank

Post's attachments

Attachment icon Equipment Service Records.pdf 140.93 kb, 110 downloads since 2021-09-14 

Re: Filtering in FastReport (still working on a couple of issues)

Hi All,
I haven't figured out yet how to solve my original problem in FastReport, however I did find a very simple solution to my original question.
.
I have 2 tables - Equipment and Service.
Equipment has both Equipment that is IN SERVICE and SPARES that are not in Service.
For my Services report all I have to do is to use the Service table since it does not contain any service records for SPARE parts. Simple and easy.  Now I don't have to try to do any filtering for  spare parts since it's already done for me. This is one of those moments where you wonder why you didn't notice the obvious.  Oh well, live and learn smile
.
I still would like to be able to change the color of the NextDueDate on my report to show RED if overdue and GREEN if not overdue.  I'll work on that & (highly likely) I'll be back for more help.
.
Thanks, Frank

Re: Filtering in FastReport (still working on a couple of issues)

Hi Frank,
Use the 'highlight' tab in the memo field where you want the highlighting to show and add the relevant condition (see screenshot in the attachment).
Derek.

Post's attachments

Attachment icon overdue.zip 470.39 kb, 143 downloads since 2021-09-16 

Re: Filtering in FastReport (still working on a couple of issues)

Derek,
Thanks, that works perfectly.
.
From my working in FastReport it looks like it has lots of options. I've read some of the manual and it looks like it's a whole separate program with all kinds of bells and whistles.
.
That's why I was trying to use the Data Filtering to select certain records, but I never could get it to work.
.
Anyway I've got enough to learn in MVD without going looking for more trouble smile
Thanks, Frank

Re: Filtering in FastReport (still working on a couple of issues)

Derek,
As I mentioned, your Highlighting tip worked great...until it stopped working.
.
Here's what I have for the Highlight option:
<Report."tbl_Equip.calcNextSvcDate"> < <Date>
Here's the error I'm getting:
Memo: Could not convert variant of type (Unicode String) into type (Date)
.
What's strange is that the first time I tried it, it worked fine and now it doesn't?
I've been working on the program doing all kinds of stuff so maybe I changed something?
.
I also tried a date that is NOT a calculated date and that doesn't work either.
.
Thanks, Frank

Re: Filtering in FastReport (still working on a couple of issues)

papafrankc,

If you are taking a typed in date from a memo field  and want to make it an actual date from MVD/Pascal point of view you need to use strToDate(Form.Memo.Text) might also be strToDate(Form.Memo.Lines[line number]) or DateToStr if taking a date and placing it into a memo field.

On a clear disk you can seek forever

Re: Filtering in FastReport (still working on a couple of issues)

CDB,
I'm trying to use a Calculated field in FastReport.  If the Calculated field is less than Today's Date then I would like to have it show in RED on the report, showing that service is overdue.
.
Here's my calculated field courtesy of the forum (thanks for that)
strftime('%m/%d/%Y',date(julianday((select max(SvcDate) from tbl_Service
where tbl_Service.id_tbl_Equip = tbl_Equip.id)) + (select SvcInterval
from tbl_Svc_Interval where tbl_Equip.id_tbl_Svc_Interval = tbl_Svc_Interval.id))) 

.
I tried to apply your suggestion to the Highlight field in FastReport as follows:
<strToDate(Report."tbl_Equip.calcNextSvcDate")> < <Date>
.
So far, I don't get any errors with this one, but it does not appear to be recognizing whether the date is before or after today's date.  It is changing all of the records to RED.
.
Derek sent me an example program with the following Highlighted code:
<Report."test.duedate"> < <Date>
This one works fine but mine does not when I use this code.  I get an error.
.
Thanks, Frank

10 (edited by derek 2021-09-27 11:10:35)

Re: Filtering in FastReport (still working on a couple of issues)

Hi Frank, Hi CDB,
Highlighting works for me, based on what you've told us.
One thing that might be an issue is if you have items that haven't yet had a service but it should still highlight the other records.
Personally,I'd probably test for items being overdue within MVD with a calculated field and then simply use that as the condition in FastReport - so, using your example, something like:

(case
when strftime('%m/%d/%Y',date(julianday((select max(SvcDate) from tbl_Service 
where tbl_Service.id_tbl_Equip = tbl_Equip.id)) + (select SvcInterval 
from tbl_Svc_Interval where tbl_Equip.id_tbl_Svc_Interval = tbl_Svc_Interval.id)))  < date('now') then 'overdue'
else ''
end)

And then it's a simple text comparison check in FastReport.
Derek.

Re: Filtering in FastReport (still working on a couple of issues)

Derek,
I created a calculated field with your provided code.
I put a test Text field on my Equip form and it looks like it does know the difference between a SPARE and an INSTALLED setting.  Since a SPARE has no Service records, it returns a blank as it should.

However for the other INSTALLED records it returns 'overdue' for all of them, even when I put the next service due date way in advance.
.
Maybe I'm doing something wrong?
Thanks, Frank