Topic: Calculate or count fields with text.

Hello friends.
I would like to know if it is possible to do the sum of fields with text and that gave me the result QUANT and TOTAL in numbers as shown in the image.
Each individual who receives a PROC action that will be added to the next PROC if it suffers more than one action.
I searched for a SQL code but did not find it.
Thank you

Post's attachments

Attachment icon Image 1.jpg 56.5 kb, 205 downloads since 2018-08-21 

Re: Calculate or count fields with text.

Hi Lejoso,
I think doing it with a calculated field is probably the easiest and most data-efficient, but there are other ways as well.
Please have a look at the attached example and see if it helps.
Regards,
Derek.

Post's attachments

Attachment icon lejeso.zip 336.61 kb, 522 downloads since 2018-08-21 

3 (edited by lejoso 2018-08-21 21:06:25)

Re: Calculate or count fields with text.

Wow, amazing

That's exactly what I need, works perfectly. I'll use it on my project.
I'm using combobox in my project with every proc in a table ... Will it work?
Where do I learn this code that you used in the calculated field?
I had not seen him here in the forum yet.
Then I'd like to learn other ways to do that.

Many, many thanks Derek

by Google Translator

4 (edited by derek 2018-08-21 23:00:31)

Re: Calculate or count fields with text.

Hi Lejoso,
Yes, you can use comboboxes instead of fields - the logic is the same.
But when you use comboboxes, you need a second table (I have called it 'types') where you enter the type of 'proc'.
Because you have 3 Procs, you have to define the relationship between the 2 tables 3 times.  And then you also need to create two extra calculated fields to hold the description of Proc2 and Proc3 so that you can see it in the tablegrid.
Please have a look at the attachment and I'm sure you'll be able to follow what I've done.
Regards,
Derek.

Post's attachments

Attachment icon lejoso with comboboxes.zip 338.33 kb, 465 downloads since 2018-08-22 

Re: Calculate or count fields with text.

Hi Derek
Thank you very much for your support.
That's perfect, but I can not generate a report.
Instead of adding it, it's putting the QTY in line.
I searched for a way to do it in SQL and found the CAST function ... I do not know if it would do.
Thank you

Post's attachments

Attachment icon lejoso with comboboxes.rar 5.78 kb, 530 downloads since 2018-08-22 

Re: Calculate or count fields with text.

lejoso,
Just remove the quotes around the result field (after then) in Derek's calculate field. It makes the field numeric and your report will sum properly.


See below:


case 
when id_types  is     null and id_types1 is     null and id_types2 is     null then 0 
when id_types  is not null and id_types1 is     null and id_types2 is     null then 1 
when id_types  is not null and id_types1 is not null and id_types2 is     null then 2   
when id_types  is not null and id_types1 is null     and id_types2 is not null then 2 
when id_types  is not null and id_types1 is not null and id_types2 is not null then 3 

when id_types1 is     null and id_types  is     null and id_types2 is     null then 0 
when id_types1 is not null and id_types  is     null and id_types2 is     null then 1 
when id_types1 is not null and id_types  is not null and id_types2 is     null then 2   
when id_types1 is not null and id_types  is null     and id_types2 is not null then 2 
when id_types1 is not null and id_types  is not null and id_types2 is not null then 3 

when id_types2 is     null and id_types  is     null and id_types1 is     null then 0 
when id_types2 is not null and id_types  is     null and id_types1 is     null then 1 
when id_types2 is not null and id_types  is not null and id_types1 is     null then 2   
when id_types2 is not null and id_types  is null     and id_types1 is not null then 2 
when id_types2 is not null and id_types  is not null and id_types1 is not null then 3   
end

Re: Calculate or count fields with text.

Hi ehwagner
Many thanks for this tip. Now it works the way I wanted it to.
thankful

Re: Calculate or count fields with text.

Hello, Derek and Ehwagner.
I am not able to report with GROUP BY of proc.
Is there any example in the forum that I can follow?
Thank you

Re: Calculate or count fields with text.

lejoso wrote:

Hello, Derek and Ehwagner.
I am not able to report with GROUP BY of proc.
Is there any example in the forum that I can follow?
Thank you

An example for you:

Post's attachments

Attachment icon Report group.zip 8.84 kb, 450 downloads since 2018-09-10 

Dmitry.

Re: Calculate or count fields with text.

Thank you, Mr Dmitry.
I will try to adapt it to my project. I think it will work.
Best reggards

Re: Calculate or count fields with text.

Hello Mr. Dimtry
Unfortunately I am not able to implement the report in my project with this example.
Was there any other in the forum?
Thanks

Re: Calculate or count fields with text.

lejoso wrote:

Hello Mr. Dimtry
Unfortunately I am not able to implement the report in my project with this example.
Was there any other in the forum?
Thanks

Please attach your project, also describe what report you need.

Dmitry.

Re: Calculate or count fields with text.

Unfortunately I can't find solution for your case.

Dmitry.

Re: Calculate or count fields with text.

No problem. Thanks for trying.
I'll look for another way.