Hi, how to add a user settings value in date expression?
[Date] >=โ07/02/2018โ to [Date] >=โ07/02/โ, usersettings(year)
Thx
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.
OK thx Iโll try it.
The expression is valid but its result type โDateโ is not one of the expected types: Text
So there is no problem: DATE(CONCATENATE(โ01/07/โ,USERSETTINGS(SetYear))) and it work
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))))
Iโve try many thing and the problem is the enum type of the usersettings
Sorry it donโt work. Formula is OK, but no result and test is not ok โฆ
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.
Suggest you to please put the expression in a VC and you can test its working in say detail view/ form
etc.
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))))))
Date must set in US format as MM/DD/YYYY or DD/MM/YYYY ?
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โ)))
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.
The date in form view in a VC as well as real column
User | Count |
---|---|
43 | |
27 | |
24 | |
23 | |
13 |