• 0

calculation in a rule

Question

i want to make a rule in which if x field is less than 2 years from 2022

then hide this section

the problem is that there is no option to do 2 years from 2022

so what is other ways to achieve this

i tried using vrtual field to calculate 2 years from system date

but it also shows the day month and time as well

and then i cant use for the rule

this all i want to do in submission form

Recommended Posts

• 1
On 10/3/2022 at 9:40 AM, DRAhmed said:

i also want to have a table with all dates in format mm/yyyy from 1/1940 to 12/2050

but dont know how to do

You can use the DatePart function in a formula field to have this format for your date values. For example:

DatePart(month, [@field:DateField]) + '/' + DatePart(year, [@field:DateField])

Share on other sites

• 1

Hello @DRAhmed,

It is definitely possible to calculate difference in years.  Could you provide more details to suggest you the solution that works as expected?

1) Should 2022 be hardcoded? Or do you need to use the current year (for example, in 2023 you need to use 2023 for the rule)?

2) Since it is a Submission form I assume that the user enters the date that you want to use to calculate the difference in years.

Is it a full date like 05/01/2020? Or is is just a year in the Text(255), Number or Integer field like 2020?

3) If the user only enters a year and you need to calculate the difference between the entered year and the current year, you may test these formulas (replace the [@field:year_field]   with your field name):

If the year has the Integer or Number data type:      DatePart (year, SysUTCDateTime()) - [@field:year_field]

If the year has the Text(255) data type:                          DatePart (year, SysUTCDateTime()) - CAST ( [@field:year_field] as int)

Share on other sites

• 1

Hello @DRAhmed,

I am glad that you got the formula you need.

If I understood you correctly, you want to concatenate 2 values.

First of all, you may combine several conditions in the WHEN  condition.

For example:

CASE
WHEN (DatePart (year, SysUTCDateTime()) - CAST ([@field:bach_year] as int) >= 2) AND [@field:specialization] = 'cardiologist'
THEN 'spec cardiologist'

WHEN (DatePart (year, SysUTCDateTime()) - CAST ([@field:bach_year] as int) >= 2) AND [@field:specialization] = 'therapist'
THEN 'spec therapist'

ELSE 'a'
END

Another option:

If you do not need to check the value in that x field, but just to add it to the result you may try this:

CASE
WHEN DatePart (year, SysUTCDateTime()) - CAST ([@field:bach_year] as int) >= 2
THEN 'spec'+space(1)+'[@field:specialization]'
ELSE 'a'
END

If this does not work, please update this thread with more details and I will try to help.

Share on other sites

• 1

Hello @DRAhmed

If you apply the formula on the DataPage, this one should calculate the difference between the the date in format mm/yyyy and the today`s date in months:

DateDiff(month, (TRY_CONVERT(date, (LEFT([@field:Date],2)+'/01/'+RIGHT([@field:Date],4)), 101)), SysUTCDateTime())

Replace the [@field:Date] with the field that stores date in mm/yyyy format.

I am afraid that I have no the table example that you mentioned. However, I believe it is possible to generate the values in Excel, for example

Share on other sites

• 1
On 10/3/2022 at 4:41 AM, DRAhmed said:

i have another question if you could help

if it than difference is more than 2 years it will show spec , i want to make it spec + whatever is chosen in another field

like for example if in field x it chosen cardiologist and difference in years is more than 2 years  , then it should show " spec cardiologist "

Hello. You can just add the parameter for that field by choosing it in the field picker. For example:

Case
WHEN DatePart (year, SysUTCDateTime()) - CAST ( [@field:bach_year] as int) >= 2
Then "spec" + '' + [@field:FieldName]
Else "a"
End

Share on other sites

• 0
2 hours ago, CoopperBackpack said:

Hello @DRAhmed,

It is definitely possible to calculate difference in years.  Could you provide more details to suggest you the solution that works as expected?

1) Should 2022 be hardcoded? Or do you need to use the current year (for example, in 2023 you need to use 2023 for the rule)?

2) Since it is a Submission form I assume that the user enters the date that you want to use to calculate the difference in years.

Is it a full date like 05/01/2020? Or is is just a year in the Text(255), Number or Integer field like 2020?

3) If the user only enters a year and you need to calculate the difference between the entered year and the current year, you may test these formulas (replace the [@field:year_field]   with your field name):

If the year has the Integer or Number data type:      DatePart (year, SysUTCDateTime()) - [@field:year_field]

If the year has the Text(255) data type:                          DatePart (year, SysUTCDateTime()) - CAST ( [@field:year_field] as int)

1 ) current year , but only year without month,day or time

2) only year ( dropdown ) from a table i made with all years from 1950 till 2022

3) i wrote the code like

case
WHEN DatePart (year, SysUTCDateTime()) - CAST ( [@field:bach_year] as int) >= 2
Then "spec"
Else "a"
end

it did what i wanted to do

so thank you alot

i have another question if you could help

if it than difference is more than 2 years it will show spec , i want to make it spec + whatever is chosen in another field

like for example if in field x it chosen cardiologist and difference in years is more than 2 years  , then it should show " spec cardiologist "

Share on other sites

• 0
2 hours ago, CoopperBackpack said:

Hello @DRAhmed,

I am glad that you got the formula you need.

If I understood you correctly, you want to concatenate 2 values.

First of all, you may combine several conditions in the WHEN  condition.

For example:

CASE
WHEN (DatePart (year, SysUTCDateTime()) - CAST ([@field:bach_year] as int) >= 2) AND [@field:specialization] = 'cardiologist'
THEN 'spec cardiologist'

WHEN (DatePart (year, SysUTCDateTime()) - CAST ([@field:bach_year] as int) >= 2) AND [@field:specialization] = 'therapist'
THEN 'spec therapist'

ELSE 'a'
END

Another option:

If you do not need to check the value in that x field, but just to add it to the result you may try this:

CASE
WHEN DatePart (year, SysUTCDateTime()) - CAST ([@field:bach_year] as int) >= 2
THEN 'spec'+space(1)+'[@field:specialization]'
ELSE 'a'
END

If this does not work, please update this thread with more details and I will try to help.

this is absolutly perfect

one more request if you can help

what if i want the difference to be between months not years , what is the formula going to be ?

like the difference between 1/1950 ( format is mm.yyyy) and SysUTCDateTime()

i also want to have a table with all dates in format mm/yyyy from 1/1940 to 12/2050

but dont know how to do

if you have a ready one please share so i can import it

Share on other sites

• 0
50 minutes ago, CoopperBackpack said:

It looks like I was able to generate the values in Excel.

If you need steps to create the same file, please let me know, I will provide the steps.

the problem is when i try to import this table its shows data incompatible , because the field i want to replace is a number field and this table can be only inserted as text field data type

and the original data field which this data will be entered is a text field data type so if i change it all  my data will be erased

Share on other sites

• 0
1 hour ago, DRAhmed said:

the problem is when i try to import this table its shows data incompatible , because the field i want to replace is a number field and this table can be only inserted as text field data type

and the original data field which this data will be entered is a text field data type so if i change it all  my data will be erased

so i changed the data type from number to text field

when i try it ,  it shows Calculation error in calculated field

i refreshed the page and it works perfectly now

thank you so much

Share on other sites

• 0
17 hours ago, GoodBoy said:

You can use the DatePart function in a formula field to have this format for your date values. For example:

DatePart(month, [@field:DateField]) + '/' + DatePart(year, [@field:DateField])

thank you , @CoopperBackpack formula worked

Share on other sites

• 0
17 hours ago, GoodBoy said:

Hello. You can just add the parameter for that field by choosing it in the field picker. For example:

Case
WHEN DatePart (year, SysUTCDateTime()) - CAST ( [@field:bach_year] as int) >= 2
Then "spec" + '' + [@field:FieldName]
Else "a"
End

i had to change all field from number to text and it worked but it was alot of work

Share on other sites

• 0

i have another question

i used a cascading listebox with parent field being a virtual field using the above formula

is there a way that in report i can make a search field that can show all entries with the field  cascading listbox if it isnt blank

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.