Convert text to number or date with formulas

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:
image
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 :cry:

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
    });
}

1 Like

Yes, thanks Matt.
It is possible to use JS conversions in an automation.
Good thinking!

1 Like

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 :confused:

@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.

1 Like