Hi, how to add a user settings value in date ...

expressions
(Arnaud B.) #1

Hi, how to add a user settings value in date expression?

[Date] >=“07/02/2018” to [Date] >=“07/02/”, usersettings(year)

Thx

(Suvrutt Gurjar) #2

Presume you wish to construct a date from a fixed day/month string and year obtained through usersettings…

You may wish to try an expression as below

[Date] > = DATE(CONCATENATE(“07/02/”,TEXT(USERSETTINGS(SetYear))))

The expression assumes that the string “07/02” is fixed. Please use name something other than just Year for usersetting year parameter, as Year could be a reserved word for YEAR(date) function. The above example has name as SetYear and is a number type usersettings field.

1 Like
(Arnaud B.) #3

OK thx I’ll try it.

(Arnaud B.) #4

The expression is valid but its result type ‘Date’ is not one of the expected types: Text

(Arnaud B.) #5

So there is no problem: DATE(CONCATENATE(“01/07/”,USERSETTINGS(SetYear))) and it work

(Arnaud B.) #6

Forumla is oK bu result is empty …

AND(OR([Etat] = “Run1”, [Etat]=“Run2”), AND([Date] > = DATE(CONCATENATE(“01/07/”,USERSETTINGS(SetYear))), [Date] < = DATE(CONCATENATE(“30/06/”,USERSETTINGS(SetYear)+1))))

(Arnaud B.) #7

I’ve try many thing and the problem is the enum type of the usersettings

(Arnaud B.) #8

Sorry it don’t work. Formula is OK, but no result and test is not ok …

(Suvrutt Gurjar) #9

Hi @Arnaud_B, Is it possible for you to share screen print of any error you are getting and expression you are using?

Also please note that in browser mode testing, the usersetting value needs to be entered again after each save of the app configuration in the app editor.

So please enter the usersetting value , save it and then test it in expression in browser mode.

(Suvrutt Gurjar) #10

Suggest you to please put the expression in a VC and you can test its working in say detail view/ form

etc.

(Arnaud B.) #11

Formula is appsheet validate: USERSETTINGS type is enum

AND(OR([Etat] = “Run 1”, [Etat]=“Run 2”), AND([Date] > = DATE(CONCATENATE(“01/07/”,TEXT(USERSETTINGS(SetYear)))), [Date] < = DATE(CONCATENATE(“30/06/”,TEXT(USERSETTINGS(SetYear))))))

(Arnaud B.) #12

Date must set in US format as MM/DD/YYYY or DD/MM/YYYY ?

(Arnaud B.) #13

ALL these statements are true: …1: ANY of these statements is true: …1: (The value of column ‘Etat’) is equal to (“Run 1”) …2: (The value of column ‘Etat’) is equal to (“Run 2”) …2: ALL these statements are true: …1: (The value of column ‘Date’) is greater than or equal to (DATE( …The concatenation of all these text values ( …“01/07/” …TEXT( …The value of ‘SetYear’ from the row referenced by ‘_THISUSER’))) …2: (The value of column ‘Date’) is less than or equal to (DATE( …The concatenation of all these text values ( …“30/06/” …TEXT( …The value of ‘SetYear’ from the row referenced by ‘_THISUSER’)))

(Suvrutt Gurjar) #14

Hi @Arnaud_B, My request is you may test the expression in stages. Suggest please put just the date formula into

a VC or real column and include that VC or real column

in a view to test the date expression is working.

Please test for a date such greater than 12 such as 15 so that you know if it works in mm/dd or dd/mm format. For me it worked in mm/dd format string, even though my back end storage is dd/mm

Please see attached. Hope this helps.

(Suvrutt Gurjar) #15

The date in form view in a VC as well as real column