• Registered: 2019-12-24
  • Posts: 169

Topic: Reference a Form's Field in a cfField?

Hello again folks,
.
Is it possible to reference a form's date field in a cfField formula?
.
Currently cfVacEarned is:

ifnull(employee.dateHireMod,0) + 
case when round((julianday('now') - julianday(employee.dateHire))/(365/12)) > 180 then '20'
when round((julianday('now') - julianday(employee.dateHire))/(365/12))> 120 then '15'
when round((julianday('now') - julianday(employee.dateHire))/(365/12))> 60 then '10'
when round((julianday('now') - julianday(employee.dateHire))/(365/12))> 12 then '5'
else '0'
end

This is always referencing today's date.
.
I'm trying to allow users specify a date (mainly for reporting) via the new Date Point field.  Something like this:

ifnull(employee.dateHireMod,0) + 
case when round(({frmMain.dtpDatePoint.SqlDate} - julianday(employee.dateHire))/(365/12)) > 180 then '20'
when round(({frmMain.dtpDatePoint.SqlDate} - julianday(employee.dateHire))/(365/12))> 120 then '15'
when round(({frmMain.dtpDatePoint.SqlDate} - julianday(employee.dateHire))/(365/12))> 60 then '10'
when round(({frmMain.dtpDatePoint.SqlDate} - julianday(employee.dateHire))/(365/12))> 12 then '5'
else '0'
end

This doesn't work, but is there a way to do this?  Or could someone offer ideas on how to accomplish it?
.
Thank you in advance,
-joshuA

Post's attachments

Attachment icon v6.2.zip 349.79 kb, 127 downloads since 2021-12-12 

"Energy and persistence conquer all things."

2 Reply by k245 (edited by k245 2021-12-12 10:25:12)

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

Re: Reference a Form's Field in a cfField?

This problem can be solved if you add a table with a field of the "Date" type and one record, and then use this field in the SQL-query, Including in calculated fields.


For example:


Table - Config
Field - CurrentDate

ifnull(employee.dateHireMod,0) + 
case when round((julianday( select currentDate from config ) - julianday(employee.dateHire))/(365/12)) > 180 then '20'
when round((julianday( select currentDate from config ) - julianday(employee.dateHire))/(365/12))> 120 then '15'
when round((julianday( select currentDate from config ) - julianday(employee.dateHire))/(365/12))> 60 then '10'
when round((julianday( select currentDate from config ) - julianday(employee.dateHire))/(365/12))> 12 then '5'
else '0'
end
Визуальное программирование: блог и телеграм-канал.

k245's Website

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

Re: Reference a Form's Field in a cfField?

Hi all,
In my opinion, it's time to write a SQL query and use it to search in Button2
as an incremental search.

  • Registered: 2019-12-24
  • Posts: 169

Re: Reference a Form's Field in a cfField?

Hi k245, sparrow,
.
I appreciate both of your comments.
.
Without changing my search functions already in place, I'm going to try the config table approach (for now).  Others have suggested similar per-user "persistent settings" like this, so I think this will serve for other features down the road too.  I have been putting this idea off up until now, so I guess it's time to explore it a bit.
.
I plan to include another example once I get it working smile
.
Again, thank you both
-joshuA

"Energy and persistence conquer all things."
  • Registered: 2019-12-24
  • Posts: 169

Re: Reference a Form's Field in a cfField?

So, I'm just curious...
.
When Role-based access is enabled, there exists a table named _user.
.
Would it be bad practice to add these "config" related fields to the _user table since they're specific to each user?
.
This may be a question of preference, so if it is... I'm not trying to start an argument over it.  But I am interested in anyone's opinions.
.
-joshuA

"Energy and persistence conquer all things."