Topic: Search button stops working....

Hello everyone.
Could someone tell me why the search button stops working when I do a search using the "CID" field.
Project attached.
Thank you very much.

Post's attachments

Attachment icon BIENAL CONTROLE.zip 585.47 kb, 324 downloads since 2019-10-11 

Re: Search button stops working....

.

Post's attachments

Attachment icon BIENAL CONTROLE.7z 361.64 kb, 319 downloads since 2019-10-11 

3 (edited by derek 2019-10-11 16:59:21)

Re: Search button stops working....

Hi Lejoso, Hi Sibprogsistem,
I took a different approach and used a calculated field (cfcidsearch) for all of the 'CID' searches;  it seems to work fine.
A couple of questions
1.  why does form1.badd call 'show form' instead of 'new record'?
2.  is there a reason why you couldn't link bienal / cid_a in a one to many relationship rather than using cid_a as a lookup that you repeat 12 times?  It's not really 'relational' in the true sense.  And what if there is more than 12?
3.  You have 14,448 records in a look-up table (cid_a)?  I'm surprised users ever manage to select the correct code?
Regards,
Derek.

Post's attachments

Attachment icon BIENAL CONTROLE suggestion.zip 925.87 kb, 327 downloads since 2019-10-11 

Re: Search button stops working....

Hi Mr. Derek
Thank you very much for welcoming my project.
The correction is perfect and meets exactly what I need.
As for your questions:
1. It was a slip of mine. As I am new to MVD I sometimes make these mistakes.
2. I tried to do it this way, but couldn't make a TableGrid compatible with the need for my project. I know there is a more coherent form and at some point I will find it.
3. Users register according to a previously completed form.

I have noticed in many projects that “Calculated Field” solves most of MVD users' problems. It really is very powerful and makes the script cleaner.
If possible, I would like you to give me a place or a way for me to study this tool further.
Sorry for English, my language is Portuguese and I am using Google Translator.
Again my thank you very much.

Re: Search button stops working....

Hello again Mr. Derek

I have a new problem. I need the CID field lookup to return me exactly the code described, for example R63.
But the search also returns other records that have R63, such as R635 ...
Unfortunately I need the value to be exact.
See in the picture.
It's possible?

Thank you very much.

Post's attachments

Attachment icon Imagem 1.jpg 120.34 kb, 139 downloads since 2019-10-16 

6 (edited by derek 2019-10-16 00:32:27)

Re: Search button stops working....

Hi Lejoso,
If you need to see only R63 and not R63, R635 etc, I would suggest changing the calculated field (cfcidsearch) to insert a blank space after every CID number in cfcidsearch (please see the attached amended project).
So now you can enter R (23 records), R6 (13 records), R63 (13 records),  R63 and a space (1  record) or  R635 (12 records) etc. 
This gives you a lot of flexibility to filter in different ways and might be useful as your CID numbers appear to have a structured hierarchy (A00 is for different types of Cholera, A16 for different types of Tuberculosis etc etc).
I've also added a bit of script so that when you single click on a CID number on Form1.tablegrid1, it will show you its description;  because there are so many codes, perhaps this will help your users.
Regards,
Derek.

Post's attachments

Attachment icon BIENAL CONTROLE suggestion.zip 928.09 kb, 340 downloads since 2019-10-16 

Re: Search button stops working....

Hello Mr. Derek

Thanks again.
It's perfect ... and the script you put in got even better.
My users and I appreciate it.

Best regards

Re: Search button stops working....

Hello sibprogsistem

I did not understand...
Thank you anyway

Re: Search button stops working....

Hi Derek.

I can't get the same result as the calculated field "cfcidsearch" in MySql.
Is there anything I can do?
Thanks.

Lejoso

Re: Search button stops working....

Hi Lejoso,
I understand that some elements of code (but not all) are usable in both SQLite and MySql without needing to be amended.
But unfortunately, I don't use MySql at all so can't say why you don't get the same result and what exactly the problem is.
Derek.

Re: Search button stops working....

Hello Derek

I understand.
I will continue searching the forum for a solution.
Thank you very much.

Best Regards

Re: Search button stops working....

Hello everyone.

Could someone tell me why this code does not work on MySql?

Thanks a lot.

ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a),'')||' '||     
ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a1),'')||' '||   
ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a2),'')||' '||    
ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a3),'')||' '||    
ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a4),'')||' '||    
ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a5),'')||' '||    
ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a6),'')||' '||    
ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a7),'')||' '||            
ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a8),'')||' '||    
ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a9),'')||' '||    
ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a10),'')||' '||   
ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a11),'')

13 (edited by sibprogsistem 2019-10-23 15:02:02)

Re: Search button stops working....

попробуйте использовать эти скобки

ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a),``)||` `||     

14 (edited by thezimguy 2019-10-23 15:11:11)

Re: Search button stops working....

The where is referencing two tables but the from is one table

ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a),'')||' '||

Try this

ifnull((SELECT cid_a.cid_num FROM cid_a, bienal WHERE cid_a.id=bienal.id_cid_a),'')||' '||
@thezimguy

Re: Search button stops working....

I do not believe you can use the bars (||) in MySQL. You use Concat() function instead. So if you place all your ifnull statements inside the parentheses of the Concat() function and replace all the bars(||) with a comma you should get your results.

Re: Search button stops working....

Hello sibprogsistem, thezimguy and ehwagner.

Thank you so much for your interest in helping.
I will test the suggestions in my project and as soon as possible I will give an answer.

Thanks

Re: Search button stops working....

Hello friends.
I was able to solve my problem:

concat(
  concat(
    concat(
      concat(
        concat(
          concat(
            concat(
              concat(
                concat(
                  concat(
                    concat(
                      concat(
                        concat(
                          concat(
                            concat(
                              concat(
                                concat(
                                  concat(
                                    concat(
                                      concat(
                                        concat(
                                          concat(
                                            cast(ifnull((
                                              select cid_a.cid_num
                                              from cid_a
                                              where cid_a.id = bienal.id_cid_a
                                            ), '') as char), 
                                            ' '
                                          ), 
                                          cast(ifnull((
                                            select cid_a.cid_num
                                            from cid_a
                                            where cid_a.id = bienal.id_cid_a1
                                          ), '') as char)
                                        ), 
                                        ' '
                                      ), 
                                      cast(ifnull((
                                        select cid_a.cid_num
                                        from cid_a
                                        where cid_a.id = bienal.id_cid_a2
                                      ), '') as char)
                                    ), 
                                    ' '
                                  ), 
                                  cast(ifnull((
                                    select cid_a.cid_num
                                    from cid_a
                                    where cid_a.id = bienal.id_cid_a3
                                  ), '') as char)
                                ), 
                                ' '
                              ), 
                              cast(ifnull((
                                select cid_a.cid_num
                                from cid_a
                                where cid_a.id = bienal.id_cid_a4
                              ), '') as char)
                            ), 
                            ' '
                          ), 
                          cast(ifnull((
                            select cid_a.cid_num
                            from cid_a
                            where cid_a.id = bienal.id_cid_a5
                          ), '') as char)
                        ), 
                        ' '
                      ), 
                      cast(ifnull((
                        select cid_a.cid_num
                        from cid_a
                        where cid_a.id = bienal.id_cid_a6
                      ), '') as char)
                    ), 
                    ' '
                  ), 
                  cast(ifnull((
                    select cid_a.cid_num
                    from cid_a
                    where cid_a.id = bienal.id_cid_a7
                  ), '') as char)
                ), 
                ' '
              ), 
              cast(ifnull((
                select cid_a.cid_num
                from cid_a
                where cid_a.id = bienal.id_cid_a8
              ), '') as char)
            ), 
            ' '
          ), 
          cast(ifnull((
            select cid_a.cid_num
            from cid_a
            where cid_a.id = bienal.id_cid_a9
          ), '') as char)
        ), 
        ' '
      ), 
      cast(ifnull((
        select cid_a.cid_num
        from cid_a
        where cid_a.id = bienal.id_cid_a10
      ), '') as char)
    ), 
    ' '
  ), 
  cast(ifnull((
    select cid_a.cid_num
    from cid_a
    where cid_a.id = bienal.id_cid_a11
  ), '') as char)
)

Re: Search button stops working....

Hello friends.
Looking at my old posts I saw that I forgot to indicate how I solved this problem of mine, without forgetting the valuable contribution of Derek, Sibprogsistem, Thezimguy and Ehwagner.
I used the SQLite code, which Derek very kindly made for my project, on the code translation website https://www.jooq.org/translate/.
Just paste the SQL code and choose the output reference, it solved perfectly for me.
I hope I've helped.
Greetings from Brazil.