Hi, I think Fibery should let users convert a text to a number or date, using formulas.
I have a Period type, which I named as ‘Q’ + Quarter + ‘-’ + Year:
I.E: Q1-2022, for referring to the First Quarter of 2022.
If I have this feature, I could fill their date range with automation or formulas, instead of manually.
A Formula Field can calculate a DateTime or DateRange from a string.
But I am not clear what you mean when you say “Let users convert” –
Can you describe where this “Q1-2022” string exists, and where the corresponding Date/range would exist, and what would trigger that conversion?
Not really, I have this on Fibery:
I want to configure an automation to calculate the DateRange from the name of the Period.
This automation will be triggered when a Period is created.
I don’t have a way to convert the year from a string to a number, in order to make the DateRange.
A formula for Q1-2021 should be like this:
DateRange(Date(Right(Name,4), 1, 1), Date(Right(Name,4), 3, 31))
But it won’t work because Right(Name,4) is a string, and a number is expected.
Ah, I see – Fibery is missing a “parseInt” or “ToNumber” function in formulas 
There is currently no support for converting text to numbers unfortunately.
But have you considered approaching the problem from a different angle: you could define two number fields (T and Y) and then the name can be generated using a formula, e.g.
"T" + ToText(T) + "-" + ToText(Y)
and you can generate the dates from these fields as well:
DateRange(Date(Y,(3 * T) - 2,1),If(T = 4,Date(Y + 1,1,1) - Days(1),Date(Y,(3 * T) + 1,1) - Days(1)))
This Rule-based approach seems to work - it automatically converts a Text field (named ‘String’ in the example) to an Number or Date, and stores the result in another field(s) of the appropriate type.
Code:
const fibery = context.getService('fibery');
for (const entity of args.currentEntities) {
const str = entity['String']; // input field name (text)
await fibery.updateEntity(entity.type, entity.id, {
'Number1': parseInt(str), // output field name and value
'Number2': parseFloat(str), // output field name and value
'Date1': new Date(str) // output field name and value
});
}
Yes, thanks Matt.
It is possible to use JS conversions in an automation.
Good thinking!
Of course, the automation method needs the date range field to be a read/write field, which may be a disadvantage if you want to be sure that no-one will accidentally change the dates 
@Chr1sG, are you certain that scripts can’t update “read only” fields? I remember reading that the API was not constrained, that this was only for the UI.
That’s correct. You cannot modify readonly fields via scripts or API.
I also wanted to add my vote to adding a ToNumber function to the formulas. It is quite a useful thing to have and seems like a big omission from the current set.
I must have been thinking of the API’s capability to hide specific entity fields from the UI.
Been coming up to this limitation quite frequently as I try to do automations around version names, KPIs, etc.
Is creating a “ToNumber” function that works essentially the same as “ToText” anywhere on the roadmap?
I’m not a developer so I struggle with the javascript workarounds for some of these things.
Here’s a workaround - The following Formula will convert the Name text field to an Integer only (NOT floating point) up to 9 digits (but you could add more).
If first “If” sets the value returned (-9999) if any non-digit characters are found in the Name field.
If( MatchRegex(Name,"[^0-9]"), -9999,
If( Length(Name)<1, 0, (Find("0123456789", Middle(Name, Length(Name)-0, 1)) - 1) + 10 *
If( Length(Name)<2, 0, (Find("0123456789", Middle(Name, Length(Name)-1, 1)) - 1) + 10 *
If( Length(Name)<3, 0, (Find("0123456789", Middle(Name, Length(Name)-2, 1)) - 1) + 10 *
If( Length(Name)<4, 0, (Find("0123456789", Middle(Name, Length(Name)-3, 1)) - 1) + 10 *
If( Length(Name)<5, 0, (Find("0123456789", Middle(Name, Length(Name)-4, 1)) - 1) + 10 *
If( Length(Name)<6, 0, (Find("0123456789", Middle(Name, Length(Name)-5, 1)) - 1) + 10 *
If( Length(Name)<7, 0, (Find("0123456789", Middle(Name, Length(Name)-6, 1)) - 1) + 10 *
If( Length(Name)<8, 0, (Find("0123456789", Middle(Name, Length(Name)-7, 1)) - 1) + 10 *
If( Length(Name)<9, 0, (Find("0123456789", Middle(Name, Length(Name)-8, 1)) - 1)
))))))))))
This could be further tweaked to ignore specific non-numeric characters like “.” and “-”, by adding RegexReplace() to each line – e.g:
... (Find("0123456789", RegexReplace(Name, "[.,/-]", ""), Length( ...
That would only be useful if each numeric sub-field is fixed-length (though additional bewildering complexity could possibly solve some of that 
).
Nice trick 
Thanks @Matt_Blais, unfortunately I need to set a decimal number value!
@Chr1sG @mdubakov, is it not as easy as it would seem to just reverse the ToText function into a “ToNumber” function? I know a string can be anything and a number has a much smaller amounts of valid characters, but the naive PO in me thinks this would be a low effort/high value feature.
@interr0bangr
This Rule will work to convert a text field to a floating point value. Note that there is no more Formula field - there is a Text field (input) and a Numeric field (output) that is updated by the script when the input Text field changes.
Fibery numeric fields are limited to max 8 digits to the right of the decimal.
const INPUT_TEXT_FIELD = 'NumText'
const OUTPUT_NUMERIC_FIELD = 'Num'
const fibery = context.getService('fibery');
for (const entity of args.currentEntities) {
const num = parseFloat( entity[INPUT_TEXT_FIELD])
await fibery.updateEntity(entity.type, entity.id, {
[OUTPUT_NUMERIC_FIELD]: num
})
}
Here’s a solution to converting Text to Float (decimals)
Firstly, thanks to @Matt_Blais for his awesome solution to convert Text to Integer! From now on, I will call his function “MattFunction” because I will explain the solution for anyone interested.
In essence, the solution is:
InputText
→ Convert the integer part to Integer (Int_int) and convert the Decimal part to Integer (Dec_int) using the MattFunction
→ Convert Dec_int to float (Dec_dec) by diving it by a multiple of 10 → Dec_dec = (Dec_int/Power(10, Length(Dec_int))
→ Create the final float using Int_int + Dec_dec
Example
Input Text = 123.678
Int_int = 123
Dec_int = 678
Dec_dec = 678 / 10^3 = 0.678
Float = 123 + 0.678
Easiest way to replicate it is to use helper Formula Columns like in the screenshot above but if we wrap all of them in a single one we get the following monster formula. If you want to use it I guess you need to use a text editor to Find and Replace the “InputText” with the name of your target Column in Fibery.
If( MatchRegex(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)),"[^0-9]"), -9999,
If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<1, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-0, 1)) - 1) + 10 *
If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<2, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-1, 1)) - 1) + 10 *
If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<3, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-2, 1)) - 1) + 10 *
If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<4, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-3, 1)) - 1) + 10 *
If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<5, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-4, 1)) - 1) + 10 *
If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<6, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-5, 1)) - 1) + 10 *
If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<7, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-6, 1)) - 1) + 10 *
If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<8, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-7, 1)) - 1) + 10 *
If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<9, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-8, 1)) - 1) + 10
))))))))))
+
If( MatchRegex(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))),"[^0-9]"), -9999,
If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<1, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-0, 1)) - 1) + 10 *
If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<2, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-1, 1)) - 1) + 10 *
If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<3, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-2, 1)) - 1) + 10 *
If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<4, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-3, 1)) - 1) + 10 *
If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<5, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-4, 1)) - 1) + 10 *
If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<6, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-5, 1)) - 1) + 10 *
If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<7, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-6, 1)) - 1) + 10 *
If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<8, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-7, 1)) - 1) + 10 *
If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<9, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-8, 1)) - 1) + 10
))))))))))
/
Power(10,Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, ".")))))
Yes, the formula is unreadable, but it works with native Fibery Formulas. It is a good temporary solution until we get the “ToNumber” function or the ability to have custom functions or “Lambdas” or at least the “Lets” function.
Thank you!