calculation in a rule

[DRAhmed]

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 

[GoodBoy]

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

 

[CoopperBackpack]

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)

[CoopperBackpack]

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.

[CoopperBackpack]

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

[CoopperBackpack]

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

Please try to download the table https://docs.google.com/spreadsheets/d/1m5wyCt7CKrk7etmGKhqwg-Tga3vQIIgX/edit?usp=sharing&ouid=108834513784025380354&rtpof=true&sd=true

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

[GoodBoy]

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

 

[DRAhmed]

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 "  

[DRAhmed]

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 

 

[DRAhmed]

50 minutes ago, CoopperBackpack said:

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

Please try to download the table https://docs.google.com/spreadsheets/d/1m5wyCt7CKrk7etmGKhqwg-Tga3vQIIgX/edit?usp=sharing&ouid=108834513784025380354&rtpof=true&sd=true

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 

[DRAhmed]

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 

[DRAhmed]

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

[DRAhmed]

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

[DRAhmed]

 

@CoopperBackpack 

@GoodBoy

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