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

Topic: Save the results of a Calculated field?

Hi Everyone,
.
Can you save the results of a calculated field into a text field in a table?
.
I'm trying to save this result in a table field so I can use that saved result in a Report(SQL)  ORDER BY to sort my report.
.
I'm not at all sure if this the way to do it??
.
Any suggestions on how to sort a Report(SQL) will be appreciated.
.
Thanks
Frank

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,676

Re: Save the results of a Calculated field?

Hi Frank,
Not sure I understand the question.
Are you trying to use a calculated field as a sort sequence in your report(sql)?
If so,
1.  for the calculated field, you can just replicate the syntax in the report(sql) selection (and optionally give it a working name) - ie
select qty * price as cost,. 
Saving a calculated field to a table would kind of defeat the purpose of using a calculated field in the first place.
2.  for the sort sequence, you can either repeat the calculation in the report(sql) order by clause (ie  order by qty * price) or use its working name (ie order by cost).
To make the sort sequence a bit more flexible, you could also click on a discrete tablegrid column header  and use that as the column to sort on in the report.
Please see the attachment.
Derek.

Post's attachments

Attachment icon reportsql sort.zip 338.09 kb, 154 downloads since 2022-07-29 

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

Re: Save the results of a Calculated field?

Derek,

Thanks for the suggestions.
I thought I had tried both #1 & #2 however they didn't work.
.
Then after I saw your suggestions I went back & tried #1 again.
This time I added a field called NextSvcDate to my table.
.
Then I added ORDER BY NextSvcDate to my Report SQL statement and it works.
.
NextSvcDate is the Name of my Calculated field.
.
Nothing gets saved in this field in my table however which seems strange to me, but the Calculation does happen & shows up on my form.
So I'm guessing that the Report(SQL) picks up the calculated result.
.
This probably sounds confusing to you and it sure is to me.  But I get the results I was looking for so I'm not going to mess with it.  I feel like the blind squirrel who just found an acorn LOL.
.
Thanks for your help as always
Frank

4 Reply by papafrankc (edited by papafrankc 2022-08-05 06:26:25)

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

Re: Save the results of a Calculated field?

Derek,

It turns out that my weird fix didn't work after all.  When I put in more test data the dates were coming out wrong.
.
So I went back into my Report(SQL) and used the Order By with my Calculated Field and I changed the date to Year,Month,Day - strftime('%Y/%m/%d',date(julianday(.... and it's now sorting my report correctly by the Calculated Date.
.
Thanks for the suggestions
Frank