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

Topic: Format a Calculated Field

Hi All,
I'm trying to SORT a tblGrid using a Calculated Field.  But it's not working.  Here's what I have:
.
OPTION 1
calcNextSvcDate =
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)))
.
OPTION 2
calcYMDnextSvcDate =
strftime('%Y/%m/%d',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))) 
.
When I use calcNextSvcDate the tblGrid does not sort by this column.
When I use calcYMDnextSvcDate it is working OK.
.
So that's good news for the SORTING issue.  However my problem is that the dates in the cells for option 2 are showing as Y m d and since I'm in the US, I would like it to show m d Y
.
First, is there any way to format the data in the cells in OPTION 2 ?
Second, is maybe my approach to the SORTING issue maybe not the correct way to do it ?
.
Thanks
Frank

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Format a Calculated Field

Hi Frank,

Modify the second calculated field :

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))

.
Place both fields in the table.
Do sorting by 2 fields but make it invisible in the script.
Display 1 field.
Or ...
Could you attach a project to understand the details of the problem and other solutions?

  • k245
  • k245
  • Member
  • Offline
  • From: Deep
  • Registered: 2017-10-12
  • Posts: 1,532

Re: Format a Calculated Field

Use calculated fields with care - piling them up can be detrimental to performance. To select data with complex processing, it is better to use one SQL query, written and optimized manually.

Визуальное программирование: блог и телеграм-канал.

k245's Website

4 Reply by sparrow (edited by sparrow 2022-08-16 12:26:03)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Format a Calculated Field

Suggested solution based on limited information received.
That's why I suggested showing the project.
Of course, it is better to store a ready date for the next service than to calculate each time.
Date and time is a very delicate thing in both calculation and display and sorting.
But each project has its own owner. )))

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

Re: Format a Calculated Field

Sparrow, k245
Thank you for the suggestions.  You've given me some things to think about.
.
I like Sparrows idea of storing the NEXT SVC DATE in a field instead of using a Calculated field.  Calculated fields are nice but there seems to be some restrictions when I want to use the info in that field to do something because it's not really saved data.
.
I'll have to change some stuff to see if I can make it work but it could be worth it.  I'll let you know how it goes.
.
Thanks
Frank

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

Re: Format a Calculated Field

Sparrow,
Do you know how I can save the results of OPTION 2 into a Text field.  I need that result to calculate when the next Service Due Date is? 
.
The calculation works but I only seem to be able to save the result in a calculated field.  I'm thinking if I had the result in a Text field then I could use it going forward.
.
Maybe this is not the best solution?
.
Thanks
Frank

  • Registered: 2022-08-03
  • Posts: 89

Re: Format a Calculated Field

Bonjour a tous
J'ai besoin de votre aide
j'ai une base que gère les devis avec un compteur et sont propre numéro de document.
je voulais a partir du devis établir une facture avec sont propre numéro de document.

On peux le faire avec MVDB

Merci de votre aide

Niko

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Format a Calculated Field

Hi all,

You are a little confused.
A calculated field is not a table field. This is part of the SQL query, the so-called subquery. It's impossible to save it.
Here is an example of how this can be done in the program, if I understand you correctly. Option 2 in the example is recommended.

Post's attachments

Attachment icon svc.zip 334.81 kb, 176 downloads since 2022-08-17 

9 Reply by papafrankc (edited by papafrankc 2022-08-20 02:31:11)

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

Re: Format a Calculated Field

Sparrow,
I've been going over your example program (svc.zip) and it looks really good.
I've been wanting to save the results of a calculation into a field before and your solution shows me how to do it.
I believe using this approach will simplify things for me considerably.
.
While trying to implement it into my program I'm realizing I believe I have some design flaws in my program.
So it's going to take some work to make the necessary changes.
.
*** When I first posted this Reply I referred to add2 as a function or procedure.  Sorry I didn't realize that add2 is the name of the form.  I had a senior moment.
.
I will let you know if I have any problems.
(I have a tendency to get myself out into the weeds from time to time so I'm sure I'll be back)
.
Thanks so much for another valuable lesson.
Frank