• Registered: 2020-06-26
  • Posts: 302

Topic: Report(SQL) filtering

Hello,
I'm using Report(SQL) to create a report and I have an issue.
I'm trying to add some child records to the bottom of my Equipment report.
(please see the attached PDF file with the details)
.
Everything works fine...except for when I try to add the child records from the Service table.
I'm getting the service records from ALL of the service records on file.  Even the ones for other pieces of equipment.
I want to see ONLY the service record for Equip Name = New 1
But you can see on the report that I'm getting service records for Equip Name = New 2 also.
.
Thanks, Frank

Post's attachments

Attachment icon MY REPORT.pdf 110.07 kb, 201 downloads since 2021-10-08 

2 Reply by brian.zaballa (edited by brian.zaballa 2021-10-08 08:30:34)

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Report(SQL) filtering

It's hard to find the problem here Frank. I suggest you put all the SQL query here and maybe someone can point it out.


Here, I made an example. I hope you can get an idea from it.


On frmEquip, there are 2 Report buttons. First button will not work(not really, it just wont display equipment details) when no service is added to an equipment.


Report 2, on the other hand will display equipment even without service is added but will require additional script(well you can use that script later on especially when working with multi-table to display in a report)


PS: By the way, when you said tbl_SvcProc is a child, you mean in a service record you will have one or more procedure/s? I only make it as a connection in my sample

Post's attachments

Attachment icon Frank_Equip.zip 555.32 kb, 188 downloads since 2021-10-08 

brian

3 Reply by papafrankc (edited by papafrankc 2021-10-08 21:31:26)

  • Registered: 2020-06-26
  • Posts: 302

Re: Report(SQL) filtering

Brian,
Thanks for looking into this for me.
When I was looking over your example I noticed that you were using REPORT and not REPORT(SQL) as I was trying to do.
So I went back to my original REPORT solution I had tried and took another look.
.
I've been using REPORT quite a bit but I now realize I didn't fully understand what could be done with it.
I have attached an image so you can see what I'm referring to.
The 2 tables I'm using are tbl_Equip and tbl_Service
.
Here's the settings when I was setting up the report:
1 - Select the components for the search
    . I have EqName from tbl_Equip selected here and that seems to be OK.
2 - Select the database table for the query
    . I originally had tbl_Equip selected here.  I thought since most of my data was
    . coming from this table that it should be used here.
    . However when I ran the report I was getting ALL of the records from the tbl_Service
    . table when I only wanted the ones that were associated with this one piece of
    . equipment.
    . So, just out of curiosity I changed #2 to tbl_Service and WOW like magic the
    . report  returns the data from my Equip record and ONLY the data from the
    . associated records in the Service table.
    . MVD took care of the linking between the 2 tables automatically.
    . I think there's a saying, something about 'even a blind squirrel finds an acorn
    . once in a while' ......... that's me smile
3 - The Result:
    . I just put the fields that I want in the report here.
.
I'm not sure I fully understand how this all works.  It may be written up in some MVD manual but I just didn't notice it.
.
And what's great about this solution is that it does what I needed with out any script or coding.
.
I may be explaining something that most everyone knows but for other newbies like myself maybe this can help.
.
Thanks for all of your outstanding support on my journey into MVD.  I'm definitely learning and would not be nearly as far along as I am now without all the help from the Forum.
.
Frank

Post's attachments

Attachment icon Report for Services.pdf 108.55 kb, 175 downloads since 2021-10-08 

4 Reply by brian.zaballa (edited by brian.zaballa 2021-10-09 03:15:57)

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Report(SQL) filtering

Frank,


Glad that you have fixed it. Well, MVD's really great in creating that kind of report for you. I've been trough a lot of SQL coding before(Web Development) just to create such reports and MVD simplified it all.


To fully understand what MVD's doing, you must learn a little bit of SQL Queries. You really must select from tbl_Service since that table handles the record(Master data) you are trying to display. You just have to LEFT JOIN it(Service) to tbl_Equp table to get the equip information.


MVD automatically creates that left join when you add equip field in your report. MVD will look on the reference for that table using the main table you select(tbl_Service in this case). MVD will find that reference(id_tbl_Equip) and join it to the table (tbl_Equip) to get equip details you ask MVD to fetch. Then if you add another table(in my example tbl_technician, it will look for id_tbl_technician and LEFT JOIN it to tbl_technician), and so on...


To sum it up, MVD creates SQL something like this behind that report button

SELECT
   tbl_Equip.eqName AS "tbl_Equip_eqName", 
   tbl_Service.s_date, 
   tbl_technician.t_name
FROM tbl_Service
LEFT JOIN tbl_Equip ON tbl_Equip.id = tbl_Service.id_tbl_Equp
LEFT JOIN tbl_technician ON tbl_technician.id = tbl_Service.id_tbl_technician 
WHERE tbl_Equip.eqNAME = "[Text_from_eqName_on_Form]"

Well, MVD is assigning each fields an alias using "AS" like I done in the first field.


Note: Just be careful with that kind of filter you used (eQName), Having 2 or more equipment with the same Name will ruin your report. It will display all services for all that equipment.

In deployment, there's more likely no equipment with the same name thou but if your application doesn't check it upon saving, then your application can have 2 or more equipment with the same(exact) name

brian
  • Registered: 2020-06-26
  • Posts: 302

Re: Report(SQL) filtering

Brian,
Thanks for the explanation, I believe I understand a little more about SQL queries now.
.
And thanks for the heads up about duplicate field names.  I did some testing and yes, duplicate names does break the report.
.
I looked on the forum and thanks to ehwagner on 2017-05-01 I found some code that works great:
If SqlExecute('Select Count(Id) From MyTable Where Col1 = "' + Form2.Edit_Col1.Text + '"') > 0 then
      Begin
         MessageBox('Duplicate Record Exists - Cannot Save','Error',MB_OK+MB_ICONERROR);
         Cancel := True;
         Form2.Edit_Col1.SetFocus;
         Exit;
      End;
.
I just changed the table and column names and it works great.
.
Thanks again for your help, Frank

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Report(SQL) filtering

You're welcome.

I got here a procedure focusing the textbox. It's out of the topic but can help you save some time and have a better User Experience for your application

{
    Focus and select all text of a textbox
}
procedure FocusTBox(tb: TdbEdit);
begin
    tb.SelectAll;
    tb.SetFocus;
end;

Add it on your script, then change

Form2.Edit_Col1.SetFocus;

to

FocusTBox(Form2.Edit_Col1);
brian

7 Reply by papafrankc (edited by papafrankc 2021-10-10 03:01:50)

  • Registered: 2020-06-26
  • Posts: 302

Re: Report(SQL) filtering

Brian,
I found a problem when using the code I mentioned about checking for Duplicate Names.
It works OK for a NEW record however it fails when EDITING an existing record. For an EXISTING record it does the test and sees that the Equipment Name already exists.  So it won't let me modify and save an existing record.
.
I'm trying to limit it to only NEW records using an if Action... but I'm not having any luck so far.
.
SOLVED - for some reason I was trying to use an If Action... statement and it was failing on the word ACTION.
So I used if frmEquip.dbAction = 'NewRecord' then and it works smile
.
ONE MORE QUESTION: (there's always one more, right)
This solution is case sensitive, so when I put in Flag and flag it says it is OK. I know some people just type in words and don't capitalize the first letters.
So I'd like to be able to compare the names for both Upper and Lower case.  I've tried upper() and uppercase() and I'm not getting them to work.
.
Thanks, Frank

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Report(SQL) filtering

I'm not sure if this is what you are looking. It's a workaround on checking exact string

function CheckExactString(s1: String; s2: String): boolean;
begin
    result :=  (((PosEx(s1, s2)) = 1) AND (Length(s1)=Length(s2)));
end;
brian
  • Registered: 2020-06-26
  • Posts: 302

Re: Report(SQL) filtering

Brian,
I notice you've provided a Function.
Unfortunately I've only used Procedures in my script files.
Can you please tell me how to use it?
Thanks,
Frank

10 Reply by brian.zaballa (edited by brian.zaballa 2021-10-11 05:01:16)

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Report(SQL) filtering

procedures => do algorithm and/or process
function => will do algorithm and/or process then return a value

That function returns a boolean value so can use it on a condition like

if CheckExactString('Flag', 'Flag') then
begin
  // do something
end;

or


// If not the same
if (Not CheckExactString('Flag', 'flag')) then
begin
  // do something
end;
Post's attachments

Attachment icon tesst.zip 546.97 kb, 175 downloads since 2021-10-11 

brian

11 Reply by brian.zaballa (edited by brian.zaballa 2021-10-11 05:55:05)

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Report(SQL) filtering

if you are using this code

If SqlExecute('Select Count(Id) From MyTable Where Col1 = "' + Form2.Edit_Col1.Text + '"') > 0 then
      Begin
         MessageBox('Duplicate Record Exists - Cannot Save','Error',MB_OK+MB_ICONERROR);
         Cancel := True;
         Form2.Edit_Col1.SetFocus;
         Exit;
      End;

Then, you might want to change it to

procedure Save_Button_OnCLick();
var
   _chk: String;
begin
   _chk := SQLExecute('SELECT COALESCE(Col1, '''') FROM MyTable WHERE Col1 LIKE "' + Form2.Edit_Col1.Text + '" LIMIT 1');
   if CheckExactString(Form2.Edit_Col1.Text, _chk) then
   begin
         Cancel := True;
         Form2.Edit_Col1.SetFocus;
         Exit;
   end;
end;
brian
  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Report(SQL) filtering

I found other fix to your problem, without using the function I gave, you can have extra sql query to get what you want

SQLExecute('PRAGMA case_sensitive_like=OFF');
If SqlExecute('Select Count(Id) From MyTable Where Col1 = "' + Form2.Edit_Col1.Text + '"') > 0 then
Begin
         MessageBox('Duplicate Record Exists - Cannot Save','Error',MB_OK+MB_ICONERROR);
         Cancel := True;
         Form2.Edit_Col1.SetFocus;
End;
SQLExecute('PRAGMA case_sensitive_like=ON'); // make sure to turn it on
brian