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.
My Visual Database → General → 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.
.
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.
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.
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.
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.
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
Hello sibprogsistem
I did not understand...
Thank you anyway
Hi Derek.
I can't get the same result as the calculated field "cfcidsearch" in MySql.
Is there anything I can do?
Thanks.
Lejoso
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.
Hello Derek
I understand.
I will continue searching the forum for a solution.
Thank you very much.
Best Regards
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),'')
попробуйте использовать эти скобки
ifnull((SELECT cid_a.cid_num FROM cid_a WHERE cid_a.id=bienal.id_cid_a),``)||` `||
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),'')||' '||
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.
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
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)
)
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.
My Visual Database → General → Search button stops working....
Powered by PunBB, supported by Informer Technologies, Inc.
Theme Hydrogen by Kushi