Text to date without JSON or Plugin

[anon94994493]

Hi bubblers…

has anyone found a more efficient and easy way to convert a text field into a date…

my scenario is that we have an email inbound that receives emails from agencies and within those emails there are 2-3. dates. As these are contained within the text body of the email we are unable to use the JSON input to separate them out and format them as dates.

I’ve been experimenting with extraction and using REGEX, but I feel it’s getting way too complicated for something that should be relatively simple process to format text as date. particularly since I’m thinking I’d also have to do a search and replace for each Month and convert to an integer. Jan > 01, Feb >02, Mar>03 etc…

I did get some form of system in my head by having an arbitrary date of 01/01/0000 00:00 and then using the +years +months +days +Hours +minutes to then increment from there. it seemed to work fro the year, but then I got into the month and couldn’t think on how to do it all in one expression.

my date formats within the email are…

Between 08:00 (UTC+1) on Tue 16 Jul 2024 and 20:00 (UTC+1) on Tue 16 Jul 2024

Issued: 10:17 (UTC+1) on Mon 15 Jul 2024

The entire email is at the bottom of the post so you can see the use case.

Has anyone got any relatively simple and scalable solution to this dilemma?

thanks in advance

View all warnings | Email preferences | View in browser

# Weather warning

	## Yellow warning for North West England
	Rain
	Between 08:00 (UTC+1) on Tue 16 Jul 2024 and 20:00 (UTC+1) on Tue 16 Jul 2024

Headline

Some places staying dry but slow-moving, heavy showers are likely to lead to some disruption, particularly to transport.

What to expect

• Spray and flooding on roads probably making journey times longer
• Bus and train services probably affected with journey times taking longer
• There is a small chance that a few properties could be temporarily flooded in the heaviest showers.

What should I do?

---

Issued: 10:17 (UTC+1) on Mon 15 Jul 2024

Further details

Heavy showers are expected to develop during Tuesday morning, then become more widespread and slow-moving across the east of the warning area during the afternoon. Whilst many places will miss the worst, where showers do occur, 15-20 mm rainfall is likely within an hour, and perhaps 30 to 40 mm in 1 to 2 hours in a few places. The odd lightning strike is also possible.

Showers will steadily ease during the evening.

What should I do? Consider if your location is at risk of flash flooding. If so, consider preparing a flood plan and an emergency flood kit.

Give yourself the best chance of avoiding delays by checking road conditions if driving, or bus and train timetables, amending your travel plans if necessary.

People cope better with power cuts when they have prepared for them in advance. It’s easy to do; consider gathering torches and batteries, a mobile phone power pack and other essential items.

If you find yourself outside and hear thunder, protect yourself by finding a safe enclosed shelter (such as a car). Do not shelter under or near trees, or other structures which may be struck by lightning. If you are on an elevated area move to lower ground.

Be prepared for weather warnings to change quickly: when a weather warning is issued, the Met Office recommends staying up to date with the weather forecast in your area.

View full warning details and map view

---

Regions and local authorities affected

Central, Tayside & Fife

• Angus
• Clackmannanshire
• Dundee
• Falkirk
• Fife
• Perth and Kinross
• Stirling

Grampian

• Aberdeen
• Aberdeenshire

North East England

• Durham
• Gateshead
• Newcastle upon Tyne
• North Tyneside
• Northumberland
• South Tyneside
• Sunderland

North West England

• Cumbria

SW Scotland, Lothian Borders

• Dumfries and Galloway
• East Lothian
• Edinburgh
• Midlothian Council
• Scottish Borders
• West Lothian

Strathclyde

• East Ayrshire
• East Dunbartonshire
• East Renfrewshire
• Glasgow
• North Lanarkshire
• South Lanarkshire

Manage your email preferences:

Download our app for warning updates on the go

For enquiries regarding this warning, please contact the Met Office Weather Desk:

+44 370 900 0100
enquiries@metoffice.gov.uk

---

View all warnings Email preferences View in browser Unsubscribe

Follow us on

© Crown copyright

[jobs]

I’m not a huge fan of AI at its current state, but I feel like this would be the perfect use case.

Send the contents of the email to chatGPT asking for the date in a constant format.

You’ll get the response correct more of the time than if you try and regex an email that may change over time.

Just a thought!

[anon94994493]

@anon94994493

Between 08:00 (UTC+1) on Tue 16 Jul 2024 and 20:00 (UTC+1) on Tue 16 Jul 2024

Thanks for the response, it’s something I’ll look at, I was however hoping for a simpler and more elegant solution as using AI to do it seems like using a sledgehammer to crack open a nut.

[anon94994493]

So after ponding this for a while and doing a couple of experiments I think I’ve found a relatively simple way to do this if anyone is interested… it doesn’t involve plugin’s javascript or anything other than some REGEX, filtering and other bubble native functions…

so to start with I used REGEX to extract each of the components that I wanted to achieve - ChatGPT is very good at helping you to write REGEX if you paste in the text and then ask it.

I also found it useful to create a scratch page and have the content that is in the database to be transformed and an input where you can test the REGEX before committing it to a workflow and having to re run emails etc.

Step 1.

once you have created your web hook (or other source) ensure there is at least one entry in the database in the raw format.

Step 2.

create you scratch page and on that page have a text box that is the do a search for…and search for the entry in Step 1.

add to this page an input element and also another text box.

within this second text box do a search for the same content as in step 2 but at the end of the search add ‘extract with REGEX’ and have the source for the regex as the input field on the page.

Step 3

in the database table create a numeric field for each of the date components you want to extract, I wanted, Year, Month, Day and Hour.

go into where you have your web hook or API source or where you want to create your workflow to extract the dates.

so using the REGEX pattern created in ChatGPT and tested on the scratch page you can now use this in the workflow to extract each component of your source into the new numeric field, to save it you will need to convert to number and subtract 2001 from the result for years, -1 for months, -1 for days and in our case -12 for hours.

In the months I had to covert Jan, Feb, Mar to integers so there are 12 find and replaces that take that into account and make the changes.

so you should (if you have your REGEX and numeric fields and conditions/expressions built correctly) have a series of fields that represent the date.

The next step (as the next step in your workflow) is to then make an arbitrary date into the date you want.

this was done by having a new date field and the expression to complete is is to take and arbitrary date (in my case 01/01/2000. 01:01:01

so you effectively add + years, +months +day +hour from your created numeric fields from your REGEX extraction to create the date and time you desire.

I hope this helps someone who was also struggling to convert from text to date.

so using a combination of REGEX, a numerical field and adding to an arbitrary date you can get the result you desire - it could probably all be done in one hit with a hugely long expression, but that would be a nightmare to diagnose or modify if you needed to…

[anon94994493]

@lindsay_knowcode

as per out chat earlier, here is the post we discussed.

[Jici]

And why not use a “backend date parser” ?
Use a backend WF, send date as text, and let API Connector parse the date.
Not sure which one is Better to parse the date (the backend WF itself or the API Connector). If I have time, I will do some test…

[anon94994493]

I wasn’t aware there was such a thing, and in the example, the data is contained within the json text body along with the rest of the email contents.

also, in our case we do all this in the backend once we have received the email with the dates we parse it into several components and then process them all.

can you share what you meant be date parser?

[Jici]

I mean to create an endpoint in backend WF that will receive the date as text and return data from API. Use API Connector to call this backend WF and set “Date” as type for the API Connector.

In your case, with "08:00 (UTC+1) on Tue 16 Jul 2024 "
You will need to use split by " on " and send the Date and time in the correct order
So you will send using API Connector to backend WF
Tue 16 Jul 2024 08:00 (UTC+1)

[georgecollier]

If AI is using a sledgehammer to crack a nut, then this is using a six tonne elephant!

Agree that calling a backend workflow with the date using the API connector is the best option. The backend will parse the date correctly, and you can return it straight back using return data from API.

[anon94994493]

Harsh

I’ll check out the other methods…

in my defence, when I researched this I saw a lot of posts on this with no specific easy conclusion. although they were older posts.

have a great weekend!

[georgecollier]

Hey Robbie

I’m working on something that might reliably help with this:

You can provide any text input and a date template, and it will extract any dates found as Bubble dates.

Additionally, if you don’t provide any date template, it’ll do its best to automatically identify any, including with relative times:

Do you want to try it or let me know if there’s any other things that would be handy?

[anon94994493]

Hi George,

I’ll give that a try in the next week or so.

my current method is working now it’s implemented so I don’t want break it too much